题解 | #牛客每个人最近的登录日期(五)#

献上一个超级笨的办法，硬写写出来。。。。。。
留存表里面用的是 现在日期 初次登陆日期 初次登陆日期+1day
如果现在日期 = 初次登陆日期+1day 那么 现在日期-1day就是初次日期。以现在日期为groupby对象
统计“初次登陆日期+1day”的数量。此数量就是现在日期-1day的 新增用户留存数量

右连接当日新增表。新增留存用户数量/新增用户数量 即可。

SELECT v2.date3 date, ROUND(IFNULL(v3.old/v2.new,0),3) p
FROM
(
SELECT DATE_ADD(v1.date1,INTERVAL -1 DAY) date2,SUM((CASE WHEN v1.date_min_next = v1.date1 THEN 1 ELSE 0 END)) OLD
FROM
(
SELECT l.user_id,l.date date1, a.date_min,DATE_ADD( a.date_min,INTERVAL 1 DAY) date_min_next
FROM login l
LEFT JOIN
(
SELECT user_id id1, MIN(date) date_min
FROM login
GROUP BY user_id
) a
ON l.user_id = a.id1
) v1
GROUP BY v1.date1
) v3

RIGHT JOIN
(
SELECT l.date date3, SUM(CASE WHEN l.date = a.date_min THEN 1 ELSE 0 END) NEW
FROM login l
INNER JOIN
(
SELECT user_id id1, MIN(date) date_min
FROM login
GROUP BY user_id
) a
ON l.user_id = a.id1
GROUP BY l.date
) v2

ON v3.date2 = v2.date3