推荐阅读文章列表

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

我的大数据学习之路

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

SQL题目

来自字节跳动数据开发一面

• 有一张用户登录日志表ods_login_usr_log, 包含user_id（用户id）和login_dt（登录日期）
• 问题：计算每个用户最大的连续登录天数，可以间隔一天

-- 举例如下：
-- 输入
use_id   login_dt
1001   20240812
1002   20240812
1001   20240813
1001   20240814
1001   20240816
1002   20240816
1001   20240819
1002   20240817
1001   20240820
-- 输出
user_id   max_cnt
1001        5
1002        2

答案解析

模拟数据

create table ods_login_usr_log (
user_id varchar(20),
login_dt varchar(20)
);
INSERT INTO ods_login_usr_log VALUES 
('1001','20240812'),
('1002','20240812'),
('1001','20240813'),
('1001','20240814'),
('1001','20240816'),
('1002','20240816'),
('1001','20240819'),
('1002','20240817'),
('1001','20240820')
;

思路分析

• 本题不同于常见的《用户最大连续登录天数》
• 回顾一下如何求用户最大连续登录天数
  • 首先计算出连续分组id：登录日期 - 按照用户id分组登录日期进行排序的排名
  • 最后按照用户id和分组id进行分组计数
• 同上述题目一样，本题的解题关键是 如何计算出连续分组id
  • 我们反向分析以下，如何将间隔一天的登录放到同一个分组中
  • 只要相邻两个登录日期相差不超过2即为同一个分组，如果超过2就会被分到新组中
• 此时思路就很明显了，先获取当前行上一次登录日期并相减得到差值，然后判断差值是否大于2，如果大于2就开一个新的组，否则被分为同一个组中，最后根据用户id和分组id进行分组，计算最大登录日期-最小登录日期+1即可得到连续登录天数，再取最大值即可

具体代码

select 
    user_id,
    max(cnt) as max_cnt
from (
    select 
        user_id,
        datediff(max(login_dt), min(login_dt)) + 1 as cnt
    from (
        select 
            user_id, 
            login_dt, 
            sum(if(diff > 2, 1, 0)) over(partition by user_id order by login_dt) groupid
        from (
            select 
                user_id, 
                login_dt, 
                datediff(login_dt, lag(login_dt, 1, 0) over(partition by user_id order by login_dt)) diff
            from ods_login_usr_log
        ) t1
    ) t2
    group by user_id, groupid
) t3
group by user_id
;