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

with
    sales_tb as (
        select
            orders.product_id,
            products.unit_price,
            sum(orders.quantity) as quantities
        from
            orders
            join products on orders.product_id = products.product_id
        where
            year(order_date) = '2023'
        group by
            orders.product_id,
            products.unit_price
    ),
    ranks_tb as (
        select
            orders.product_id,
            orders.customer_id,
            orders.quantity,
            customers.customer_age,
            dense_rank() over (
                partition by
                    orders.product_id
                order by
                    orders.quantity desc,
                    customers.customer_age
            ) as ranks
        from
            orders
            join customers on orders.customer_id = customers.customer_id
        where
            year(order_date) = '2023'
    ),
    selected_tb as (
        select
            product_id,
            quantity as max_monthly_quantity,
            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
            ranks_tb
        where
            ranks = 1
    )
select
    sales_tb.product_id,
    round(sales_tb.unit_price * sales_tb.quantities,2) as total_sales,
    sales_tb.unit_price,
    sales_tb.quantities as total_quantity,
    round(sales_tb.unit_price * sales_tb.quantities / 12,2) as avg_monthly_sales,
    selected_tb.max_monthly_quantity,
    selected_tb.customer_age_group
from
    sales_tb
    join selected_tb on sales_tb.product_id = selected_tb.product_id
order by
    total_sales desc,
    sales_tb.product_id

做三张临时表即可解决问题。（代码有冗余，仅作为思路参考）