SQL练习七—留存率问题

目录

一 如何计算留存率

1.先把想要的数据提取出来观察：用户id、日期、次日日期

2.留存情况：我们希望当前日期和次日日期对应起来

3.留存率计算：留存的(有次日信息)/全部 

4.整理思路，代码优化

 二 留存率实战练习—2021年11月每天新用户的次日留存率

一 如何计算留存率

• 公式：留存率 = （留存用户数 / 初始用户数）× 100%。
• 举例：假设一个应用在第一天有 1000 个新用户注册，到第 7 天时，这 1000 个用户中有 300 个仍然在使用该应用，那么第 7 天的留存率 = （300 / 1000）× 100% = 30%。

问题分析：

1.先把想要的数据提取出来观察：用户id、日期、次日日期

去重：去掉一个用户一日多次登录记录

select distinct device_id,date,date_add(date,interval 1 day)as ciri
from question_practice_detail
order by device_id,date

2.留存情况：我们希望当前日期和次日日期对应起来

如果没有就是次日没来，如果有就代表次日留存

with t1 as(
select distinct device_id,date,date_add(date,interval 1 day)as ciri
from question_practice_detail
order by device_id,date
)
select a.device_id,a.date as '登录日期',b.date as '次日日期' from  t1 as a
left join t1 as b
on a.device_id=b.device_id and a.date+1=b.date

3.留存率计算：留存的(有次日信息)/全部 

  count(次日)/count(device_id)

4.整理思路，代码优化

select round(count(yd)/count(device_id),4) as avg_ret
from(
select distinct q1.device_id as device_id,q1.date as td ,q2.date as yd
from question_practice_detail as q1
left join  question_practice_detail  as q2
on q1.device_id=q2.device_id and q2.date=date_add(q1.date,interval 1 day)
    ) a

 二 留存率实战练习—2021年11月每天新用户的次日留存率

t1：用户，注册日期(最小的登录日期)，按日期group 计每日新增用户数 

t2：(用户的登录信息) 用户，登录时间(进出跨天两天都计入)

把这个注册信息和登录信息连接on用户id和日期(检查次日是否登录 注册日期+1=登录日期)

select
    regdt,
    round(count(t3.uid) / count(t2.uid), 2) as uv_left_rate
from
    (
        select
            uid,
            regdt,
            count(uid) over (
                partition by
                    regdt
            ) as uv
        from
            (
                select
                    uid,
                    date (min(in_time)) as regdt
                from
                    tb_user_log
                group by
                    uid
            ) t1
    ) t2
    left join (
        select
            uid,
            date (in_time) as dt
        from
            tb_user_log
        union
        select
            uid,
            date (out_time) as dt
        from
            tb_user_log
    ) t3 on t3.dt = date_add (regdt, interval 1 day)
    and t2.uid = t3.uid
where
    date_format (regdt, '%Y-%m') = '2021-11'
group by
    regdt