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

# 产品ID（product_id）：产品的ID。
# 总销售额（total_sales）：该产品的2023年总销售额。
# 单价（unit_price）：产品的单价。
# 总销量（total_quantity）：该产品的2023年总销售数量。
# 月平均销售额（avg_monthly_sales）：2023年该产品的月均销售额。
with a as(
    select 
        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 
        join products using(product_id)
    where year(order_date)='2023'
    group by product_id,unit_price
),
# 1-10，11-20，21-30，31-40，41-50，51-60
b as(
    select 
        product_id,
        floor((customer_age+9)/10)*10 as customer_age_group,
        sum(quantity) as number,
        row_number() over(partition by product_id order by sum(quantity) desc,floor((customer_age+9)/10)*10 ) as rk
    from orders join customers using(customer_id)
    where year(order_date)='2023'
    group by product_id,floor((customer_age+9)/10)*10
),
# 61+
b2 as(
    select 
        product_id,
        '61+' as customer_age_group,
        sum(quantity) as number
    from orders join customers using(customer_id)
    where year(order_date)='2023' and customer_age>=61
    group by product_id
),
# max(1-10，11-20，21-30，31-40，41-50，51-60)  vs   61+
b3 as(
    select 
        product_id,
        case when temp.number<b2.number then b2.customer_age_group else concat(concat(temp.customer_age_group-9,'-'),temp.customer_age_group) end as customer_age_group
    from (
        select *
        from b
        where rk=1
    )temp left join b2 using(product_id)
),
-- 再拿到1即可  单月最高销量（max_monthly_quantity）：2023年该产品的最大月销售数量。
c as(
    select 
        product_id,
        sum(quantity) as monthly_quantity,
        row_number() over(partition by product_id order by sum(quantity) desc) as rk
    from orders
    where year(order_date)='2023'
    group by product_id,month(order_date)
)
select 
    product_id,
    total_sales,
    unit_price,
    total_quantity,
    avg_monthly_sales,
    monthly_quantity as max_monthly_quantity,
    customer_age_group
from a 
    join b3 using(product_id)
    join (
        select *
        from c 
        where rk=1
    )tempc using(product_id)
order by total_sales desc,product_id;