题解 | #2021年11月每天新用户的次日留存率#

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