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

#目前只会无限套娃，调试了快2小时记录下。。。
select
    t5.product_id,
    t5.total_sales,
    t5.unit_price,
    t5.total_quantity,
    t5.avg_monthly_sales,
    t5.max_monthly_quantity,
    t5.customer_age_group
from
    (
        select
            t2.product_id,
            sum(t2.sales) as total_sales,
            t2.unit_price,
            sum(t2.quantity) as total_quantity,
            t2.avg_monthly_sales,
            t2.max_monthly_quantity,
            t2.customer_age_group,
            row_number() over (
                partition by
                    t2.product_id
                order by
                    t2.qt_gp desc,
                    t2.customer_age_group asc
            ) as rk
        from
            (
                select
                    o.product_id,
                    p.unit_price,
                    o.quantity,
                    o.quantity * p.unit_price as sales,
                    t1.avg_monthly_sales,
                    t1.max_monthly_quantity,
                    t4.customer_age_group,
                    t4.qt_gp
                from
                    orders o
                    join products p on o.product_id = p.product_id
                    join customers c on c.customer_id = o.customer_id
                    join (
                        select
                            t.product_id,
                            round(sum(t.sales) / 12, 2) as avg_monthly_sales,
                            max(t.quantity) as max_monthly_quantity
                        from
                            (
                                select
                                    o.product_id,
                                    o.quantity,
                                    p.unit_price,
                                    o.quantity * p.unit_price as sales,
                                    month (o.order_date) as sales_month
                                from
                                    orders o
                                    join products p on o.product_id = p.product_id
                            ) t
                        group by
                            t.product_id
                    ) t1 on t1.product_id = o.product_id
                    join (
                        select
                            t3.product_id,
                            t3.customer_age_group,
                            sum(t3.quantity) as qt_gp
                        from
                            (
                                select
                                    o.product_id,
                                    o.quantity,
                                    case
                                        when c.customer_age between 1 and 10  then "1-10"
                                        when c.customer_age between 11 and 20  then "11-20"
                                        when c.customer_age between 21 and 30  then "21-30"
                                        when c.customer_age between 31 and 40  then "31-40"
                                        when c.customer_age between 41 and 50  then "41-50"
                                        when c.customer_age between 51 and 60  then "51-60"
                                        when c.customer_age > 60 then "61+"
                                    end as customer_age_group
                                from
                                    orders o
                                    join customers c on c.customer_id = o.customer_id
                            ) t3
                        group by
                            t3.product_id,
                            t3.customer_age_group
                    ) t4 on t4.product_id = o.product_id
            ) t2
        group by
            t2.product_id,
            t2.avg_monthly_sales,
            t2.max_monthly_quantity,
            t2.customer_age_group
    ) t5
where
    rk = 1
order by
    t5.total_sales desc,
    t5.product_id;