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

with
    max_sales as (
        SELECT
            product_id max_product_id,
            DATE_format (order_date, '%Y%m') M_order_date,
            SUM(quantity) max_monthly_quantity,
            DENSE_RANK() OVER (
                partition by
                    product_id
                order by
                    SUM(quantity) DESC
            ) ranking
        FROM
            orders
        WHERE
            order_date BETWEEN '2023-01-01' AND '2023-12-31'
        GROUP BY
            product_id,
            DATE_format (order_date, '%Y%m')
    ),
    max_q_c as(
        SELECT 
        t1.product_id,
        CASE 
        WHEN t2.customer_age BETWEEN 1 AND 10 THEN '1-10'
        WHEN t2.customer_age BETWEEN 11 AND 20 THEN '11-20'
        WHEN t2.customer_age BETWEEN 21 AND 30 THEN '21-30'
        WHEN t2.customer_age BETWEEN 31 AND 40 THEN '31-40'
        WHEN t2.customer_age BETWEEN 41 AND 50 THEN '41-50'
        WHEN t2.customer_age BETWEEN 51 AND 60 THEN '51-60'
        ELSE '61+'
        END age_group,
        DENSE_RANK() OVER(partition by t1.product_id order by SUM(t1.quantity) DESC,MIN(age_group) ASC) q_ranking,
        SUM(t1.quantity) sum_quantity_c
        FROM orders t1
        INNER JOIN customers t2
        ON t1.customer_id = t2.customer_id
        WHERE t1.order_date BETWEEN '2023-01-01' AND '2023-12-31' 
        GROUP BY 
        t1.product_id,        
        age_group
    )



SELECT 
    DISTINCT a1.product_id,
    a1.total_sales,
    ROUND(a1.total_sales / a1.total_quantity,2) unit_price,
    a1.total_quantity,
    a1.avg_monthly_sales,
    a1.max_monthly_quantity,
    b1.age_group customer_age_group
FROM(
    SELECT *
    FROM
        (
            SELECT
                t1.product_id,
                SUM(t1.quantity) * t2.unit_price total_sales,
                SUM(t1.quantity) total_quantity,
                ROUND((SUM(t1.quantity) * t2.unit_price) / 12, 2) avg_monthly_sales
            FROM
                orders t1
                LEFT JOIN products t2 ON t1.product_id = t2.product_id
            WHERE
                t1.order_date BETWEEN '2023-01-01' AND '2023-12-31'
            GROUP BY
                t1.product_id
        ) a
    INNER JOIN max_sales b
    ON a.product_id = b.max_product_id
    WHERE b.ranking = 1
)  a1
INNER JOIN max_q_c b1
ON a1.product_id = b1.product_id
WHERE b1.q_ranking = 1
ORDER BY a1.total_sales DESC,a1.product_id