SQL面试题

1. 找出连续7天登陆的用户

该问题可衍生解决如下常见的业务场景：

1. 求出连续登录7天的用户/用户数
2. 求出连续充电次数大于等于12次的用户数
3. 求出连续3年获得冠军的选手
4. ......
   步骤1：去重。每个用户每天只保留一次登录记录

select distinct user_id, date from input

步骤2：开窗函数。对每一个客户，按照时间进行排序

select *, row_number() over (partition by user_id order by date) as `rank`  from input

步骤3：计算date-rank的日期

select *, date_sub(date, rank) as `temp_date`  from input

步骤4：以客户和 temp_date字段为主键进行分组，计算条目数，条目数大于等于7的，即为连续登录7天

select  user_id， temp_date，count(*) from input
group by user_id， temp_date
having count(*) >= 7

综上：

select  user_id， temp_date，count(*) 
from (
	-- 计算基准时间
	select *, date_sub(date, rank) as `temp_date`  
	from (
		-- 排序
		select *, row_number() over (partition by user_id order by date) as `rank`  
		from (
   			 -- 去重
			select distinct user_id, date from input
			 )
		)
	)
group by user_id， temp_date
having count(*) >= 7

2. 最大连续天数的变形问题 。求连续点击三次的用户数,中间不能有别人的点击

select user_id, diff, count(*) 
from (
	select *, (rank1-rank2) as `diff` 
	from (
		select *
			, row_number() over (order by date )  as `rank1`
        	, row_number() over (partition by user_id order by date )  as `rank2`
		from input
		)
	)
group by user_id, diff
having count(*) >= 3

3. 电商公司用户访问时间数据，某个用户连续的访问记录如果时间间隔小于 60 秒，则分为同一个组。