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

WITH temp AS(    
    SELECT a.product_id,b.product_name,b.category_id,a.sales_amount,
    round((a.sales_amount - a.cost_amount)/a.sales_amount,2) profit_rate,
    ROW_NUMBER() OVER (PARTITION BY b.category_id ORDER BY a.sales_amount DESC,a.product_id ASC) AS rk
    FROM sales_and_profit AS a
    INNER JOIN product_category AS b ON a.product_id = b.product_id
)

SELECT product_id,product_name,category_id,sales_amount,profit_rate
FROM temp 
WHERE profit_rate > 0.2 AND rk <= 3
ORDER BY category_id ASC,sales_amount DESC,product_id ASC

提醒一下，题目条件是销售金额排名前三且利润率超过 20%的，要先排名选出前三再去判断利润率的问题，我第一遍是先算利润率才排的，然后发现题目给的结果表里类别一排名前三竟然只有两个...