mysql题解 | 计算用户的平均次日留存率
计算用户的平均次日留存率
https://www.nowcoder.com/practice/126083961ae0415fbde061d7ebbde453
SELECT
COUNT(DISTINCT `next`.device_id, `next`.`date`) / COUNT(DISTINCT `before`.device_id, `before`.`date`) AS avg_ret
FROM question_practice_detail AS `before`
LEFT JOIN question_practice_detail AS `next`
ON `before`.device_id = `next`.device_id
AND DATEDIFF(`next`.`date`, `before`.`date`) = 1
;
表拼接
计算相差一天的行
留存率
时间间隔等于1的访客(去重) / 所有访客(去重)
