题解 | #日活与每日次日留存率#
日活与每日次日留存率
https://www.nowcoder.com/practice/18b168ada98d4bdb9108444443cea7d3
不是求首日的还算简单。
SELECT visit_date, COUNT(DISTINCT ID) dau, COUNT(SEC)/COUNT(DISTINCT ID) next_day_per FROM ( SELECT u1.id,u1.visit_date,u2.visit_date sec FROM user_visit_log u1 LEFT JOIN user_visit_log u2 ON u1.id=u2.id and datediff(u2.visit_date,u1.visit_date) = 1 ) A GROUP BY visit_date