SELECT
log_day '日期',
count( user_id_day0 ) '新增数量',
count( user_id_day1 ) / count( user_id_day0 ) '次日留存率',
count( user_id_day3 ) / count( user_id_day0 ) '3日留存率',
count( user_id_day7 ) / count( user_id_day0 ) '7日留存率',
count( user_id_day30 ) / count( user_id_day0 ) '30日留存率'
FROM
(
SELECT DISTINCT
log_day,
a.user_id_day0,
b.user_id AS user_id_day1,
c.user_id AS user_id_day3,
d.user_id AS user_id_day7,
e.user_id AS user_id_day30
FROM
( SELECT DISTINCT
date AS log_day,
user_id AS user_id_day0
FROM
data
GROUP BY user_id
ORDER BY log_day
) a
LEFT JOIN data b ON DATEDIFF(b.date, a.log_day ) = 1
AND a.user_id_day0 = b.user_id
LEFT JOIN data c ON DATEDIFF(c.date, a.log_day ) = 2
AND a.user_id_day0 = c.user_id
LEFT JOIN data d ON datediff(d.date, a.log_day ) = 6
AND a.user_id_day0 = d.user_id
LEFT JOIN data e ON datediff(e.date, a.log_day ) = 29
AND a.user_id_day0 = e.user_id
) temp
GROUP BY
log_day
ORDER BY log_day;
用户留存分析—SQL
于 2022-04-30 00:01:34 首次发布