牛客大厂笔试题笔记-SQL 40-44（自用）

SQL40 每个月Top3的周杰伦歌曲：从听歌流水中找到18-25岁用户在2022年每个月播放次数top 3的周杰伦的歌曲。
答案：
select sub.month, sub.ranking, sub.song_name, sub.play_pv
from (select MONTH(p.fdate) AS month,
ROW_NUMBER() OVER (
PARTITION BY month(p.fdate)
ORDER BY count() desc, p.song_id Asc
) AS ranking,
s.song_name,
count() as play_pv
from play_log p
join song_info s on p.song_id = s.song_id
join user_info u on p.user_id = u.user_id
where s.singer_name = '周杰伦' and year(fdate) = 2022 and u.age between 18 and 25
group by month(p.fdate), s.song_name, s.song_id) sub
where sub.ranking <=3
order by sub.month asc, sub.play_pv desc

这是一个经典的用到子查询的题目。
首先我门要明确我们 GROUP → RANK → FILTER 这条链条。让你以后看到任何 Top-N 分组题都不会乱。

① GROUP —— 你到底在给什么排名？

你不是在给“播放记录”排名
你是在给“每个月的每一首歌”排名

所以第一件事永远是问：
我想比较的对象是谁？本题是：(月份, 歌曲)

所以你必须先把流水变成这种表：

month song play_pv
1 明明就 4
1 说好的幸福呢 4
1 大笨钟 2
2 明明就 2
2 说好的幸福呢 1
2 大笨钟 1

这一步就是：

GROUP BY month, song
COUNT(*) AS play_pv
没有这一层，你连“排名对象”都没定义清楚。

② RANK —— 在“每个组里面”排

现在你有了：每个月 → 多首歌 → 各自的播放量

接下来你要的是：在每个月内部，按播放量从大到小排序

这就是窗口函数：

ROW_NUMBER() OVER (
PARTITION BY month
ORDER BY play_pv DESC
)

你是在做：一月一张榜，二月一张榜，三月一张榜，不是全表一张榜。

③ FILTER —— 只留下前 N

窗口函数不会删除数据，它只是在“标号”。

比如：

month song play_pv ranking
1 明明就 4 1
1 说好的幸福呢 4 2
1 大笨钟 2 3
1 … … 4
2 明明就 2 1
2 说好的幸福呢 1 2

最后一步就是：

WHERE ranking <= 3

用一句话总结这条链

GROUP 决定“比什么”
RANK 决定“怎么比”
FILTER 决定“留下谁”

SQL41 最长连续登录天数
描述：你正在搭建一个用户活跃度的画像，其中一个与活跃度相关的特征是“最长连续登录天数”， 请用SQL实现“2023年1月1日-2023年1月31日用户最长的连续登录天数”

答案：
WITH ordered_logins AS (
SELECT
user_id,
fdate,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY fdate) AS rn
FROM
tb_dau
WHERE
fdate BETWEEN '2023-01-01' AND '2023-01-31'
),
grouped_logins AS (
SELECT
user_id,
fdate,
rn,
DATE_SUB(fdate, INTERVAL rn DAY) AS grp
FROM
ordered_logins
)
SELECT
user_id,
MAX(consec_days) AS max_consec_days
FROM (
SELECT
user_id,
grp,
COUNT(*) AS consec_days
FROM
grouped_logins
GROUP BY
user_id, grp
) AS consecutive
GROUP BY
user_id;

这个题型我第一次见“最长连续登录天数”

首先，我们需要对每个用户的登录日期进行排序，并为每个日期分配一个序号。
我们使用ROW_NUMBER()窗口函数为每个用户的登录日期分配一个序号：
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY fdate) AS rn

第二，通过计算每个日期减去其序号的结果，我们可以将连续的日期分组。
DATE_SUB(fdate, INTERVAL rn DAY)：通过把fdate减去rn天，连续的日期将具有相同的结果，从而可以分组。

fdate	rn	fdate - rn
01-01	1	12-31
01-02	2	12-31
01-04	3	01-01
01-05	4	01-01
01-06	5	01-01

最后：对每个分组计算连续天数，并找出最长的连续天数。

user_id	grp	consec_days
A	12-31	2
A	01-01	3

COUNT(*) AS consec_days：计算每个分组的数量（具有相同天数的数量），即可得到连续天数。
MAX(consec_days) AS max_consec_days：找出每个用户的最长连续天数。
这里用道了子查询。

