题解 | 查询出不同类别商品中，销售金额排名前三且利润率超过 20%的商品信息

WITH
t1 AS (     -- 清洗数据，关联两表，得出包含所需数据的底表 
SELECT
    sp.product_id,
    sp.product_name,
    sp.category_id,
    SUM(ll.sales_amount) AS total_sales_amount,
    SUM(ll.cost_amount) AS total_cost_amount
FROM product_category AS sp
LEFT JOIN sales_and_profit AS ll
    ON sp.product_id = ll.product_id
GROUP BY sp.product_id,sp.product_name,sp.category_id
),

t2 AS (     -- 计算利润率
SELECT
    product_id,
    product_name,
    category_id,
    total_sales_amount,
    COALESCE(ROUND((total_sales_amount - total_cost_amount) / NULLIF(total_sales_amount,0),2),0) AS profit_rate
FROM t1
),

t3 AS (     -- 计算每个商品类别的销售额排名
SELECT
    product_id,
    product_name,
    category_id,
    total_sales_amount,
    profit_rate,
    RANK() OVER(PARTITION BY category_id ORDER BY total_sales_amount DESC) AS rk
FROM t2
)

SELECT      -- 清洗已完成，按照题目要求输出数据
    product_id,
    product_name,
    category_id,
    total_sales_amount AS sales_amount,
    profit_rate
FROM t3
WHERE rk <= 3  AND profit_rate > 0.2
ORDER BY category_id ASC,total_sales_amount DESC,product_id ASC;