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

with tb1 as
(
    select
        p.product_id,
        p.product_name,
        p.category_id,
        sum(s.sales_amount) as sales_amount,
        sum(s.sales_amount-s.cost_amount)/sum(s.sales_amount) as profit_rate,
        rank() over(partition by p.category_id order by sum(s.sales_amount) desc) as rk
    from product_category p
    join sales_and_profit s on p.product_id = s.product_id
    group by product_id,product_name
)

select
product_id,
product_name,
category_id,
sales_amount,
round(profit_rate,2) as profit_rate
from tb1
where rk<=3 and profit_rate>0.20
order by category_id asc,sales_amount desc, product_id asc;

数据分析真不是人干的哈哈哈