其中的table只是举个例子不是本身题的table。

SQL42 分析客户逾期情况

答案：
select c.pay_ability, CONCAT(ROUND(SUM(CASE WHEN l.overdue_days IS NOT NULL THEN 1 ELSE 0 END)/COUNT()100,1),'%') AS overdue_ratio
from loan_tb l
join customer_tb c on l.customer_id = c. customer_id
group by c.pay_ability
order by overdue_ratio desc

这道题的点在于 CONCAT(ROUND(SUM(CASE WHEN lt.overdue_days IS NOT NULL THEN 1 ELSE 0 END)/COUNT()100,1),'%') AS overdue_ratio
里面的语法，固定搭配要记牢。

CASE WHEN lt.overdue_days IS NOT NULL THEN 1 ELSE 0 END：判断客户是否有逾期行为，overdue_days字段不为NULL即记作1，否则记作0。
SUM(...)：计算有逾期行为的客户数量，即上一步记作1的客户数量。
COUNT(*)：计算总客户数量。
ROUND(...,1)：将结果四舍五入保留一位小数。
CONCAT(...,'%')：输出格式为‘数字’+‘%’，此处使用字符串操作，CONCAT(A,B)即将A字符串和B字符串连接成‘AB’字符串。

SQL43 获取指定客户每月的消费额
答案:
SELECT
DATE_FORMAT(t.t_time, '%Y-%m') AS time,
ROUND(SUM(t.t_amount),1) AS total
FROM
trade t
JOIN
customer c ON t.t_cus = c.c_id
WHERE
t.t_type = 1
AND c.c_name = 'TOM'
AND YEAR(t.t_time) = 2023
GROUP BY
time
ORDER BY
time

对于我来说这道题的点在于 DATE_FORMAT(t.t_time, '%Y-%m') AS time：将交易时间格式化为年月。

SQL44 查询连续入住多晚的客户信息？

答案：
SELECT
c.user_id,
c.room_id,
g.room_type,
DATEDIFF(DATE(c.checkout_time), DATE(c.checkin_time)) AS days
FROM checkin_tb c
JOIN guestroom_tb g
ON c.room_id = g.room_id
WHERE DATE(c.checkin_time) = '2022-06-12'
AND DATEDIFF(DATE(c.checkout_time), DATE(c.checkin_time)) >= 2
ORDER BY
days ASC,
c.room_id ASC,
c.user_id DESC;

select *
from(
select c.user_id, c.room_id, g.room_type, DATEDIFF(DATE(c.checkout_time), DATE(c.checkin_time)) AS days
from checkin_tb c
join guestroom_tb g on c.room_id = g.room_id
WHERE DATE(c.checkin_time) = '2022-06-12'
) as t
where t.days >=2
order by days asc, room_id asc, user_id desc

这道题是在考对“时间 + SQL 执行逻辑 + 业务语义”的理解是否准确。

第一，酒店行业里：连续入住天数 = 住了几晚。也就是：退房日期 − 入住日期。不是 24 小时的整天数。

你如果用：TIMESTAMPDIFF(DAY, checkin_time, checkout_time)就错了。你算的是：过了几个完整的 24 小时。而题目要的是：跨了几个晚上（日历天）
所以一定要用：DATEDIFF(DATE(checkout_time), DATE(checkin_time))

第二，DATETIME 一定要先转成 DATE，在某些数据库中会隐式截断、在某些会报错、在某些会变成 0。凡是算“天 / 晚 / 日期差”，一定要：先 DATE() 再 DATEDIFF()。这是数据仓库工程的基本功。

第三，SQL的执行顺序是
FROM
→ JOIN
→ WHERE
→ SELECT
→ ORDER BY
days 在 WHERE 时还不存在，只能在 ORDER BY 用，想在 WHERE 用 → 必须用子查询 / CTE，这就是你为什么写子查询的原因。
不写子查询的话就再写一遍WHERE DATE(c.checkin_time) = '2022-06-12'AND DATEDIFF(DATE(c.checkout_time), DATE(c.checkin_time)) >= 2

第四，我一开始写了 GROUP BY，这也是一个典型新手错误。
现在做的是：一条入住记录 → 算这一次住了几晚，这是 行级计算（row-level）
不是一个用户 → 总共住了几晚（aggregation）
所以：不该用 GROUP BY，不该用 HAVING