WITH t1 AS(
SELECT DISTINCT
    p.product_id,
    SUM(o.quantity * unit_price) total_sales,
    p.unit_price,
    SUM(o.quantity) total_quantity,
    ROUND(SUM(o.quantity * unit_price)/12, 2) avg_monthly_sales,
    MAX(quantity) max_monthly_quantity
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN products p ON p.product_id = o.product_id
GROUP BY p.product_id
)

SELECT
    t2.product_id,
    t2.total_sales,
    t2.unit_price,
    t2.total_quantity,
    t2.avg_monthly_sales,
    t2.max_monthly_quantity,
    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 customer_age_group
FROM(
    SELECT
        t1.product_id,
        t1.total_sales,
        t1.unit_price,
        t1.total_quantity,
        t1.avg_monthly_sales,
        t1.max_monthly_quantity,
        MIN(c.customer_age) customer_age
    FROM t1
    JOIN orders o ON t1.product_id = o.product_id AND o.quantity = t1.max_monthly_quantity
    JOIN customers c ON o.customer_id = c.customer_id
    GROUP BY t1.product_id
    ORDER BY t1.total_sales DESC, t1.product_id) t2