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

# 查询不同类别中销售金额排名前三、利润率大于20的商品
with
t1 as(
    select
        product_id,
        product_name,
        category_id,
        rank()over(partition by category_id order by sales_amount desc) as srank,
        sales_amount,
        round((sales_amount-cost_amount)/sales_amount,2) as profit_rate 
    from
        product_category left join sales_and_profit using(product_id)
)

select
        product_id,
        product_name,
        category_id,
        sales_amount,
        profit_rate
from
    t1
where
    srank<=3
    and
    profit_rate>0.20
order by
    category_id,
    sales_amount desc,
    product_id