一、连续问题：计算每个用户的连续登录天数

题目
用户登录表 login 包含字段 user_id、login_date（日期格式 yyyy-MM-dd）。
要求：输出每个用户的最大连续登录天数，以及最近一次连续登录的起始日期和结束日期。

示例数据

user_id	login_date
1	2024-01-01
1	2024-01-02
1	2024-01-03
1	2024-01-05
2	2024-01-01
2	2024-01-02

期望输出

user_id	max_consecutive_days	start_date	end_date
1	3	2024-01-01	2024-01-03
2	2	2024-01-01	2024-01-02

SQL（Hive/Spark SQL）

WITH user_dates AS (
    SELECT DISTINCT user_id, login_date
    FROM login
),
date_rank AS (
    SELECT user_id, login_date,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
    FROM user_dates
),
group_flag AS (
    SELECT user_id, login_date,
           DATE_SUB(login_date, rn) AS group_date   -- 连续登录的组标识
    FROM date_rank
),
consecutive_group AS (
    SELECT user_id, group_date,
           MIN(login_date) AS start_date,
           MAX(login_date) AS end_date,
           COUNT(1) AS consecutive_days
    FROM group_flag
    GROUP BY user_id, group_date
)
SELECT user_id, 
       MAX(consecutive_days) AS max_consecutive_days,
       MAX(start_date) KEEP (DENSE_RANK LAST ORDER BY consecutive_days) OVER (PARTITION BY user_id) AS start_date,
       MAX(end_date)   KEEP (DENSE_RANK LAST ORDER BY consecutive_days) OVER (PARTITION BY user_id) AS end_date
FROM consecutive_group
GROUP BY user_id;

注：部分 SQL 方言（如 Hive）不支持 KEEP，可使用子查询 ROW_NUMBER 按 consecutive_days DESC 排序取第一条。

核心思路

• 使用 ROW_NUMBER() 按日期排序生成序号 rn。
• 计算 login_date - rn，连续登录的该差值相同。
• 按用户和差值分组，得到每组起止日期和天数。
• 取最大天数对应的起止日期。

关键点

• 需要先对用户去重（同一用户同一天只算一次）。
• 日期减法函数用 DATE_SUB 或 date_add 取决于 SQL 引擎。

---

二、TopN问题：每个品类销售额最高的前三名商品

题目
订单表 orders 包含字段 category（品类）、product_id（商品）、sales（销售额）。
要求：输出每个品类下销售额排名前3的商品，并列按商品ID升序。

示例数据

category	product_id	sales
手机	A	100
手机	B	200
手机	C	200
手机	D	50
电脑	E	300
电脑	F	150

期望输出

category	product_id	sales	rank
手机	B	200	1
手机	C	200	2
手机	A	100	3
电脑	E	300	1
电脑	F	150	2

SQL

WITH product_sales AS (
    SELECT category, product_id, SUM(sales) AS total_sales
    FROM orders
    GROUP BY category, product_id
),
ranked AS (
    SELECT category, product_id, total_sales,
           ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_sales DESC, product_id) AS rn
    FROM product_sales
)
SELECT category, product_id, total_sales, rn
FROM ranked
WHERE rn <= 3;

核心思路

• 先聚合得到每个商品的总销售额。
• 使用 ROW_NUMBER() 在品类内按销售额降序、商品ID升序排序。
• 筛选排名 ≤3。

关键点

• 若允许并列（如第二名有两个），可用 RANK() 或 DENSE_RANK()，但注意会影响前3的数量。
• 一般笔试题要求“销售额相同按商品ID升序”，故 ORDER BY 中加 product_id。

---

三、用户留存：计算每日新增用户的次日、7日留存率

题目
登录表 user_login 字段：user_id、login_date。
要求：计算 2024年1月1日到1月10日 每天新增用户的次日留存率和7日留存率（第7天是否活跃）。

示例数据

user_id	login_date
1	2024-01-01
1	2024-01-02
2	2024-01-01
2	2024-01-08
…	…

SQL

WITH first_login AS (
    SELECT user_id, MIN(login_date) AS install_date
    FROM user_login
    WHERE login_date BETWEEN '2024-01-01' AND '2024-01-10'
    GROUP BY user_id
),
active_dates AS (
    SELECT DISTINCT user_id, login_date
    FROM user_login
    WHERE login_date BETWEEN '2024-01-01' AND '2024-01-17'   -- 覆盖1月1日后的7天
)
SELECT 
    f.install_date,
    COUNT(DISTINCT f.user_id) AS new_users,
    ROUND(COUNT(DISTINCT CASE WHEN a.login_date = DATE_ADD(f.install_date, 1) THEN a.user_id END) * 1.0 / COUNT(DISTINCT f.user_id), 4) AS retention_1d,
    ROUND(COUNT(DISTINCT CASE WHEN a.login_date = DATE_ADD(f.install_date, 7) THEN a.user_id END) * 1.0 / COUNT(DISTINCT f.user_id), 4) AS retention_7d
