0

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.

  • 1
    Please remember to provide table definition and Postgres version. – Erwin Brandstetter Apr 25 at 16:30
1

A classical 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 at 16:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.