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

两种方式

难点在于单月最高销量和购买量最多的客户年龄段，可以分开求然后按照product_id join在一起；也可以一起算，会用到first_value窗口函数。

解法如下，欢迎牛客朋友们交流更多的解法

with
    sales as (
        select
            product_id,
            sum(total_sales) as total_sales,
            max(unit_price) as unit_price,
            sum(total_quantity) as total_quantity,
            (sum(total_sales) / 12) as avg_monthly_sales,
            max(total_quantity) as max_monthly_quantity
        from
            (
                select
                    o.product_id,
                    month(order_date),
                    sum(quantity * unit_price) as total_sales,
                    max(unit_price) as unit_price,
                    sum(quantity) as total_quantity
                from
                    (
                        select
                            *
                        from
                            orders
                        where
                            year(order_date) = 2023
                    ) o
                    left join products p on o.product_id = p.product_id
                group by
                    o.product_id,
                    month(order_date)
            ) t1
        group by
            product_id
            # left join  customers c on c.customer_id=o.customer_id
    ),
    users as (
        select distinct
            product_id,
            customer_age_group
        from
            (
                select
                    product_id,
                    customer_age_group,
                    rank() over (
                        partition by
                            product_id
                        order by
                            total_quantity desc,
                            customer_age asc
                    ) as rankings
                from
                    (
                        select
                            o.product_id,
                            c.customer_age,
                            case
                                when c.customer_age >= 61 then '61+'
                                when c.customer_age >= 51 then '51-60'
                                when c.customer_age >= 41 then '41-50'
                                when c.customer_age >= 31 then '31-40'
                                when c.customer_age >= 21 then '21-30'
                                when c.customer_age >= 11 then '11-20'
                                else '1-10'
                            end as customer_age_group,
                            sum(quantity) over (
                                partition by
                                    product_id,
                                    (
                                        case
                                            when c.customer_age >= 61 then '61+'
                                            when c.customer_age >= 51 then '51-60'
                                            when c.customer_age >= 41 then '41-50'
                                            when c.customer_age >= 31 then '31-40'
                                            when c.customer_age >= 21 then '21-30'
                                            when c.customer_age >= 11 then '11-20'
                                            else '1-10'
                                        end
                                    )
                            ) as total_quantity
                        from
                            (
                                select
                                    *
                                from
                                    orders
                                where
                                    year(order_date) = 2023
                            ) o
                            left join customers c on c.customer_id = o.customer_id
                    ) t1
            ) t2
        where
            rankings = 1
    )
select
    sales.product_id,
    total_sales,
    unit_price,
    total_quantity,
    round(avg_monthly_sales,2) as avg_monthly_sales,
    max_monthly_quantity,
    users.customer_age_group
from
    sales
    left join users on sales.product_id = users.product_id
    order by total_sales desc,product_id asc

select
    product_id,
    sum(total_sales) as total_sales,
    max(unit_price) as unit_price,
    sum(total_quantity) as total_quantity,
    round(sum(total_sales) / 12, 2) as avg_monthly_sales,
    max(month_cnt) as max_monthly_quantity,
    max(customer_age_group_new) as customer_age_group
from
    (
        select
            t3.*,
            first_value(customer_age_group) over (
                partition by
                    product_id
                order by
                    group_cnt desc,
                    CAST(
                        SUBSTRING_INDEX(customer_age_group, '-', 1) AS UNSIGNED
                    ) asc rows between unbounded preceding
                    and unbounded following
            ) as customer_age_group_new
        from
            (
                select
                    t2.*,
                    sum(total_quantity) over (
                        partition by
                            product_id,
                            customer_age_group
                    ) as group_cnt,
                    sum(total_quantity) over (
                        partition by
                            product_id,
                            month
                    ) as month_cnt
                from
                    (
                        select
                            product_id,
                            month(order_date) as month,
                            customer_age_group,
                            sum(quantity * unit_price) as total_sales,
                            max(unit_price) as unit_price,
                            sum(quantity) as total_quantity
                        from
                            (
                                select
                                    o.*,
                                    case
                                        when c.customer_age >= 61 then '61+'
                                        when c.customer_age >= 51 then '51-60'
                                        when c.customer_age >= 41 then '41-50'
                                        when c.customer_age >= 31 then '31-40'
                                        when c.customer_age >= 21 then '21-30'
                                        when c.customer_age >= 11 then '11-20'
                                        else '1-10'
                                    end as customer_age_group,
                                    p.unit_price
                                from
                                    (
                                        select *
                                        from
                                            orders
                                        where
                                            year(order_date) = 2023
                                    ) o
                                    left join products p on o.product_id = p.product_id
                                    left join customers c on c.customer_id = o.customer_id
                            ) t1
                        group by
                            product_id,
                            month(order_date),
                            customer_age_group
                    ) t2
            ) t3
    ) t4
group by
    product_id
    order by total_sales desc,product_id asc