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

WITH t2 AS (
SELECT t1.product_id,
       product_name,
       category_id,
       sales_amount,
       ROUND((sales_amount-cost_amount)/sales_amount,2) AS profit_rate,
       RANK() OVER(PARTITION BY category_id ORDER BY sales_amount DESC) AS rnk
FROM sales_and_profit t1
LEFT JOIN product_category p ON p.product_id=t1.product_id
)
SELECT product_id,
       product_name,
       category_id,
       sales_amount,
       profit_rate
FROM t2 
WHERE rnk<=3 AND  profit_rate>=0.2
ORDER BY  category_id ASC,sales_amount DESC,product_id ASC