播面

面试题：2025年省份及下属城市销售额双重 Top 3 分析

题目描述：
有一张销售明细表 sales_records，记录了每笔订单的省份、城市、销售额和订单日期。请编写 SQL 查询，找出 2025 年总销售额排名前 3 的省份；并针对这 3 个省份，分别找出其下属销售额排名前 3 的城市。

1. 示例数据

输入表：sales_records

2. 期望输出结果

数据说明：

1. 2024年的数据（如订单20）被排除。
2. 2025年省份总销售额：浙江 (31000) > 江苏 (27000) > 广东 (25000) > 四川 (19000)。因此四川被剔除。
3. 各省内部筛选出销售额前3的城市。

3. SQL 解决方案 (基于 MySQL 8.0+ / Standard SQL)

WITH city_sales AS (
    -- Step 1: 计算2025年每个城市和省份的基础总销售额
    SELECT 
        province,
        city,
        SUM(sales_amount) AS total_city_sales
    FROM sales_records
    WHERE order_date >= '2025-01-01' AND order_date <= '2025-12-31'
    GROUP BY province, city
),
top_provinces AS (
    -- Step 2: 汇总省份销售额，并找出排名前 3 的省份
    SELECT 
        province,
        SUM(total_city_sales) AS total_province_sales
    FROM city_sales
    GROUP BY province
    ORDER BY total_province_sales DESC
    LIMIT 3
),
ranked_cities AS (
    -- Step 3: 在各省份内部，对城市的销售额进行排名
    SELECT 
        province,
        city,
        total_city_sales,
        ROW_NUMBER() OVER (
            PARTITION BY province 
            ORDER BY total_city_sales DESC
        ) AS city_rank
    FROM city_sales
)
-- Step 4: 关联省份 Top 3 和城市排名，过滤出目标数据
SELECT 
    tp.province,
    tp.total_province_sales,
    rc.city,
    rc.total_city_sales,
    rc.city_rank
FROM top_provinces tp
INNER JOIN ranked_cities rc ON tp.province = rc.province
WHERE rc.city_rank <= 3
ORDER BY tp.total_province_sales DESC, rc.city_rank ASC;

4. 面试官视角：SQL 深度剖析与评价标准

此题是标准的“组内 Top N”问题的升级版，融合了多层聚合与过滤依赖。面试官可以通过此题考察候选人以下维度：

核心考点解析

1. 公用表表达式（CTE）的设计与可读性：
   不推荐使用多层嵌套子查询，使用 WITH 语法将复杂逻辑拆解为 city_sales、top_provinces、ranked_cities，能极大展现候选人编写工程级 SQL 的规范性与结构化思维。

2. 日期过滤的性能考量（SARGability）：

   • 反例：WHERE YEAR(order_date) = 2025
   • 正例：WHERE order_date >= '2025-01-01' AND order_date <= '2025-12-31'
   • 面试加分项：主动向面试官解释，避免在索引列上使用函数（如 YEAR()），因为这会导致索引失效从而全表扫描。使用范围查询能有效利用 order_date 上的索引。

3. 窗口函数的选择（ROW_NUMBER vs RANK vs DENSE_RANK）：
   在 ranked_cities 中，我们使用了 ROW_NUMBER()。

   • 追问准备：面试官可能会问：“如果遇到并列第三名的城市，该如何处理？”
     • 若要并列保留，应使用 RANK() 或 DENSE_RANK()。
     • 若要严格返回3条，则用 ROW_NUMBER()。展示对这三个窗口函数细节差异的掌握是高级工程师的标配。

4. 双重 Top N 逻辑的闭环：
   不能直接对所有城市进行全局 ROW_NUMBER() 排名。必须先计算省份总额挑出 Top 3 省份，再在省份内部对城市进行 Rank，最后通过 INNER JOIN 剔除不属于 Top 3 省份的城市。这考查了候选人处理“局部与整体”数据流向的清晰度。