题解 | 哪些产品在特定时间段内表现最为出色

-- 逻辑拆解：找到2024年销量最高的产品(选好驱动的左表为products表，因为销售记录表很明显记录不唯一,会产生笛卡尔积)
-- 首先先把排名求出来，再取第一名即可，这里的可以挖的陷阱是GROUP BY 分组后，partition by 必须是group by的列或者是更小颗粒度
With sales_ranked AS(
SELECT
    t1.product_id,
    SUM(t2.sales_quantity) total_sales_quantity,
    SUM(t2.sales_amount) total_sales_amount,
    DENSE_RANK() OVER(ORDER BY SUM(t2.sales_quantity) DESC) ranking
FROM products t1
LEFT JOIN sales_records t2
ON t1.product_id = t2.product_id
AND t2.sales_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY t1.product_id
)
SELECT
    t1.product_id,
    t2.product_name,
    t1.total_sales_amount,
    t1.total_sales_quantity
FROM sales_ranked t1
LEFT JOIN products t2
ON t1.product_id = t2.product_id
WHERE t1.ranking = 1
ORDER BY t1.product_id