Postgres 13

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 event_tracker as
    select 1577863551 as timestamp, 1 as id, 'home' as event union all
    select 1577863555 as timestamp, 1 as id, 'profile' as event union all
    select 1577863555 as timestamp, 2 as id, 'home' as event 

SELECT 3


select et.*, to_timestamp(timestamp)
from event_tracker et


timestamp
id
event
to_timestamp


1577863551
1
home
2020-01-01 07:25:51+00

1577863555
1
profile
2020-01-01 07:25:55+00

1577863555
2
home
2020-01-01 07:25:55+00



SELECT 3


with t as (
      select id, dte, sum(sum(inc)) over (partition by id order by dte) as running_ins
      from ((select id, date(to_timestamp(timestamp)) as dte, 1 as inc
             from event_tracker
            ) union all
            (select id, date(to_timestamp(timestamp)) + interval '31' day as dte, -1 as inc
             from event_tracker
            )
           ) id
      group by id, dte
     ),
     first_last as (
      select user, dte, (case when running_ins > 0 then 1 else -1 end) as user_inc
      from (select t.*,
                   lag(running_ins) over (partition by id order by dte) as prev_running_ins
            from t
           ) t
      where prev_running_ins is null or
            prev_running_ins = 0 and running_ins > 0 or
            prev_running_ins > 0 and running_ins = 0
     )
select fl.dte,
       sum(sum(user_inc)) over (order by fl.dte) as distinct_30_days
from first_last fl
group by fl.dte;


dte
distinct_30_days


2020-01-01 00:00:00
2

2020-02-01 00:00:00
0



SELECT 2


with t as (
      select id, dte, sum(sum(inc)) over (partition by id order by dte) as running_ins
      from ((select id, date(to_timestamp(timestamp)) as dte, 1 as inc
             from event_tracker
            ) union all
            (select id, date(to_timestamp(timestamp)) + interval '31' day as dte, -1 as inc
             from event_tracker
            )
           ) id
      group by id, dte
     ) select t.* from t


id
dte
running_ins


1
2020-01-01 00:00:00
2

1
2020-02-01 00:00:00
0

2
2020-01-01 00:00:00
1

2
2020-02-01 00:00:00
0



SELECT 4

timestamp	id	event	to_timestamp
1577863551	1	home	2020-01-01 07:25:51+00
1577863555	1	profile	2020-01-01 07:25:55+00
1577863555	2	home	2020-01-01 07:25:55+00

id	dte	running_ins
1	2020-01-01 00:00:00	2
1	2020-02-01 00:00:00	0
2	2020-01-01 00:00:00	1
2	2020-02-01 00:00:00	0