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

with s1 as (
    select p.product_id,
    sum(unit_price*quantity) total_sales,
    unit_price,
    sum(quantity) total_quantity,
    round(sum(unit_price*quantity)/12,2) avg_monthly_sales,
    max(quantity) max_monthly_quantity
    from orders o join customers c on o.customer_id = c.customer_id 
    join products p on o.product_id = p.product_id
    group by product_id
    order by total_sales desc
),
s2 as (
    select p.product_id,max(quantity) max_quantity
    from orders o join customers c on o.customer_id = c.customer_id 
    join products p on o.product_id = p.product_id
    group by p.product_id
),
s3 as (
    select 
    p.product_id,
    quantity,
    customer_age,
    if(customer_age between 1 and 10, '1-10',
    if(customer_age between 11 and 20, '11-20',
    if(customer_age between 21 and 30, '21-30',
    if(customer_age between 31 and 40, '31-40',
    if(customer_age between 41 and 50, '41-50',
    if(customer_age between 51 and 60, '51-60', '60+')))))) as customer_age_group
    from orders o join customers c on o.customer_id = c.customer_id 
    join products p on o.product_id = p.product_id
    order by p.product_id,customer_age_group
),
s4 as (
    select t.product_id,customer_age_group 
    from (
        select s2.product_id,s3.customer_age_group,s3.customer_age,
        row_number() over(partition by s2.product_id order by s3.customer_age asc) as rk
        from s2 join s3 on s2.max_quantity = s3.quantity and s2.product_id = s3.product_id 
    ) t
    where rk = 1
)
select s1.product_id,
total_sales,
unit_price,
total_quantity,
avg_monthly_sales,
max_monthly_quantity,
customer_age_group
from s1 join s4 on s1.product_id = s4.product_id 
order by total_sales desc,s1.product_id

一小时 已累死