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

with
    t1 as (
        select
            product_id,
            customer_age_group
        from
            (
                select
                    rank() over (
                        partition by
                            product_id
                        order by
                            sa desc,customer_age_group
                    ) as r,
                    product_id,
                    customer_age_group
                from
                    (
                        select
                            product_id,
                            
                            sum(quantity) as sa,
                            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
                            customers
                            join orders using (customer_id)
                        where
                            year(order_date) = 2023
                        group by
                        product_id,
                                                        customer_age_group
                        order by
                            product_id
                    ) as t
            ) as tem
        where
            r = 1
    ),
    t2 as (
        select
            product_id,
            max(mo) as max_monthly_quantity
        from
            (
                select
                    product_id,
                    month(order_date) as month,
                    sum(quantity) as mo
                from
                    orders
                where
                    year(order_date) = 2023
                group by
                    product_id,
                    month(order_date)
                order by
                    product_id
            ) as monthly_sales
        group by
            product_id
    ),

    t3 as (
        select
            product_id,
            sum(unit_price * quantity) as total_sales,
            unit_price,
            sum(quantity) as total_quantity,
            round(sum(quantity * unit_price) / 12, 2) as avg_monthly_sales
        from
            customers
            join orders using (customer_id)
            join products using (product_id)
        group by
            product_id,
            unit_price
        
    )
  select product_id
  ,total_sales
  ,unit_price
  ,total_quantity
  ,avg_monthly_sales
  ,max_monthly_quantity
  ,customer_age_group
  from t1 join t2  using(product_id)
  join t3 using(product_id)
  order by
            total_sales DESC,
            product_id