题解 | 统计每个产品的销售情况（没有那么复杂吧老铁）
统计每个产品的销售情况
https://www.nowcoder.com/practice/d431aa7bf72c4fd7b048ec639bc83ad2
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
