select
product_category,
age_group,
total_sales_amount,
round(total_sales_amount/sum(total_sales_amount) over (partition by product_category),2) as purchase_percentage
from
(select
p.category as product_category,
c.age_group,
sum(s.quantity*s.price) as total_sales_amount
from
sales s
join products p on s.product_id=p.product_id
join customer_info c on s.sale_id=c.sale_id
group by
p.category,c.age_group) sub
order by
product_category asc,
purchase_percentage desc
- round(total_sales_amount/sum(total_sales_amount) over (partition by product_category),2) as purchase_percentage可以把窗口函数的结果作为round的输入
- 这题最后要再按百分比降序排列才能通过