[first_dates]: userId:uint64, first_dt:date
[clickstream]: userId:uint64, dt:date
to_char(first_dt, 'yyyy-MM') as monthly
with fd as (select concat(split_string(to_char(first_dt, 'yyyy-MM-dd'), '-')[0],'-',
split_string(to_char(first_dt, 'yyyy-MM-dd'), '-')[1])
as first_month,
userId
from first_dates),
clst as (select concat(split_string(to_char(first_dt, 'yyyy-MM-dd'), '-')[0],'-',
split_string(to_char(first_dt, 'yyyy-MM-dd'), '-')[1])
as month,
userId
from clickstream
)
select first_month,
month,
count(distinct clst.userId)/(count(distinct fd.userId) over partition by (first_month))*100.0 as retention
from fd
LEFT JOIN clst
using(userId)
GROUP BY first_month, month