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

WITH t as(    
    SELECT a.product_id,b.product_name,
    sum(a.sales_amount) as total_sales_amount,
    sum(a.sales_quantity) as total_sales_quantity,
    DENSE_RANK() OVER (ORDER BY sum(a.sales_quantity) DESC) as rk
    FROM sales_records AS a
    JOIN products AS b ON a.product_id = b.product_id
    WHERE YEAR(a.sales_date) = 2024
    GROUP BY a.product_id
)

SELECT product_id,product_name,total_sales_amount,total_sales_quantity
FROM t 
WHERE rk = 1
ORDER BY product_id ASC

JOIN表连接+GROUP BY+ROW_NUMBER窗口函数排序(相同值排名相同),然后选出rk=1的+ORDER BY