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

select
    t1.product_id,
    total_sales,
    unit_price,
    total_quantity,
    avg_monthly_sales,
    max_monthly_quantity,
    customer_age_group
from
    (
        select
            a.product_id,
            sum(quantity * unit_price) as total_sales,
            max(unit_price) as unit_price,
            sum(quantity) as total_quantity,
            round(sum(quantity * unit_price) / 12, 2) as avg_monthly_sales
        from
            orders a
            left join customers b using (customer_id)
            left join products c using (product_id)
        where
            year (order_date) = 2023
        group by
            a.product_id
    ) t1
    join (
        select
            product_id,
            max(y_quantity) as max_monthly_quantity
        from
            (
                select
                    a.product_id,
                    month (order_date),
                    sum(quantity) as y_quantity
                from
                    orders a
                    left join customers b using (customer_id)
                    left join products c using (product_id)
                where
                    year (order_date) = 2023
                group by
                    a.product_id,
                    month (order_date)
            ) d
        group by
            product_id
    ) t2 using (product_id)
    join (
        select
            product_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
        from
            (
                select
                    product_id,
                    customer_age,
                    dense_rank() over (
                        partition by
                            product_id
                        order by
                            quantity desc,
                            customer_age
                    ) as rk
                from
                    (
                        select
                            a.product_id,
                            a.customer_id,
                            customer_age,
                            sum(quantity) as quantity
                        from
                            orders a
                            left join customers b using (customer_id)
                            left join products c using (product_id)
                        where
                            year (order_date) = 2023
                        group by
                            a.product_id,
                            a.customer_id,
                            customer_age
                    ) d
            ) e
        where
            rk = 1
    ) t3 using (product_id)
order by
    total_sales desc,
    t1.product_id

写过最长的代码。。。