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 T (date_key date, user_id int);
insert into T values
('2022-01-01', 001),
('2022-01-01', 002),
('2022-01-02', 001),
('2022-01-02', 003),
('2022-01-03', 002),
('2022-01-03', 003),
('2022-01-04', 002),
('2022-01-04', 004)
8 rows affected
with data as (
select *, row_number() over (partition by date_trunc('month', date_key), user_id order by date_key) as rn
from T
)
select date_key, sum(count(case when rn = 1 then 1 end)) over (partition by date_trunc('month', date_key) order by date_key) as cum_monthly_users
from data
group by date_key;
| date_key | cum_monthly_users |
|---|---|
| 2022-01-01 | 2 |
| 2022-01-02 | 3 |
| 2022-01-03 | 3 |
| 2022-01-04 | 4 |