题目
给定一个包含事件日志的表格，找出连续访问平台时间最长的前五个用户。
注意：连续访问是指用户在连续的几天内每天至少访问一次平台。
示例：
输入：
events 表
Column | Type |
|---|---|
user_id | INTEGER |
created_at | DATETIME |
url | VARCHAR |
输出：
Column | Type |
|---|---|
user_id | INTEGER |
streak_length | INTEGER |
答案
解题思路
我们需要找出连续访问平台时间最长的前五名用户。首先，我们要确保只从created_at列中为每个用户选择不重复的日期，以免重复日期错误地中断连续访问记录。
SELECT *
FROM events
GROUP BY created_at, user_id) dates
之后，第一步是找到一种计算每个用户从created_at列中的“连续访问记录”的方法。这是一个“间隔和岛屿”问题，其中数据被分成连续值的“岛屿”，由“间隔”分隔（例如1-2-3, 5-6, 9-10）。一个巧妙的技巧是利用两个等增量序列相减将为每对值产生相同的差值。
例如，[1, 2, 3, 5, 6] - [0, 1, 2, 3, 4] = [1, 1, 1, 2, 2]。
通过创建一个新的列来包含此类减法的结果，我们可以对每个用户的连续访问记录进行分组和计数。对于我们的增量序列，我们可以使用每个事件的行号，可以通过窗口函数ROW_NUMBER()或DENSE_RANK()获得。这两个函数之间的区别在于它们处理重复值的方式，但由于我们需要删除重复值以准确计算连续访问记录，所以它们之间没有区别。
这里用到的函数是：
DATE_ADD(column, interval): 用于将指定的时间间隔添加到给定的日期或时间值。在这个例子中，created_at是要添加时间间隔的列，而INTERVAL -ROW_NUMBER() DAY表示要减去的天数。ROW_NUMBER() OVER (PARTITION BY column ORDER BY column): 用于为每个分区内的记录分配一个唯一的行号。在这个例子中，user_id是分区依据，即根据不同的用户ID进行分组；ORDER BY created_at表示按照创建时间的顺序对每个分组内的记录进行排序。
SELECT
DATE(DATE_ADD(created_at, INTERVAL -ROW_NUMBER()
OVER (PARTITION BY user_id ORDER BY created_at) DAY)) AS grp,
user_id,
created_at
FROM (
SELECT *
FROM events
GROUP BY created_at, user_id) dates
将事件归类为连续访问记录后，只需按连续访问记录分组，计算每个组的数量，为每个用户选择最高的连续访问记录，并对前五名用户进行排名。
易错点
- 排除重复日期
- 分组计算方法：
答案代码
WITH grouped AS (
SELECT
DATE(DATE_ADD(created_at, INTERVAL -ROW_NUMBER()
OVER (PARTITION BY user_id ORDER BY created_at) DAY)) AS grp,
user_id,
created_at
FROM (
SELECT *
FROM events
GROUP BY created_at, user_id) dates
)
SELECT
user_id, streak_length
FROM (
SELECT user_id, COUNT(*) as streak_length
FROM grouped
GROUP BY user_id, grp
ORDER BY COUNT(*) desc) c
GROUP BY user_id
LIMIT 5
