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

with sub_customers as
(
    SELECT *,
    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 >= 61 THEN '61+'
        ELSE 'Unknown'  -- 处理NULL或异常值
    END as age_bin 
    FROM customers
),
sub_orders as
(
    select o.*, age_bin, SUM(quantity) over(partition by product_id,month(order_date)) as month_quantity, SUM(quantity) over(partition by product_id,age_bin) as age_group_quantity
    from orders o
    left join sub_customers c on o.customer_id=c.customer_id
    where year(order_date)=2023
),
max_customer_age as
(   
    select product_id, row_number() over(partition by product_id order by age_group_quantity desc) as rk, age_bin
    from sub_orders
)
select p.product_id, sum(quantity)*unit_price as total_sales, unit_price,
       sum(quantity) as total_quantity,
       round(sum(quantity)*unit_price / 12, 2) as avg_monthly_sales,
       max(month_quantity) as max_monthly_quantity,
       max(a.age_bin) as customer_age_group
from products p
left join sub_orders o on p.product_id=o.product_id
left join max_customer_age a on p.product_id=a.product_id and a.rk=1
group by p.product_id, unit_price
order by total_sales desc, p.product_id asc