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

WITH product_sales AS (
    SELECT
        t1.product_id,
        t1.product_name,
        t1.category_id,
        SUM(t2.sales_amount) AS sales_amount,
        SUM(t2.cost_amount)  AS cost_amount,
        RANK() OVER (
            PARTITION BY t1.category_id
            ORDER BY SUM(t2.sales_amount) DESC
        ) AS sales_rank
    FROM product_category t1
    LEFT JOIN sales_and_profit t2
        USING (product_id)
    GROUP BY
        t1.product_id,
        t1.product_name,
        t1.category_id
)
SELECT
    product_id,
    product_name,
    category_id,
    sales_amount,
    round(((sales_amount - cost_amount) / sales_amount),2) AS profit_rate
FROM product_sales
WHERE
    sales_rank <= 3
    AND (sales_amount - cost_amount) / sales_amount > 0.2
ORDER BY
    category_id ASC,
    sales_amount DESC,
    product_id ASC;