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

with d as (select product_id,min(customer_age) as customer_age
from(
    select c.product_id,customer_age
    from(
        select o.product_id,c.customer_id,customer_age,sum(quantity) as tq
        from customers c
        join orders o on c.customer_id=o.customer_id and year(order_date)=2023
        group by o.product_id,c.customer_id,customer_age) c
    where (c.product_id,c.tq) in (
        select product_id,max(tq) as mq
        from(
            select o.product_id,c.customer_id,customer_age,sum(quantity) as tq
            from customers c
            join orders o on c.customer_id=o.customer_id and year(order_date)=2023
            group by o.product_id,c.customer_id,customer_age) b
    group by b.product_id)) e
group by e.product_id

),
a as (select 
        o.product_id,
        unit_price,
        month(order_date),
        sum(quantity) as monthly_quantity
    from customers c,products p,orders o
    where c.customer_id=o.customer_id and p.product_id=o.product_id and year(order_date)=2023
    group by o.product_id,month(order_date),unit_price)

select 
    a.product_id,
    unit_price*sum(monthly_quantity) as total_sales,
    unit_price,
    sum(monthly_quantity) as total_quantity,
    round((unit_price*sum(monthly_quantity))/12,2) as avg_monthly_sales,
    max(monthly_quantity) as max_monthly_quantity,
    case
        when customer_age between 1 and 10 then '1-10'
        when customer_age between 11 and 20 then '11-20'
        when customer_age between 21 and 30 then '21-30'
        when customer_age between 31 and 40 then '31-40'
        when customer_age between 41 and 50 then '41-50'
        else '61+' end as customer_age_group
from a
join d on a.product_id=d.product_id
group by a.product_id,unit_price,customer_age
order by total_sales desc,a.product_id asc