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

Question

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?

Ajax1234 · Accepted Answer · 2023-02-01 16:20:35Z

1

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.

answered Feb 1, 2023 at 16:20

Ajax1234

71.7k9 gold badges67 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Roman · Accepted Answer · 2023-02-01 16:29:42Z

1

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

answered Feb 1, 2023 at 16:29

Roman

314 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related