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

with t as (
    select a.product_id, product_name, category_id, 
    sum(sales_amount) as sales_amount, 
    round((sum(sales_amount)-sum(cost_amount))/sum(sales_amount), 2) as profit_rate
    from product_category a
    join sales_and_profit b on a.product_id=b.product_id
    group by a.product_id, product_name, category_id
)
select product_id, product_name, category_id, sales_amount, profit_rate
from (
    select product_id, product_name, category_id, sales_amount, profit_rate,
    row_number() over (partition by category_id order by sales_amount desc) as rn
    from t
) temp
where rn<=3 and profit_rate>0.2
order by category_id