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

select 
product_id,
product_name,
category_id,
sales_amount,
round((sales_amount-cost_amount)/sales_amount,2) profit_rate
from (
    select p.product_id,
    product_name,
    category_id,
    sales_amount,
    cost_amount,
    rank() over (partition by category_id order by sales_amount desc) rk
    from product_category p join sales_and_profit s on p.product_id=s.product_id
) t
where round((sales_amount-cost_amount)/sales_amount,2)>0.2 and rk<=3
order by category_id,sales_amount desc,product_id

窗口函数在一定程度上可以避免在主查询中使用group by