题解 | 分析每个商品在不同时间段的销售情况

/*
①商品ID：product_id
②商品名称：product_name
③2024年第二季度的销售总额：q2_2024_sales_total
④在所属类别的销售排名：category_rank
⑤所属供应商：supplier_name
*/

WITH
t1 AS (     -- 链接三表，清洗数据，并计算每个商品的销售额
SELECT
    sp.product_id,
    sp.product_name,
    gy.supplier_name,
    COALESCE(SUM(dd.total_amount),0) AS xse,
    sp.category
FROM product_info AS sp
LEFT JOIN order_info AS dd
    ON dd.product_id = sp.product_id
    AND dd.order_date >= '2024-04-01'
    AND dd.order_date < '2024-07-01'
LEFT JOIN supplier_info AS gy
    ON sp.product_id = gy.product_id
GROUP BY sp.product_id,sp.product_name,gy.supplier_name,sp.category
)


SELECT      -- 使用窗口函数计算出商品在类别中的销售额排名后，按照题干要求排序
    product_id,
    product_name,
    xse AS q2_2024_sales_total,
    RANK() OVER(PARTITION BY category ORDER BY xse DESC) AS category_rank,
    supplier_name
FROM t1
ORDER BY product_id;