FROM first_login f
LEFT JOIN active_dates a ON f.user_id = a.user_id
GROUP BY f.install_date
ORDER BY f.install_date;

核心思路

• 计算每个用户的首次登录日（新增日）。
• 关联用户的活跃日期表。
• 用条件聚合判断在 install_date+1 和 install_date+7 是否有登录。
• 注意日期范围需要覆盖到第7天之后。

关键点

• 必须使用 LEFT JOIN，否则会丢失未回访的用户。
• 分母是新增用户数，分子是留存用户数，注意去重。
• 日期加减用 DATE_ADD（MySQL）或 DATEADD（SQL Server）等。

---

四、行列转换：学生成绩行转列

题目
成绩表 score：student_id、subject（科目）、score。
要求：输出每个学生各科目的成绩，一行展示，列名为科目名称（语文、数学、英语）。

示例数据

student_id	subject	score
1	语文	90
1	数学	85
1	英语	88
2	语文	78
2	数学	92

期望输出

student_id	语文	数学	英语
1	90	85	88
2	78	92	NULL

SQL

SELECT student_id,
       MAX(CASE WHEN subject = '语文' THEN score END) AS 语文,
       MAX(CASE WHEN subject = '数学' THEN score END) AS 数学,
       MAX(CASE WHEN subject = '英语' THEN score END) AS 英语
FROM score
GROUP BY student_id;

核心思路

• 使用 CASE WHEN 将科目作为列值。
• 配合聚合函数（MAX 或 SUM）把多行压缩为一行。
• 每个学生每个科目只有一个值，聚合函数不影响结果。

关键点

• 如果每个学生每个科目可能有多个成绩（如多次考试），需要先聚合或使用 SUM 求和。
• 列数固定，若科目不确定需动态拼接，但笔试通常写静态。

---

五、直播最高同时在线人数

题目
直播间用户进出记录表 live_log：user_id、room_id、event_time、event_type（‘enter’ 或 ‘exit’）。
要求：计算每个直播间在当天最高同时在线人数。

示例数据

user_id	room_id	event_time	event_type
1	101	2024-01-01 10:00:00	enter
1	101	2024-01-01 10:15:00	exit
2	101	2024-01-01 10:05:00	enter
2	101	2024-01-01 10:20:00	exit

期望输出

room_id	max_online
101	2

SQL

WITH online_change AS (
    SELECT room_id, event_time,
           CASE WHEN event_type = 'enter' THEN +1 ELSE -1 END AS delta
    FROM live_log
    WHERE DATE(event_time) = '2024-01-01'   -- 指定日期
),
online_cnt AS (
    SELECT room_id, event_time,
           SUM(delta) OVER (PARTITION BY room_id ORDER BY event_time) AS current_online
    FROM online_change
)
SELECT room_id, MAX(current_online) AS max_online
FROM online_cnt
GROUP BY room_id;

核心思路

• 进入事件为 +1，离开事件为 -1。
• 按时间排序，计算累积和（窗口函数 SUM OVER）得到每个时刻的在线人数。
• 取最大值。

关键点

• 若同一用户同房间进出时间重叠，需要确保离开时间 > 进入时间，一般数据已保证。
• 如果有多个事件同一时间，通常先加后减（即进入先计算，离开后计算），可用 ORDER BY event_time, event_type DESC 调整顺序。

---

六、算法题：滑动窗口最大值

题目
给定整数数组 nums 和窗口大小 k，求每个滑动窗口的最大值。
示例：nums = [1,3,-1,-3,5,3,6,7], k=3
输出：[3,3,5,5,6,7]

Python 解法（双端队列）

from collections import deque

def maxSlidingWindow(nums, k):
    dq = deque()          # 存储索引，保证队首是当前窗口最大值
    result = []
    for i, num in enumerate(nums):
        # 移除不在窗口内的队首索引
        if dq and dq[0] < i - k + 1:
            dq.popleft()
        # 移除队尾所有小于当前值的索引（它们不可能成为最大值）
        while dq and nums[dq[-1]] < num:
            dq.pop()
        dq.append(i)
        # 窗口形成后记录最大值
        if i >= k - 1:
            result.append(nums[dq[0]])
    return result

核心思路

• 使用单调递减双端队列，队首始终是窗口最大值。
• 新元素从队尾加入前，弹出所有比它小的元素。
• 每次窗口滑动后，队首过期则弹出。

复杂度

• 时间 O(n)，空间 O(k)。

---