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

WITH sales_total AS (
    SELECT
        p.product_id,
        p.product_name,
        SUM(s.sales_amount) AS total_sales_amount,
        SUM(s.sales_quantity) AS total_sales_quantity,
        RANK() OVER (ORDER BY SUM(s.sales_quantity) DESC) AS rn
    FROM
        products AS p
        INNER JOIN
        sales_records AS s ON p.product_id=s.product_id
    WHERE
        s.sales_date BETWEEN '2024-01-01' AND '2024-12-31'
    GROUP BY
        p.product_id,
        p.product_name)
SELECT
    product_id,
    product_name,
    total_sales_amount,
    total_sales_quantity
FROM
    sales_total
WHERE
    rn=1
ORDER BY
    product_id ASC;