Question 89903 in PostgreSQL Question posted in
The official documentation can be found here.

How to retrieve rows based on a time interval within rows in Postgres? – Postgresql

I have table (t_answer) like below:

user_id created_at answer
1 2023-01-01 1a
1 2023-01-02 1b
1 2023-01-11 1c
2 2023-02-05 2a
2 2023-02-20 2a

I want to retrieve the rows within an interval of 1 week starting from each user’s first answer’s created_at date. So, the result will be like:

user_id created_at answer
1 2023-01-01 1a
1 2023-01-02 1b
2 2023-02-05 2a

So, what should be the query to get this?

Tags:

2

Answers


  1. 0

    Using a subquery:

    select t.* from t_answer t where t.created_at <= (select min(t1.created_at) 
   from t_answer t1 where t1.user_id = t.user_id) + interval '1 week';

    See fiddle.

    Login or Signup to reply.
  2. 0 
    with t as
    (select
        row_number() over (partition by user_id order by created_at) as rn,
        *
        from t_answer)
select
    o.*
    from t
        join t_answer as o on
            t.user_id = o.user_id
    where t.rn = 1 and
        o.created_at < t.created_at + interval '1 week';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search