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

with t as(
    select 
        p.product_id,
        p.product_name,
        p.category_id,
        coalesce(s.sales_amount,0) as sales_amount,
        round(coalesce((s.sales_amount-s.cost_amount)/s.sales_amount,0),2) as profit_rate,
        row_number() over(partition by p.category_id order by s.sales_amount desc) as sale_rank
    from product_category p
    left join sales_and_profit s
    on p.product_id=s.product_id)

select
    t.product_id,
    t.product_name,
    t.category_id,
    t.sales_amount,
    t.profit_rate
from t
where
    t.sale_rank<=3 and t.profit_rate>=0.2
order by 
    t.category_id,t.sales_amount desc,t.product_id