当前位置：首页 > news >正文

SQL 经典面试题

news 2026/3/26 20:32:59

第 1 题连续问题

如下数据为蚂蚁森林中用户领取的减少碳排放量。找出连续 3 天及以上减少碳排放量在 100 以上的用户。

输入：

id	dt	lowcarbon
1001	2021-12-12	123
1002	2021-12-12	45
1001	2021-12-13	43
1001	2021-12-13	45
1001	2021-12-13	23
1002	2021-12-14	45
1001	2021-12-14	230
1002	2021-12-15	45
1001	2021-12-15	23

输出：

id
1002
1003
1004

建表及插入语句：

-- 创建表
CREATE TABLE carbon_reduction (id INT,dt DATE,lowcarbon INT
);-- 插入测试数据
INSERT INTO carbon_reduction (id, dt, lowcarbon) VALUES
(1001, '2021-12-12', 123),
(1002, '2021-12-12', 45),
(1001, '2021-12-13', 43),
(1001, '2021-12-13', 45),
(1001, '2021-12-13', 23),
(1002, '2021-12-14', 45),
(1001, '2021-12-14', 230),
(1002, '2021-12-15', 45),
(1001, '2021-12-15', 23),
(1001, '2021-12-16', 150),
(1001, '2021-12-17', 110),
(1001, '2021-12-18', 95),
(1001, '2021-12-19', 120),
(1002, '2021-12-16', 130),
(1002, '2021-12-17', 105),
(1002, '2021-12-18', 115),
(1002, '2021-12-20', 125),
(1003, '2021-12-14', 101),
(1003, '2021-12-15', 102),
(1003, '2021-12-16', 103),
(1003, '2021-12-17', 104),
(1003, '2021-12-18', 99),
(1004, '2021-12-13', 110),
(1004, '2021-12-14', 120),
(1004, '2021-12-15', 130);

结果：

select distinct id
from (select *,sum(flag)over(partition by id order by dt) as group_idfrom (select *,if(datediff(dt, lag(dt,1)over(partition by id order by dt)) = 1, 0, 1) as flagfrom carbon_reduction cr where lowcarbon > 100) t1
) t2
group by id, group_id
having count(*) >= 3

第 2 题分组问题

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

输入：

id	ts(秒)
1001	17523641234
1001	17523641256
1002	17523641278
1001	17523641334
1002	17523641434
1001	17523641534
1001	17523641544
1002	17523641634
1001	17523641638
1001	17523641654

输出：

id	ts(秒)	group
1001	17523641234	1
1001	17523641256	1
1001	17523641334	2
1001	17523641534	3
1001	17523641544	3
1001	17523641638	4
1001	17523641654	4
1002	17523641278	1
1002	17523641434	2
1002	17523641634	3

建表语句与插入数据语句：

CREATE TABLE user_visits (id INT,ts BIGINT
);INSERT INTO user_visits (id, ts) VALUES
(1001, 17523641234),
(1001, 17523641256),
(1002, 17523641278),
(1001, 17523641334),
(1002, 17523641434),
(1001, 17523641534),
(1001, 17523641544),
(1002, 17523641634),
(1001, 17523641638),
(1001, 17523641654);

结果代码：

select *,sum(flag) over(partition by id order by ts) as `group`
from (select*,if(ts-lag(ts,1,null)over(partition by id order by ts) < 60, 0, 1) as flagfrom user_visits uv 
) t1

第 3 题间隔连续问题

某游戏公司记录的用户每日登录数据。计算每个用户最大的连续登录天数，可以间隔一天。解释：如果一个用户在 1,3,5,6 号登录游戏，则视为连续 6 天登录。

输入：

-- 创建用户登录记录表
CREATE TABLE user_login (id INT,dt DATE
);-- 测试案例1：用户1001 - 有间隔但符合连续登录规则
INSERT INTO user_login (id, dt) VALUES
(1001, '2021-12-01'),
(1001, '2021-12-03'),  -- 间隔1天
(1001, '2021-12-05'),  -- 间隔1天
(1001, '2021-12-06'),  -- 连续
(1001, '2021-12-08'),  -- 间隔1天
(1001, '2021-12-10'),  -- 间隔1天
(1001, '2021-12-12');  -- 间隔1天-- 测试案例2：用户1002 - 完美连续登录
INSERT INTO user_login (id, dt) VALUES
(1002, '2021-12-01'),
(1002, '2021-12-02'),
(1002, '2021-12-03'),
(1002, '2021-12-04'),
(1002, '2021-12-05');-- 测试案例3：用户1003 - 有间断的情况
INSERT INTO user_login (id, dt) VALUES
(1003, '2021-12-01'),
(1003, '2021-12-02'),
(1003, '2021-12-05'),  -- 中断2天
(1003, '2021-12-07'),  -- 间隔1天
(1003, '2021-12-08'),  -- 连续
(1003, '2021-12-10');  -- 间隔1天-- 测试案例4：用户1004 - 单次登录
INSERT INTO user_login (id, dt) VALUES
(1004, '2021-12-01');-- 测试案例5：用户1005 - 复杂间隔模式
INSERT INTO user_login (id, dt) VALUES
(1005, '2021-12-01'),
(1005, '2021-12-02'),
(1005, '2021-12-04'),  -- 间隔1天
(1005, '2021-12-06'),  -- 间隔1天
(1005, '2021-12-07'),  -- 连续
(1005, '2021-12-08'),  -- 连续
(1005, '2021-12-10'),  -- 间隔1天
(1005, '2021-12-11'),  -- 连续
(1005, '2021-12-13');  -- 间隔1天-- 测试案例6：用户1006 - 跨月登录
INSERT INTO user_login (id, dt) VALUES
(1006, '2021-11-28'),
(1006, '2021-11-30'),  -- 间隔1天
(1006, '2021-12-01'),  -- 连续（跨月）
(1006, '2021-12-03');  -- 间隔1天-- 测试案例7：用户1007 - 有较长间断
INSERT INTO user_login (id, dt) VALUES
(1007, '2021-12-01'),
(1007, '2021-12-03'),  -- 间隔1天
(1007, '2021-12-04'),  -- 连续
(1007, '2021-12-10');  -- 中断5天，新的开始

输出：

id  | max_consecutive_days
----|---------------------
1001| 12天 (12-01到12-12，允许间隔1天)
1002| 5天  (完美连续)
1003| 6天  (12-05到12-10，允许间隔1天)
1004| 1天  (单次登录)
1005| 13天 (12-01到12-13，允许间隔1天)
1006| 6天  (11-28到12-03，允许间隔1天，跨月)
1007| 4天  (12-01到12-04，允许间隔1天)

结果代码：

select id,max(consecutive_days) as max_consecutive_days
from (select id,group_id,datediff(max(dt),min(dt))+1 as consecutive_daysfrom (select *,sum(flag)over(partition by id order by dt) as group_idfrom (select *,if(datediff(dt, lag(dt,1)over(partition by id order by dt))<=2, 0, 1) as flagfrom user_login) t1) t2group by id, group_id
) t3
group by id

查看全文

http://www.jsqmd.com/news/139808/