大数据开发面经汇总【持续更新...】
我的大数据学习之路
大数据开发面试笔记V6.0
来自字节跳动数据开发一面
-- 举例如下:
-- 输入
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')
;
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
;
#数据人的面试交流地##sql##大数据开发#