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

with
    base_data as (
        select
            products.product_id,
            month(order_date) as month,
            unit_price,
            quantity,
            quantity * unit_price as total_amount,
            customer_age,
            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
            orders
            join customers using (customer_id)
            join products using (product_id)
        where
            year(order_date) = 2023
    ),
    max_monthly_quantity_data as (
        select distinct
            product_id,
            max_monthly_quantity
        from
            (
                select
                    product_id,
                    max(sum(quantity)) over (
                        partition by
                            product_id
                    ) as max_monthly_quantity
                from
                    base_data
                group by
                    product_id,
                    month
                order by
                    product_id asc,
                    month asc
            ) t1
    ),
    grouped_data AS (
        select
            product_id,
            customer_age_group
        from
            (
                SELECT
                    product_id,
                    customer_age_group,
                    total_quantity,
                    ROW_NUMBER() OVER (
                        PARTITION BY
                            product_id
                        ORDER BY
                            total_quantity DESC,
                            customer_age_group asc
                    ) AS sales_rank
                FROM
                    (
                        SELECT
                            product_id,
                            customer_age_group,
                            SUM(quantity) AS total_quantity
                        FROM
                            base_data
                        GROUP BY
                            product_id,
                            customer_age_group
                    ) t2
                ORDER BY
                    product_id,
                    sales_rank
            ) t3
        where
            sales_rank = 1
    ),
    basic_stat as (
        select
            product_id,
            round(sum(total_amount), 2) as total_sales,
            round(unit_price, 2) as unit_price,
            sum(quantity) as total_quantity,
            round(sum(total_amount) / 12, 2) as avg_monthly_sales
        from
            base_data
        group by
            product_id,
            unit_price
    )
select
    *
from
    basic_stat
    join max_monthly_quantity_data using (product_id)
    join grouped_data using (product_id)
order by
    total_sales desc,
    product_id asc,
    customer_age_group asc