背景
假设你现在负责支付宝会员业务，为提高用户粘性，产品上线了新一版签到领积分兑换的功能，现希望了解新功能的用户使用情况，希望得知连续签到3天及以上的用户ID和对应符合条件的连续签到起始日期？
表描述
用户签到日志表：user_sign_log第一列是用户id，第二列是用户签到日期
说明：签到功能一天仅需要签到一次
输出要求
- user_id
- start_sign_dt
- continous_days
解题
在msyql8环境下运行
思路一
连续？本质是等差数列，间隔都是1。所以要构造一列签到日期间隔rn值，如果签到日期sign_in_time连续，新增的rn列值也连续的，大家一起递增，则自然作差相减的值grp即为一个固定的值。最后算出每个user_id的连续签到时间即可。
WITH RankedSignIns AS ( SELECT user_id, DATE(sign_in_time) AS sign_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY DATE(sign_in_time)) AS rn FROM user_sign_log GROUP BY user_id, sign_date ), GroupedSignIns AS ( SELECT user_id, sign_date, DATE_SUB(sign_date, INTERVAL rn DAY) AS grp FROM RankedSignIns ) SELECT user_id, MIN(sign_date) AS start_sign_dt, COUNT(*) AS continous_days FROM GroupedSignIns GROUP BY user_id, grp HAVING COUNT(*) >= 3;
思路二
实际差值天数=理论差值天数。找到当前签到日期后两条记录的日期b，对比当前签到日期两天后的日期与b是否相等，相等则符合条件。
SELECT user_id FROM ( SELECT user_id, sign_in_dt, after2_sign_dt, DATEDIFF(after2_sign_dt, sign_in_dt) AS days_diff FROM ( SELECT *, LEAD(sign_in_dt, 2) OVER (PARTITION BY user_id ORDER BY sign_in_dt) AS after2_sign_dt FROM ( SELECT user_id, DATE(sign_in_time) AS sign_in_dt FROM user_sign_log GROUP BY user_id, DATE(sign_in_time) ) t1 ) t2 ) t3 WHERE days_diff = 2 GROUP BY user_id;
自测
现有一张用户签到表（user_sign_d）,标记用户每日是否签到，表结构如下
- sign_date: 日期
- user_id: 用户ID
- if_sign：当日是否签到,1表示签到，0表示未签到
问题：请计算每个用户最长连续签到天数？
输出表结构如下:
- user_id: 用户ID
- max_continuous_days: 最大连续签到天数
SELECT user_id, MAX(consecutive_days) AS longest_consecutive_days FROM ( SELECT user_id, COUNT(*) AS consecutive_days FROM ( SELECT user_id, sign_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY sign_date) - ROW_NUMBER() OVER (PARTITION BY user_id, if_sign ORDER BY sign_date) AS grp FROM user_sign_d WHERE if_sign = 1 ) AS RankedSigns GROUP BY user_id, grp ) AS ConsecutiveSigns GROUP BY user_id;
