记录面试遇到的SQL题目--用户留存率
已知一张用户信息表。结构如下:
现需要计算日活跃用户,以及次日、3日、7日的用户留存率:
解决思路如下:
单个表既有新客户,也有老用户每天的登录时间,所以进行自联结计算留存用户
select
a.dt
,count(distinct a.id) as `日活跃用户`
,count(distinct b.id) as `次日留存数`
,count(distinct c.id) as `三日留存数`
,count(distinct d.id) as `七日留存数`
,concat(round(count(distinct b.id) / count(distinct a.id) * 100, 2), '%') as `次日留存率`
,concat(round(count(distinct c.id) / count(distinct a.id) * 100, 2), '%') as `三日留存率`
,concat(round(count(distinct d.id) / count(distinct a.id) * 100, 2), '%') as `七日留存率`
-- select *
from yhlc a
LEFT join yhlc b on a.id=b.id and b.dt=a.dt+1
LEFT join yhlc c on a.id=c.id and c.dt=a.dt+3
LEFT join yhlc d on a.id=d.id and d.dt=a.dt+7
group by a.dt;