题解 | 计算用户的平均次日留存率
计算用户的平均次日留存率
https://www.nowcoder.com/practice/126083961ae0415fbde061d7ebbde453
WITH t1 AS (
-- 每日刷题人数
SELECT
date,
COUNT(DISTINCT device_id) AS active_cnt
FROM question_practice_detail
GROUP BY date
),
t2 AS (
-- 每日的次日留存人数
SELECT
a.date,
COUNT(DISTINCT a.device_id) AS retain_cnt
FROM question_practice_detail a
JOIN question_practice_detail b
ON a.device_id = b.device_id
AND DATEDIFF(b.date,a.date) = 1
GROUP BY a.date
)
SELECT
SUM(retain_cnt)/SUM(active_cnt) AS avg_ret
FROM t1
LEFT JOIN t2 ON t1.date =t2.date
#SQL#
左边刷题右边 chat 效果很好