题解 | 计算用户的平均次日留存率
计算用户的平均次日留存率
https://www.nowcoder.com/practice/126083961ae0415fbde061d7ebbde453
WITH base AS (SELECT DISTINCT device_id, date
FROM question_practice_detail),
all_day AS (SELECT device_id,
date,
LEAD(date) OVER (PARTITION BY device_id ORDER BY date) AS next_date
FROM base)
SELECT SUM(CASE WHEN DATEDIFF(next_date, date)=1 THEN 1 ELSE 0 END) / COUNT(*) AS avg_ret
FROM all_day
