How to retrieve rows based on a time interval within rows in Postgres?-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

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';