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

with
    a as (
        select
            *,
            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,
            unit_price*quantity as sales
        from
            customers
            left join orders using (customer_id)
            left join products using (product_id)
    ),
b as(select product_id,
            unit_price,
            customer_age_group,
            max(quantity) max_monthly_quantity,
            row_number() OVER (PARTITION BY product_id ORDER BY max(quantity) DESC) rk
        from
            a
        group by
            product_id,
            unit_price,
            customer_age_group
    ),
c as (select     product_id,
            unit_price,
            customer_age_group,
            max_monthly_quantity
            from b where rk=1),
d as (select
    product_id,
    sum(sales) total_sales,
    sum(quantity) total_quantity,
    round(sum(sales) / 12,2) avg_monthly_sales
    from a
    GROUP BY
    product_id
order by
    total_sales desc,
    product_id
)
select product_id,total_sales,unit_price,total_quantity,avg_monthly_sales,max_monthly_quantity,customer_age_group
from c join d using (product_id)
order by total_sales desc,product_id