title: GROUP BY based on dates and number of months
body: <p>I have a PostgreSQL (many) table with monitoring data. The table contains an ID and Date column. Now I need to classify (group) all this monitoring data in strata (groups) of 6 months. So, in case a monitoring occurs more than 6 months later than the latest date, it is considered as a new monitoring and should be added to a new 'instance group'. If the monitoring occurred WITHIN 6 months of the latest date, it is considered as the same monitoring and added to the first instance group.</p>
  <p>So basically I need to group the data based on ID and Date by counting the number of months between the Dates. All dates that are &lt; 6 months in between go into one group (with the same ID). Now here is the difficulty: In case a Date is more than 6 months (&gt; 6 months) ahead of the (second latest) previous date the row needs to be added to a new/seperate Date group (but of the same ID). Below the input and desired output:</p>
  <p><a href="https://i.stack.imgur.com/NHRXB.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/NHRXB.png" alt="enter image description here" /></a></p>
  <p><a href="https://i.stack.imgur.com/5aY8Z.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/5aY8Z.png" alt="enter image description here" /></a></p>
  <p>Of course, I need to calculate specific results (SUMS/AVERAGES) for each monitoring instance group, but that should be fairly easy if I know how to create the groups. Any idea how I can do this best?</p>