SQL业务查询题 | 解题思路&代码分析

SQL是作为一名数据分析师必备的技能，在笔试/面试中我们经常会遇到各种SQL查询题，特别是对于工作相关的业务指标的计算。这篇文章中我将各种SQL业务查询题进行汇总。

题目1：求销量排名前三的二级类目

已知一个订单表，有字段：订单号、日期（date）、下单时间、下单金额（amount）、商品一级类目（type_1）、商品二级类目（type_2）。求最近7天内每一个一级类目下成交总额排名前3的二级类目。

解题思路：

1. 首先要筛选出7天内的订单数据：datediff(now(),date) between 0 and 7
2. 对每个一级类目下各个二级类目成交总额进行排名，需要同时对一级类目和二级类目进行分组，然后对每个一级类目下二级类目的金额进行求和排序。这里可以使用窗口函数：rank() 或者 dense_rank() 新建一个排名字段（ranking），按一级类目分块，按求和后的成交总额进行从大到小排序
3. 最后保留排名$<=3$ 的数据，即为所求

具体代码：

SELECT type_1, type_2, total_amt FROM (
	SELECT type_1, type_2,
		SUM(amount) total_amt,
		dense_rank() over (partition by type_1 order by SUM(amount) DESC) dense_ranking
	FROM order_log
	WHERE DATEDIFF(now(),date) BETWEEN 0 AND 7
	GROUP BY type_1, type_2
	ORDER BY type_1, dense_ranking
	) p
WHERE dense_ranking <= 3

相似问题：求每个班代课次数最多的老师信息

从表lesson中，提取n_minutes（分钟数）>30的记录中，每个班级中代课次数最多的老师id及姓名

字段	解释
c_id	班级id
t_id	教师id
t_name	教师名字
n_minutes	上课分钟数

具体代码：

SELECT c_id, t_id, t_name
FROM (
	SELECT 
		c_id, 
		t_id, 
		t_name,
		rank() over(partition by c_id order by count(1) desc) ranking
	FROM class_log
	WHERE n_minutes > 30
	GROUP BY c_id, t_id, t_name
	) p
WHERE ranking = 1

---

题目2：每日留存率计算

表名：user_log

字段	解释
log_day	登录日期
device_id	用户设备id
app_id	用户app_id

其中 device_id 和 app_id 确定唯一的用户
要求计算每日的次日、3日、7日留存率

解题思路：

1. 建立子查询：使用 left join 配合 datediff 函数，并将数据按日期进行 group by，计算每日的：
   活跃用户数 ——当天活跃的用户数
   次日留存用户数 ——当天活跃用户数中次日仍活跃的用户数
   3日留存用户数 ——当天活跃用户数中3日后仍活跃的用户数
   7日留存用户数 ——当天活跃用户数中7日后仍活跃的用户数
2. 建立主查询：根据子查询结果，计算每日的：
   次日留存率 ——次日留存用户数 / 活跃用户数
   3日留存率 与 7日留存率 同理

具体代码：