题解 | SQLW9 每个商品的销售总额

WITH t1 AS (
    SELECT a.quantity,
           p.name,
           p.category
    FROM orders a
    LEFT JOIN products p
    on a.product_id = p.product_id
),
t2 AS (
    SELECT name,
           SUM(quantity) AS total_sales
    FROM t1
    GROUP BY name
),
t3 AS (
    SELECT t2.name AS product_name,
           t2.total_sales,
           RANK()
               over (PARTITION BY p.category
                   ORDER BY t2.total_sales DESC,product_id ASC)
               AS category_rank
    FROM t2
    LEFT JOIN products p
    ON t2.name = p.name
)SELECT * FROM t3
WHERE product_name IS NOT NULL ;

# 这道题的细节是出现了空值，一般还是 inner join 好一点！