题目

题目：现在运营想要查看用户在某天刷题后第二天还会再来刷题的留存率。请你取出相应数据。
牛客：SQL29 计算用户的平均次日留存率
示例：question_practice_detail

根据示例，你的查询应返回以下结果：

|avg_ret|
|-|
|0.3000|

题解

⚠️ 下述方法依次更好，若无需递进，可由后往前看。

方法1：留存日总数/日期总数——各用户的留存日/日期数量获取——留存日的判断与打标签

思路：分用户判断与打标签
1.通过窗口函数lag判断各个用户当前日相较前一日是否留存，并标记。
2.汇总每个用户的留存日总数；汇总每个用户的登陆日总数。
3.汇总所有用户的留存日总数、登陆日总数，相除得到留存率。

select
    round((sum(retention_sum) / sum(total_sum)), 4) avg_ret S3.汇总所有用户的留存日数量、登陆日数量，相除得到留存率。
from
    (
        select
            device_id,
            sum(retention_status) retention_sum S2.汇总各用户的留存日总数
        from
            (
                select
                    device_id,
                    date,
                    case
                        when date - lag (date, 1) over (
                            partition by
                                device_id
                            order by
                                date
                        ) = 1 then 1
                        else 0
                        end as retention_status S1.通过日期相减 判断每个用户 相对于前一天 是否留存；
                    留存当日记录为1；因为order by date，所以重复日期的记录不会被多余标记
                from
                    question_practice_detail
            ) as sub
        group by
            device_id
    ) as sub_retention
    join ( 连接各用户的留存日数量、登录日数量表
        select
            device_id,
            count(distinct date) as total_sum S2.汇总各用户登陆日总数；
            因为数据不是截选，前后均没登录，考虑留存比较的登陆日数量时，登入日数量不用-1
        from
            question_practice_detail
        group by
            device_id
            ) as sub_total 
            on sub_retention.device_id = sub_total.device_id

方法2：留存日总数/日期总数——用户与日期的去重组合——留存日的判断与打标签

思路：利用用户与日期的去重组合
1.获取用户与登陆日的去重组合。
2.判断每个组合的登陆日留存情况，并标记。
3.汇总标记为留存的组合，除以组合总数，得到留存率。

SELECT 
SUM(IF(retention=1,1,0))/COUNT(device_id) avg_ret 汇总留存的用户、登陆日组合，除以所有用户、登陆日的组合
这里用到IF,因为两日期可能不连续，相减大于1,故只考虑1（留存情况）。
FROM(
    SELECT 
    device_id,
    date,
    date - (lag(date,1) over(PARTITION BY device_id ORDER BY date )) AS retention 获取每个用户、登陆日组合的留存情况
    FROM(
        SELECT DISTINCT device_id,date from question_practice_detail 用户、登陆日组合去重
        ) sub_group
    ORDER BY device_id
) sub_retention

方法3：留存日总数/日期总数——用户与日期的去重组合——通过多字段表连接判断留存

思路：利用用户与日期的去重组合，利用多字段表连接判断留存。
1.去重组合用户、日期。
2.left join上datelag列，用多字段表连接进行匹配。
3.对主表进行count并相除。

select count(sub2.device_id)/count(sub1.device_id) as avg_ret sub1完整，left join的sub2仅包含匹配的datelag
from(
    select distinct device_id, date datenow from question_practice_detail
    ) as sub1 用户、日期的去重组合
left join( LEFT JOIN: datenow完整呈现，对应展现datelag匹配值
    select distinct device_id, date datelag from question_practice_detail
    ) as sub2 
    on sub1.device_id=sub2.device_id
    and sub1.datenow=sub2.datelag+1 用多字段表连接将同用户的datelag与datenow匹配