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

with
    源表 as (
        select
            *
        from
            orders as c
            join products as b using (product_id)
            join customers as a using (customer_id)
    ),
    单产品总销售额 as (
        select
            product_id,
            unit_price,
            sum(unit_price * quantity) as total_sales,
            sum(quantity) as total_quantity,
            round(sum(unit_price * quantity) / 12 * 1.0, 2) as avg_monthly_sales
        from
            源表
        group by
            product_id
    ),
    单品单月销售额及最高销量 as (
        select
            product_id,
            b.max_monthly_quantity
        from
            (
                select
                    product_id,
                    month (order_date) as month,
                    sum(quantity) as max_monthly_quantity,
                    row_number() over (
                        partition by
                            product_id
                        order by
                            sum(quantity) desc
                    ) as 月销量排名
                from
                    源表
                group by
                    product_id,
                    month
            ) as b
        where
            b.月销量排名 = 1
    ),
    客户年龄段 as (
        select
            product_id,
            customer_age_group
        from
            (
                select
                    product_id,
                    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,
                    sum(quantity),
                    row_number() over (
                        partition by
                            product_id
                        order by
                            sum(quantity) desc,
                            customer_age
                    ) as 单品单客户销量排名
                from
                    源表
                group by
                    product_id,
                    customer_id,
                    customer_age
            ) as a
        where
            a.单品单客户销量排名 = 1
    )
select
    单产品总销售额.product_id,
    单产品总销售额.total_sales,
    单产品总销售额.unit_price,
    单产品总销售额.total_quantity,
    单产品总销售额.avg_monthly_sales,
    单品单月销售额及最高销量.max_monthly_quantity,
    客户年龄段.customer_age_group
from
    单产品总销售额
    join 单品单月销售额及最高销量 using (product_id)
    join 客户年龄段 using (product_id)
order by
    单产品总销售额.total_sales desc,
    单产品总销售额.product_id;