永辉超市大数据开发校招笔试题

SQL题，简单的我就不放出来了，比较有难度的一道题，是求用户存留率的。

题：

用户表user_id user_big_type user_mid_type fst_login_date100001 上海市徐汇区 2016-01-02

用户登录表user_id login_date100001 2016-03-03

时间维度表date_id date_name date_year1 1900-01-01 1900....40000 2016-01-01 2016

求上海市用户的留存率(用sql语句写以下的逻辑)维度第一周的留存率第二周的留存率第三周的留存率第四周的留存率上海市 50% 30% 20% 15%

注:第几周为第一次登录开始每7天为一周,如用户2016-03-01为首次登录,那么第一周从2016-03-02至2016-03-08,一天内有多次登录只算一次

/*--计算日期 2022-03-29 2-8 9-15 16-22 23-29user_id | user_big_type | fst_login_date | login_date | w1 | w2 | w3 | w4 |fm1| fm2 | fm3 | fm4100001 | 上海市 | 2022-01-01 | 2022-01-02 | 1 | 0 | 0 | 0 | 1 | 1 | 1 | 1100001 | 上海市 | 2022-01-01 | 2022-01-08 | 1 | 0 | 0 | 0 | 1 | 1 | 1 | 1100001 | 上海市 | 2022-01-01 | 2022-01-13 | 0 | 1 | 0 | 0 | 1 | 1 | 1 | 1100001 | 上海市 | 2022-01-01 | 2022-01-19 | 0 | 0 | 1 | 0 | 1 | 1 | 1 | 1100001 | 上海市 | 2022-01-01 | 2022-01-30 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1100001 | 上海市 | 2022-01-01 | 2022-01-31 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1

100002 | 上海市 | 2022-03-27 | 2022-03-28 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0

解析：

with tmp as (
--为用户每周是否留存打上标记,如果来了,为1,如果没来为0
--计算周活跃度的分母,计算日期如果离首次登陆日期太近的话,有些数据根本就没有第二周,第三周的数据,会导致分母变大(严格来说应该剔除这些数据)
SELECT
t1.user_id,
t1.user_big_type,
max(if(datediff(t2.login_date,t1.fst_login_date) BETWEEN  1 AND 7  ,1,0)) as w1, --第一周是否活跃的标记,活跃为1,不活跃为0
max(if(datediff(t2.login_date,t1.fst_login_date) BETWEEN  8 AND 14 ,1,0)) as w2, --第二周是否活跃的标记,活跃为1,不活跃为0
max(if(datediff(t2.login_date,t1.fst_login_date) BETWEEN 15 AND 21 ,1,0)) as w3, --第三周是否活跃的标记,活跃为1,不活跃为0
max(if(datediff(t2.login_date,t1.fst_login_date) BETWEEN 22 AND 28 ,1,0)) as w4--第四周是否活跃的标记,活跃为1,不活跃为0
max(if(datediff('2022-03-29',t1.fst_login_date)>0,1,0)) as fm1,--判断用户的第一次登陆时间与当前时间的差值,判断其是否可以参与第一周活跃总人数的计算
max(if(datediff('2022-03-29',t1.fst_login_date)>7,1,0)) as fm2,--判断用户的第一次登陆时间与当前时间的差值,判断其是否可以参与第二周活跃总人数的计算
max(if(datediff('2022-03-29',t1.fst_login_date)>14,1,0)) as fm3,    --判断用户的第一次登陆时间与当前时间的差值,判断其是否可以参与第三周活跃总人数的计算
max(if(datediff('2022-03-29',t1.fst_login_date)>21,1,0)) as fm4     --判断用户的第一次登陆时间与当前时间的差值,判断其是否可以参与第四周活跃总人数的计算
FROM
(
--从用户表中获取上海市的数据
SELECT
user_id,
user_big_type,
fst_login_date
FROM tb_user
WHERE user_big_type='上海市'
) t1
JOIN
(
--一个用户一天可能会登录多次,一天只需要留一天登录数据,jion时可以减少数据量
SELECT
user_id,
login_date
FROM tb_login
GROUP BY user_id,login_date
) t2
ON t1.user_id=t2.user_id
GROUP BY t1.user_id,t1.user_big_type
)

SELECT
user_big_type as '维度',
sum(w1)/sum(fm1) as '第一周的留存率',
sum(w2)/sum(fm2) as '第二周的留存率',
sum(w3)/sum(fm3) as '第三周的留存率',
sum(w4)/sum(fm4) as '第四周的留存率',
FROM tmp