数分面试题-SQL常见面试题型1

1、连续时间问题

1.1 最近一周内的活跃天数

最近一周：timestampdiff(day,time,now())<=7
group by 可以起到去重的作用

select user_id，count(1)
from
	(select user_id,date from t where timestampdiff(day,time,now())<=7
	group by user_id,date)
group by user_id

1.2 每个用户一周内最大连续活跃天数

思路：排序-减去-- 计数
date_sub(日期，天数)

select user_id,max(count(diff))
from
(select date ,user_id,date_sub(date,rank) as diff
from
	(select date ,user_id ,row_number()over