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

with customer_age as(
    select customer_id,
    customer_age,
    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'
    when customer_age between 51 and 60 then '51-60'
    when customer_age >60 then '61+' end 
    as customer_age_group
    from customers),
    
    2023_orders as(
    select
    order_id,
    o.customer_id,
    c.customer_age,
    c.customer_age_group,
    o.product_id,
    quantity,
    p.unit_price,
    quantity*unit_price as sales,
    order_date
    from orders o
    inner join products p on o.product_id=p.product_id
    inner join customer_age c on c.customer_id=o.customer_id
    where year(order_date)=2023),

    monthly_sales as(
    select 
    product_id,month(order_date),
    sum(quantity) as monthly_quantity,
    row_number() over(partition by product_id order by sum(quantity) desc) as rn1
    from 2023_orders
    group by product_id,month(order_date)
    ),

    age_group as(
    select
    product_id,
    customer_id,
    customer_age,
    customer_age_group,
    sum(quantity) as age_quantity,
    row_number() over(partition by product_id order by sum(quantity) desc,customer_age) as rn2
    from 2023_orders
    group by product_id,customer_id,customer_age,customer_age_group
    ),

    total_sales as(
    select
    product_id,
    round(sum(sales),2) as total_sales,
    round(unit_price,2) as unit_price,
    sum(quantity) as total_quantity,
    round(sum(sales)/12,2) as avg_monthly_sales
    from 2023_orders
    group by product_id
    )


select
total_sales.product_id,total_sales,unit_price,total_quantity,avg_monthly_sales,monthly_quantity as max_monthly_quantity,customer_age_group
from total_sales
inner join monthly_sales on total_sales.product_id=monthly_sales.product_id and rn1=1
inner join age_group on age_group.product_id=total_sales.product_id and rn2=1
order by total_sales desc,total_sales.product_id;