Untitled

[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