SQL29题解 | #计算用户的平均次日留存率#

# 限定条件：第二天再来
# 解法1：表里的数据可以看作是全部第一天来刷题了的，那么我们需要构造出第二天来了的字段，因此可以考虑用left join把第二天来了的拼起来，限定第二天来了的可以用date_add(date1, interval 1 day)=date2筛选，并用device_id限定是同一个用户。
# 解法2：用lead函数将同一用户连续两天的记录拼接起来。先按用户分组partition by device_id，再按日期升序排序order by date，再两两拼接（最后一个默认和null拼接），即lead(date) over (partition by device_id order by date)
# 平均概率：
# 解法1：可以count(date1)得到左表全部的date记录数作为分母，count(date2)得到右表关联上了的date记录数作为分子，相除即可得到平均概率
# 解法2：检查date2和date1的日期差是不是为1，是则为1（次日留存了），否则为0（次日未留存），取avg即可得平均概率。

#太难了， 照着解析敲都没敲对，直接抄答案
SELECT count(date2) / count(date1) AS avg_ret
FROM (
    SELECT
        distinct QPD1.device_id,
        QPD1.date AS date1,
        QPD2.date AS date2
    FROM question_practice_detail AS QPD1
    LEFT JOIN(
        SELECT distinct device_id, date
        FROM question_practice_detail
    ) AS QPD2
    ON QPD1.device_id=QPD2.device_id
    AND date_add(QPD1.date, interval 1 day) = QPD2.date
) AS id_lASt_next_date