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

好抽象的题目...

with monthlysales as (
    select p.product_id, 
        date_format(o.order_date, '%y-%m') as month, 
        sum(o.quantity * p.unit_price) as monthly_sales,
        sum(o.quantity) as monthly_quantity
    from products p 
    join orders o on p.product_id = o.product_id
    where year(o.order_date) = 2023
    group by p.product_id, date_format(o.order_date, '%y-%m')
),
agegroup as (
    select product_id, age_group as customer_age_group
    from (
        select product_id, age_group,
            rank() over (partition by product_id order by sum(quantity) desc, age_group asc ) as rnk
        from (
            select o.product_id,
                case 
                    when c.customer_age <= 10 then '1-10'
                    when c.customer_age <= 20 then '11-20'
                    when c.customer_age <= 30 then '21-30'
                    when c.customer_age <= 40 then '31-40'
                    when c.customer_age <= 50 then '41-50'
                    when c.customer_age <= 60 then '51-60'
                    else '61+' 
                end as age_group, o.quantity
            from customers c 
            join orders o on c.customer_id = o.customer_id
            where year(o.order_date) = 2023
        ) t
        group by product_id, age_group
    ) ranked
    where rnk = 1
)
select 
    p.product_id,
    round(sum(m.monthly_sales), 2) as total_sales,
    round(p.unit_price, 2) as unit_price,
    sum(m.monthly_quantity) as total_quantity,
    round(sum(m.monthly_sales) / 12, 2) as avg_monthly_sales,
    max(m.monthly_quantity) as max_monthly_quantity,
    a.customer_age_group as customer_age_group
from products p
join monthlysales m on p.product_id = m.product_id
join agegroup a on p.product_id = a.product_id
group by p.product_id, p.unit_price, a.customer_age_group
order by total_sales desc, p.product_id asc;