sql面试题（连续登录相关）

1.连续登录三天
思路分析：
对用户登录记录按照用户ID和登录日期进行排序。
使用ROW_NUMBER()函数为每个用户的登录记录分配一个序列号，序列号的分配依据是登录日期的先后顺序。
计算相邻登录记录之间的日期差异，如果这个差异为1，则表明这两个登录记录是连续的。
根据日期差异，筛选出连续登录的用户记录。
步骤
数据去重：由于同一用户可能在同一天内有多次登录记录，因此首先需要对登录记录进行去重处理，只保留每个用户每天的一条记录。
排序：使用ORDER BY语句对去重后的记录按照用户ID和登录日期进行排序。
分配序列号：使用ROW_NUMBER()函数为每个用户的登录记录分配序列号，序列号会随着日期依次递增。
计算日期差异：通过DATE_SUB()函数计算当前登录日期与前一登录日期的差异，如果这个差异为1，则认为这两个登录记录是连续的。减去间隔排序天数。
筛选连续登录记录：根据日期差异的结果，筛选出连续登录的用户记录。
代码补充：WITH t1 AS (
SELECT DISTINCT uid, date(login_time) ymd
FROM t_login),
t2 as (
select uid,ymd,row_number() over (partition by uid order by ymd) as ra,
date_sub(ymd,interval (row_number() over (partition by uid order by ymd)) day) sub_date
from t1)
select uid from t2
group by uid
having count(*)>=3；
方法二，Lag()函数和datediff()
– 窗口函数lag
with t1 as (
select distinct uid, date(login_time) ymd
from t_login
),
t2 as (
select uid,ymd,datediff(ymd,lag(ymd,2) over (partition by uid order by ymd)) diff
from t1
)
select * from t2
where diff=2

结果表：
如果用户在1月1日、1月2日和1月3日连续登录，那么在1月3日的记录中，LAG(ymd, 2)将返回1月1日的日期，而在1月2日的记录中，LAG(ymd, 2)将返回NULL或者一个特定的值，因为在1月2日时没有前两天的日期。

2.连续登录最大天数用户
*思路前部与连续登录3天相同，这里构建表t3，根据uid对t2的结果集进行分组，并计算每个分组的数量，将这个数量命名为day_con。这里的day_con代表了每个用户连续登录的天数。这是根据uid而不是uid和sub_date进行分组的。这意味着它计算的是每个用户所有的登录天数；

最后的查询根据day_con的最大值筛选出t3中的记录。这个day_con代表了每个用户连续登录的天数（尽管在t3的分组中实际上计算的是总的登录天数）。这个查询将返回连续登录天数最多的用户的uid和他们连续登录的天数。
WITH t1 AS (
SELECT DISTINCT uid, date(login_time) ymd
FROM t_login),
t2 as (
select uid,ymd,row_number() over (partition by uid order by ymd) as ra,
date_add(ymd,interval (-row_number() over (partition by uid order by ymd)) day) sub_date
from t1),
t3 as (select uid,count(*) as day_con from t2
group by uid)
select uid,day_con as max_day from t3
where day_con=(select max(day_con) from t3);