题解 | 每个商品的销售总额

select c.product_name,total_sales,category_rank
from(
select b.product_name as 'product_name',total_sales,category_row,row_number() over(partition by category_row order by total_sales desc) as category_rank
from(
select product_name,total_sales,substr(category,10,1) as category_row
from(
select orders.product_id,name as product_name,category,sum(quantity) as total_sales
from orders
left join products
on products.product_id=orders.product_id
group by orders.product_id
having product_name is not null) a   # 有product_name中productE没有被记录的情况
order by a.category asc,total_sales desc,product_id asc) b) c