By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
--
create table users (user_id integer);
insert into users values (1),(2);
create table purchases (user_id integer, purchase_date date);
insert into purchases (user_id, purchase_date)
values
(1, date '2020-06-01'),
(1, date '2020-06-10'),
(1, date '2020-06-11'),
(1, date '2020-06-14'),
(1, date '2020-06-17'),
(2, date '2020-05-01'),
(2, date '2020-05-10'),
(2, date '2020-05-20'),
(2, date '2020-05-28'),
(2, date '2020-06-17')
;
2 rows affected
10 rows affected
select *
from users u
where exists (select *
from (
select count(*) over (partition by user_id
order by purchase_date
range between interval '6 day' preceding and current row) as num_purchases
from purchases p
where p.user_id = u.user_id
) t
where num_purchases >= 3)
| user_id |
|---|
| 1 |