SQL面试题总结

1.表t_act_records表，包含两个字段：uid（用户ID），imp_date（日期yyyy-mm-dd） 连续时间问题

• 1）计算2020年每个月，每个用户连续签到的最多天数
• 2）计算2020年每个月，连续2天都有登陆的用户名单
• 3）计算2020年每个月，连续5天都有登陆的用户数

1）计算2020年每个月，每个用户连续签到的最多天数

with temp_1 as (
	select distinct uid, imp_date
	from t_act_records
	where year(imp_date)=2020
	)
	,temp_2 as(
	select uid, imp_date,row_number()over(partition by uid,month(imp_date) order by imp_date) rank
	from temp_1
	)
	,temp_3 as(
	select uid,month(imp_date)as month,date_sub(imp_date,rank),count(1) as cnt
	from temp_2
	group by 1,2,3)
select month,uid,max(cnt)
from temp_3
group by 1,2

2)计算2020年每个月，连续2天都有登陆的用户名单

with temp_1 as (
	select distinct uid, imp_date
	from t_act_records
	where year(imp_date)=2020
	)
	,temp_2 as(
	select uid, imp_date,lead(impt_date,1) over(partiton by month(impt_date),uid order by imp_date) 第二次登陆日期
	from temp_1
	)
	,temp_3 as (
	select uid,month(impt_date),datediff(第二次登陆日期,imp_date) 差值
	from temp_2
	)
select month(imp_date),uid
from temp_3
group by 1,2
having 差值=1

方法二

with temp_1 as (
	select distinct uid, imp_date
	from t_act_records
	where year(imp_date)=2020
	)
	,temp_2 as(
	select uid, imp_date,row_number()over(partition by uid,month(imp_date) order by imp_date) rank
	from temp_1
	)
	,temp_3 as(
	select uid,month(imp_date)as month,date_sub(imp_date,rank) as diff
	from temp_2
	)
select month,uid,
from temp_3
group by 1,2
having count(diff)>2

3）计算2020年每个月，连续5天都有登陆的用户数

with temp_1 as (
	select distinct uid, imp_date
	from t_act_records
	where year(imp_date)=2020
	)
	,temp_2 as(
	select uid, imp_date,row_number()over(partition by uid,month(imp_date) order by imp_date) rank
	from temp_1
	)
	,temp_3 as(
	select uid,month(imp_date)as month,date_sub(imp_date,rank) as diff
	from temp_2
	)
select month,count(distinct uid),
from temp_3
group by 1
having count(diff)>5

–方法2

with temp_1 as (
	select distinct uid, imp_date
	from t_act_records
	where year(imp_date)=2020
	)
	,temp_2 as(
	select uid, imp_date,row_number()over(partition by uid,month(imp_date) order by imp_date) rank
	from temp_1
	)
	,temp_3 as(
	select u