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

with temp1 as(
-- temp1计算total_sales、unit_price、total_quantity、avg_monthly_sales
select  a.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
from orders a 
join customers b on a.customer_id = b.customer_id
join products c on a.product_id = c.product_id
where year(order_date) = '2023'
group by 1,3
# order by total_sales desc, product_id 
)
, temp2 as(
-- temp2计算 max_monthly_quantity
select  product_id,
        max(month_quantity) as max_monthly_quantity
from (
select  a.product_id,
        month(order_date) as month,
        sum(quantity) as month_quantity
from orders a 
join customers b on a.customer_id = b.customer_id
join products c on a.product_id = c.product_id
where year(order_date) = '2023'
group by 1,2
# order by product_id, month_quantity desc 
)t
group by 1
)
,
temp3 as(
-- temp3计算 customer_age_group
select  a.product_id,
        a.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'
            else '61+'
        end as customer_age_group,
        sum(quantity) as customer_product_quantity,
        row_number() over(partition by a.product_id order by  sum(quantity) desc,customer_age asc) as quantity_rk
from orders a 
join customers b on a.customer_id = b.customer_id
where year(order_date) = '2023'
group by 1,2,3
)

select  a.product_id,
        a.total_sales,
        a.unit_price,
        a.total_quantity,
        a.avg_monthly_sales,
        b.max_monthly_quantity,
        c.customer_age_group
from temp1 a
join temp2 b on a.product_id = b.product_id
join temp3 c on b.product_id = c.product_id
where quantity_rk = 1
order by total_sales desc, product_id, customer_age_group