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

with t1 as
(
    select
        p.product_id,
        p.product_name,
        p.category_id,
        s.sales_amount,
        round((s.sales_amount - s.cost_amount) / s.sales_amount, 2) profit_rate,
        row_number() over (partition by p.category_id order by s.sales_amount desc) rn
    from
        product_category p 
    join
        sales_and_profit s 
    on  
        p.product_id = s.product_id        
)
select
    product_id,
    product_name,
    category_id,
    sales_amount,
    profit_rate
from 
    t1
where
    rn < 4
    and 
    profit_rate > 0.2
order by
    category_id,
    sales_amount desc,
    product_id