留存率 和 最大连续登录天数 SQL

有数据表 login 记录了用户登录的信息, 其中有一天登录多次的用户, 如下表所示

user_id		login_date
	1		2022-01-01
	2		2022-01-01
	1		2022-01-01
	3		2022-01-01
...
...

留存率

需求1: 求每天新增用户数量, 次留, 7日留, 15日留

# 先求每个用户首次登录的日期表, 首次登录日期当做新增的日期
with reg as(
select user_id, min(login_date) as reg_date
from login
group by user_id)

select
    reg.reg_date,
    count(distinct reg.user_id) as new_num,
    count(distinct if(datediff(login.login_date, reg.reg_date) = 1, login.user_id, null))/count(distinct reg.user_id) as remain_rate2,
    count(distinct if(datediff(login.login_date, reg.reg_date) = 7, login.user_id, null))/count(distinct reg.user_id) as remain_rate7,
    count(distinct if(datediff(login.login_date, reg.reg_date) = 15, login.user_id, null))/count(distinct reg.user_id) as remain_rate15
from reg left join login 
on reg.user_id = login.user_id and datediff(login.login_date, reg.reg_date) > 0
group by reg.reg_date ;

结果如下表

最大连续登录天数

# 先去重
with dup as(
select user_id, login_date from login
group by user_id, login_date
),

-- 连续登录对应的前一天
lagt as (
select 
    user_id, 
    date_sub(login_date, interval  row_number() over(partition by user_id order by login_date)  day) as primary_day
from dup)

-- 可能有用户多次连续登录了, 去最长的一次
select user_id, max(day_num)
from 
	(
	select 
	    user_id, primary_day, count(*) as day_num
	from lagt
	group by user_id, primary_day
	) t2
group by user_id;