SQL面试题总结_统计每天符合a操作后是b操作,ab操作必须相邻

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

方法二

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

–方法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
1
2
3
4
5
6
7
8
9
10