SQL面试题总结
一、面试常见题型
1. 平均问题
SQL32 牛客直播各科目平均观看时长
select course_name, round(avg(TIMESTAMPDIFF(MINUTE, in_datetime, out_datetime)), 2) as avg_Len
from course_tb as t1
right join attend_tb as t2
on t1.course_id = t2.course_id
group by course_name
order by avg_Len desc
去除最高最低的平均
# 使用窗口函数RANK分别对工资salary进行升序和降序排列，就获得了该分组内最低和最高的工资，
# 过滤掉这2条记录再对工资salary进行平均avg即可
select a.department，avg(a.salary)
from
(
select *,
rank() over( partition by department
order by salary ) rank_asc,
rank() over( partition by department
order by salary desc) rank_desc
from emp
) a
group by a.department
where a.rank_asc >1 and a.rank_desc >1
2. 新用户与活跃用户问题
【关键点掌握】
1. 新用户的判定——依据最小登入时间为准；
2. 活跃用户的判定——登入和登出时间都是活跃时间，需要union all 连接
SQL11 每天的日活数及新用户占比
select a.dt,count(distinct a.uid) dau,round(count(distinct b.uid)/count(distinct a.uid),2) # 4. a表是所有活跃记录，b表是新用户活跃记录
from
(select uid,date(in_time) dt from tb_user_log
union all # 1. 登入和登出时间连接（两个时间都可以证明其在对应当天活跃）
select uid,date(out_time) dt from tb_user_log
group by uid,dt # 1.1 分组将用户和时间分组
) a
left join # 2. 左连接
(select uid,min(date(in_time)) dt from tb_user_log group by uid # 3. 求出每个用户的最小时间，即成为新用户的时间
) b
on a.uid=b.uid and a.dt=b.dt # 4. 将用户和时间关联
group by a.dt
order by a.dt;
3. TOP N 问题
SQL37 某乎问答单日回答问题数大于等于3个的所有用户
select answer_date,
author_id,
count(issue_id) answer_cnt
from answer_tb
where month(answer_date) = 11
group by answer_date,author_id
having count(issue_id)>=3
order by answer_date,author_id
select
product_id, user_id, cnt
from
(
select
product_id, user_id, cnt,
rank() over(partition by product_id order by cnt) rn # 2. 每个商品被浏览次数排名
from
(
select # 1. 计算每个商品被每个用户浏览的次数
product_id, user_id, count(*) cnt
from product_view
group by product_id, user_id
)t1
)t2
where rn<=3; # 3. 计算每个商品浏览前3的用户
4. 连续N天问题
SQL29 某宝店铺连续2天及以上购物的用户及其对应的天数
SELECT
user_id, COUNT(*) AS days_count
# 1. 完成内层子查询，DENSE_RANK()排序
FROM ( SELECT
DISTINCT user_id, sales_date, DENSE_RANK() OVER (PARTITION BY user_id ORDER BY sales_date) AS rk
FROM
sales_tb) as t1
# 2. 完成GROUP BY user_id, DATE_SUB(sales_date, INTERVAL rk DAY)
GROUP BY
user_id, DATE_SUB(sales_date, INTERVAL rk DAY)
# 3. 完成HAVING days_count >= 2
HAVING
days_count >= 2
ORDER BY
user_id;
5. 累计问题
SQL4 每个创作者每月的涨粉率及截止当前的总粉丝量
SELECT
author, month, fans_growth_rate,
# 2. 利用开窗函数 SUM(fans_month)，完成累计总量计算
SUM(fans_month) OVER (PARTITION BY author ORDER BY month) AS total_fans
FROM
# 1. 完成内层子查询，【每个用户】（或视频，商品）【每个月】的【粉丝量】（或观看量，销售量）
(SELECT
author, DATE_FORMAT(start_time, '%Y-%m') AS month,
SUM(IF(if_follow = 2, -1, if_follow)) AS fans_month, #1.1 求和得到用户当月总量
ROUND(SUM(IF(if_follow = 2, -1, if_follow)) / COUNT(*), 3) AS fans_growth_rate
FROM
tb_video_info AS t1
LEFT JOIN
tb_user_video_log AS t2
ON
t1.video_id = t2.video_id
WHERE
YEAR(start_time) = 2021
GROUP BY
author, month) AS t #1.2 按照【每个用户】（或视频，商品）【每个月】
ORDER BY
author, total_fans;
6. 最大同时在线问题
UNION ALL连接在线表和下线表sum(diff)over(partition by XX order by XX,XX DESC)开窗函数求和max（XX）求最大值
SQL8 每篇文章同一时刻最大在看人数
SELECT artical_id,max(instant_view) max_uv # 3. max()求最大值
FROM(SELECT artical_id,sum(diff)over(partition by artical_id order by dt,diff DESC) instant_view # 2. 窗函数求和
FROM(SELECT artical_id,in_time dt,1 diff FROM tb_user_log WHERE artical_id!=0 # [关键] 1 diff
UNION ALL # 1. UNION ALL 连接在线表和下线表
SELECT artical_id,out_time dt,-1 diff FROM tb_user_log WHERE artical_id!=0 # [关键] -1 diff
) a
)b
GROUP BY artical_id
ORDER BY max_uv DESC;
SQL34 牛客直播各科目同时在线人数
SELECT T2.course_id, T2.course_name, MAX(T1.num) max_num # 3. max()求最大值
FROM (
SELECT course_id, SUM(tag) OVER(PARTITION BY course_id ORDER BY time, tag DESC) num # 2. 窗函数求和
FROM (
SELECT user_id, course_id, in_datetime time, 1 tag FROM attend_tb # [关键] 1 diff
UNION # 1. UNION ALL 连接在线表和下线表
SELECT user_id, course_id, out_datetime time, -1 tag FROM attend_tb # [关键] -1 diff
) TA
) T1
INNER JOIN course_tb T2 ON T2.course_id = T1.course_id
GROUP BY T2.course_id, T2.course_name
ORDER BY T2.course_id;
7. 次日或7日留存率问题
SQL29 计算用户的平均次日留存率
SELECT
COUNT(q2.device_id) / COUNT(q1.device_id) AS avg_ret
FROM
(SELECT DISTINCT device_id, date FROM question_practice_detail)as q1
LEFT JOIN
(SELECT DISTINCT device_id, date FROM question_practice_detail) AS q2
ON
q1.device_id = q2.device_id AND q2.date = DATE_ADD(q1.date, interval 1 day)
select d.a_day,
count(distinct d.userid) as '活跃用户数',
count(distinct case when d.day_diff=1 then d.userid else null end) as '次日留存数',
count(distinct case when day_diff=1 then d.userid else null end)/count(distinct d.userid) as '次日留存率',
count(distinct case when d.day_diff=3 then d.userid else null end)as '3日留存数',
count(distinct case when day_diff=3 then d.userid else null end)/count(distinct d.userid) as '3日留存率',
count(distinct case when d.day_diff=7 then d.userid else null end) as '7日留存数' ,
count(distinct case when day_diff=7 then d.userid else null end)/count(distinct d.userid) as '7日留存率'
(SELECT
*, date_sub(b_day,a_day) as day_diff
FROM
(SELECT DISTINCT a.userid,
a.active_day AS a_day,
b.active_day AS b_day
FROM active_log AS a
LEFT JOIN active_log AS b
ON a.userid=b.userid) t) tt
二、面经题汇总
充值额最大的账号和金额
求平均成绩大于80，且0001课程分数高于0002课程分数的学生id
每个班每科成绩top10（怎么优化/不使用窗函数怎么做）
给一个班的数据成绩进行排名
公交车人数最高的时间段
用户登录表A，字段：user_id,device_id,login_date,求用户最大连续登录天数
有这样一个表，时间戳 用户id 用户行为，怎么计算出每天的新用户数量
实时计算每天一小时内各个种类的热门Top10商品
未解决的题
给定视频分享的from_user_id and to_user_id，找出视频分享的源头用户，以及视频分享的最大深度。
三、总结
一些解题思路：
- 将问题拆解，一步一步实现
- 考虑是使用join(需要补充一些数据时)，还是where(需要排除一些数据时)，还是子查询()
一些注意事项：
- 注意是否使用left/right join（外连接不匹配的会去null）
- 注意是否使用distinct
- where中不能有函数；
- count(null）=0；类似count后面和括号之间不能有空格
- or自带去重，而union等价于or，但union all 不去重
- date可以直接大小比较，date>'2025-10-15'
- if的用法sum(if(result = "right",1,0))；ifnull(expr1,expr2)，不为null则取expr1，为null则取expr2
- 使用类似max的分组函数，没办法保证同一行的数据被统一取出，除了max的列，其他列回默认取第一行的数据
浙公网安备 33010602011771号