题解 | 查询高价值旅行套餐客户的支出与套餐详情

WITH T AS (SELECT B.customer_id, B.package_id, B.booking_date, P.price, C.name FROM bookings B
JOIN packages P ON B.package_id = P.id
JOIN customers C ON B.customer_id = C.id)

SELECT name AS customer_name, SUM(price) AS total_travel_cost, 
COUNT(*) AS order_count, 
ROUND(SUM(price)/COUNT(name),2) AS avg_order_price
FROM T
WHERE YEAR(booking_date) = 2024
GROUP BY customer_id
HAVING sum(price) > 10000
ORDER BY total_travel_cost DESC