推荐阅读文章列表

大数据开发面经汇总【持续更新...】

我的大数据学习之路

大数据开发面试笔记V6.0

SQL题目

来自字节、阿里、腾讯、pdd等数据研发一面二面

• 有一张用户登录日志表ods_usr_login_log, 包含user_id（用户id）、login_dt（登录日期），一个用户当天可能登录多次
• 问题：计算存在新用户登录的日期的次日留存率以及3日留存率
  • N日留存用户数：某日活跃的用户在之后的第N日活跃用户数

答案解析

模拟数据

INSERT INTO ods_usr_login_log VALUES 
('001','20240701'),
('001','20240701'),
('002','20240701'),
('003','20240701'),
('001','20240702'),
('002','20240702'),
('002','20240702'),
('001','20240703'),
('002','20240704'),
('004','20240704')
;

思路分析

1. 首先按照用户和登录日期进行去重，因为每个用户在当天可能登录多次
2. 题干要求是有新用户登录，等价于求用户的首次登录日期
3. 如果用户登录时间和用户首次登录时间相差N天即为N日留存用户

具体代码

with t1 as (
    select user_id, cast(min(login_dt) as date) as dt
    from ods_usr_login_log
    group by user_id
),
t2 as (
    select user_id, cast(login_dt as date) as dt
    from ods_usr_login_log
    group by user_id, cast(login_dt as date) 
)
select 
    t1.dt
    ,count(distinct case when datediff(t2.dt, t1.dt) = 1 then t2.user_id else null end) / count(distinct t1.user_id) as retain_1d_rate
    ,count(distinct case when datediff(t2.dt, t1.dt) = 3 then t2.user_id else null end) / count(distinct t1.user_id) as retain_3d_rate
from t1
left join t2
on t1.user_id = t2.user_id
group by t1.dt
;