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

with
    t0 as (
        select
            p.product_id,
            unit_price * sum(quantity) as total_sales,
            unit_price,
            sum(quantity) as total_quantity,
            unit_price * sum(quantity) / 12 as avg_monthly_sales
        from
            products p
            left join orders o on p.product_id = o.product_id
        group by
            1
        order by
            2 desc
    ),
    t1 as (
        SELECT
            customer_id,
            product_id,
            max_monthly_quantity,
            customer_age
        FROM
            (
                SELECT DISTINCT
                    o.customer_id,
                    o.product_id,
                    quantity as max_monthly_quantity,
                    customer_age,
                    row_number() over (
                        PARTITION BY
                            o.product_id
                        ORDER BY
                            quantity DESC,
                            customer_age
                    ) AS rk
                FROM
                    orders o
                    INNER JOIN customers c ON o.customer_id = c.customer_id
            ) tmp
        WHERE
            rk = 1
    )
select
    t0.product_id,
    total_sales,
    unit_price,
    total_quantity,
    round(avg_monthly_sales, 2) as avg_monthly_sales,
    max_monthly_quantity,
    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
    t0
    left join t1 on t1.product_id = t0.product_id
order by
    2 desc,
    1