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

--先把销售金额和利润率求出来，然后排序筛选(一定排序是category_id)
with tiaojian as (
select 
py.product_id,
py.product_name,
py.category_id,
sum(sales_amount) as sales_amount,
sum(sales_amount-cost_amount)/sum(sales_amount) as profit_rate
from product_category py 
inner join sales_and_profit st on 
py.product_id=st.product_id
group by py.product_id
)


select 
product_id,
product_name,
category_id,
sales_amount,
profit_rate
from(
select 
product_id,
product_name,
category_id,
sales_amount,
round(profit_rate,2) as profit_rate,
row_number()over(partition by category_id  order by 
sales_amount desc) as pt
from tiaojian
) as  t 
where
t.pt<=3
and 
profit_rate>0.2
order by category_id,sales_amount desc,product_id