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

-- 逻辑拆解：不同类别下，销售金额排名前三且利润率超过20%的商品,难点是group by和窗口函数的关系（窗口函数必须比group by具更粗的颗粒度，而且所用的字段必须是group by里面包含的）

SELECT
    a.product_id,
    a.product_name,
    a.category_id,
    a.sales_amount,
    a.profit_rate
FROM(
    SELECT 
        t1.product_id,
        t1.product_name,
        t1.category_id,
        SUM(sales_amount) sales_amount,
        ROUND((SUM(sales_amount)-SUM(cost_amount))/SUM(sales_amount),2) profit_rate,
        ROW_NUMBER() OVER(partition by t1.category_id order by SUM(sales_amount) DESC) rk
    FROM product_category t1
    LEFT JOIN sales_and_profit t2
    ON t1.product_id = t2.product_id
    GROUP BY t1.category_id,t1.product_id
) a
WHERE a.rk <= 3 AND a.profit_rate > 0.2
ORDER BY a.category_id ASC,a.sales_amount DESC, a.product_id ASC