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

with ta as(
    select customer_id,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,customer_age
    from customers),
tb as (
    select t1.product_id,round(sum(quantity*unit_price),2) as total_sales,
    round(max(unit_price),2) as unit_price,sum(quantity) as total_quantity,
    round(sum(quantity*unit_price)/12,2) as avg_monthly_sales
    from orders t1
    left join products t2
    on t1.product_id = t2.product_id
    group by t1.product_id),
tc as (
    select distinct product_id,max(month_quan) over (partition by product_id) as max_monthly_quantity
    from(
    select product_id,date_format(order_date,'%Y-%m') as month,
    sum(quantity) as month_quan
    from orders t1
    group by product_id,date_format(order_date,'%Y-%m')) tt
),
td as (
    select product_id,customer_id,customer_age_group
    from (
    select product_id,customer_id,customer_age_group,
    row_number() over (partition by product_id order by peo_buy desc,customer_age asc) as rnk
    from ( 
    select t1.product_id,t1.customer_id,customer_age,customer_age_group,sum(quantity) over(partition by t1.product_id,t1.customer_id) as peo_buy
    from orders t1 
    left join ta
    on t1.customer_id = ta.customer_id) tt) th
    where rnk = 1
)
select tb.product_id as product_id,total_sales,unit_price,total_quantity,avg_monthly_sales,max_monthly_quantity,customer_age_group
from tb
left join tc
on tb.product_id = tc.product_id
left join td
on tb.product_id = td.product_id
order by total_sales desc,tb.product_id asc

越复杂越需要拆解成小问题（临时表解决）