题解 | #2021年11月每天新用户的次日留存率#
2021年11月每天新用户的次日留存率
http://www.nowcoder.com/practice/1fc0e75f07434ef5ba4f1fb2aa83a450
SELECT t4.uid AS uid1,t4.d1 ,t5.uid AS uid2,LEFT(t5.IN_TIME,10) d2
FROM (
-- 求新用户第一个日期出现的用户
SELECT t2.* FROM (
SELECT t1.uid ,LEFT(t1.IN_TIME,10) AS d1 ,ROW_NUMBER() over(PARTITION BY t1.uid ORDER BY t1.in_time) cnt FROM tb_user_log t1
) t2 WHERE t2.cnt = 1
) t4
LEFT JOIN tb_user_log t5 ON t4.uid = t5.uid
AND (t4.d1 = LEFT(date_sub(t5.IN_TIME,interval 1 DAY),10) OR t4.d1 = LEFT(date_sub(t5.out_time,interval 1 DAY),10))
WHERE left(d1,7) = '2021-11'
) t6 GROUP BY t6.d1
ORDER BY t6.d1
