How to select blocks from a result by condition?

Question

Example table:

master_id 1 created_at 22.02.1997
master_id 1 created_at 22.03.1997
master_id 1 created_at 22.04.1997
master_id 1 created_at 22.07.1997
master_id 1 created_at 22.08.1997
master_id 1 created_at 22.10.1997

Example query result:

master_id 1 date_from 22.02.1997 date_to 22.04.1997
master_id 1 date_from 22.07.1997 date_to 22.08.1997
master_id 1 date_from 22.10.1997 date_to 22.10.1997

Note: The interval between dates must not be longer than one month for a block.

Please remember to provide table definition and Postgres version. — Erwin Brandstetter, Apr 25 '19 at 16:30

Erwin Brandstetter · Accepted Answer · 2019-04-25T16:42:29.773

A classical gaps-and-islands problem:

SELECT master_id, min(created_at) AS date_from, max(created_at) AS date_to
FROM  (
   SELECT *
        , count(*) FILTER (WHERE step) OVER (PARTITION BY master_id ORDER BY created_at) AS range
   FROM  (
      SELECT *, created_at > lag(created_at) OVER (PARTITION BY master_id ORDER BY created_at) + interval '1 month' AS step
      FROM   tbl
      ) sub1
   ) sub2
GROUP  BY master_id, range
ORDER  BY master_id, range;

Related (with explanation):

Thank you so much, it worked for me, and gave direction to study — Ilya, Apr 25 '19 at 16:38

How to select blocks from a result by condition?

1 Answers1