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

#淦了俩小时
with t1 as(
select 
    order_id,
    customer_id,
    product_id,
    sum(quantity) quantity,
    concat(year(order_date),'-',month(order_date)) order_date
from orders 
where year(order_date) = 2023 
group by  order_id,customer_id,product_id,concat(year(order_date),'-',month(order_date))),

t2 as(
select 
    t1.product_id,
    round(sum(t1.quantity) * b.unit_price,2) total_sales,
    b.unit_price,
    sum(t1.quantity) total_quantity,
    sum(t1.quantity) * b.unit_price / 12  avg_monthly_sales,
    max(t1.quantity) max_monthly_quantity,
    case
        when a.customer_age between 1 and 10 then '1-10'
        when a.customer_age between 11 and 20 then '11-20'
        when a.customer_age between 21 and 30 then '21-30'
        when a.customer_age between 31 and 40 then '31-40'
        when a.customer_age between 41 and 50 then '41-50'
        when a.customer_age between 51 and 60 then '51-60'
        else '61+'
        end as customer_age_group
from t1 
join customers a on t1.customer_id = a.customer_id
join products b on t1.product_id = b.product_id
group by t1.product_id,b.unit_price,a.customer_age),

t3 as(
select 
    t2.product_id,
    sum(t2.total_sales) total_sales,
    min(t2.unit_price) unit_price,
    sum(t2.total_quantity) total_quantity,
    round(sum(t2.avg_monthly_sales),2) avg_monthly_sales,
    max(t2.max_monthly_quantity) max_monthly_quantity
from t2 group by product_id
order by total_sales desc,t2.product_id)

select 
    t3.*,
    e.customer_age_group
from t3 join (select 
    d.product_id,
    d.customer_age_group
from (
select 
    * ,
    row_number() over(partition by c.product_id order by c.quantity desc,c.customer_age_group) rk
from(
select 
    b.product_id,
    b.quantity,
    case
        when a.customer_age between 1 and 10 then '1-10'
        when a.customer_age between 11 and 20 then '11-20'
        when a.customer_age between 21 and 30 then '21-30'
        when a.customer_age between 31 and 40 then '31-40'
        when a.customer_age between 41 and 50 then '41-50'
        when a.customer_age between 51 and 60 then '51-60'
        else '61+'
        end as customer_age_group
from orders b join customers a on a.customer_id = b.customer_id ) c) d

where d.rk = 1) e on t3.product_id = e.product_id

order by t3.total_sales desc,t3.product_id;




# select 
#     d.product_id,
#     d.customer_age_group
# from (
# select 
#     * ,
#     row_number() over(partition by c.product_id order by c.quantity desc,c.customer_age_group) rk
# from(
# select 
#     b.product_id,
#     b.quantity,
#     case
#         when a.customer_age between 1 and 10 then '1-10'
#         when a.customer_age between 11 and 20 then '11-20'
#         when a.customer_age between 21 and 30 then '21-30'
#         when a.customer_age between 31 and 40 then '31-40'
#         when a.customer_age between 41 and 50 then '41-50'
#         when a.customer_age between 51 and 60 then '51-60'
#         else '61+'
#         end as customer_age_group
# from orders b join customers a on a.customer_id = b.customer_id ) c) d

# where d.rk = 1;