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.

Improve this question
1
  • 1
    Please remember to provide table definition and Postgres version.
    – Erwin Brandstetter
    Commented Apr 25, 2019 at 16:30

1 Answer 1

Reset to default
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):

Improve this answer
1
  • Thank you so much, it worked for me, and gave direction to study
    – Ilya
    Commented Apr 25, 2019 at 16:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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