题解 | 统计每个产品的销售情况

select *
from
(select product_id,
if(customer_age between 1 and 10,'1-10',if(customer_age between 11 and 20,'11-20',if(customer_age between 21 and 30,'21-30',if(customer_age between 31 and 40,'31-40',if(customer_age between 41 and 50,'41-50',if(customer_age between 51 and 60,'51-60','61+')))))) as customer_age_group
from
(select *
from
(select
*,
row_number()over(partition by product_id order by sq desc) as r
from
(select product_id,
customer_id,
sum(quantity) as sq
from orders
left join customers
using(customer_id)
group by product_id,
customer_id
order by product_id) c) d
where r = 1) e
left join customers
using(customer_id)) f
right join
(select
product_id,
sum(total) as total_sales,
unit_price,
sum(quantity) as total_quantity,
round(sum(total)/12,2) as avg_monthly_sales,
max(quantity) as max_monthly_quantity
from
    (select
    *,
    unit_price * quantity as total,
    month(order_date) as m
    from orders
    left join products
    using(product_id)
    left join customers
    using(customer_id)) a
group by product_id) b
using(product_id)
order by total_sales desc,product_id