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

select
    customers.name as customer_name,
    total_travel_cost,
    order_count,
    avg_order_price
from
    (
        select
            customer_id,
            sum(price) as total_travel_cost,
            count(package_id) as order_count,
            round(avg(price), 2) as avg_order_price
        from
            bookings
            left join packages on package_id = packages.id
        where date_format(booking_date,'%Y')=2024
        group by
            customer_id
        having
            total_travel_cost > 10000
    ) as t1
    left join customers
    on t1.customer_id=customers.id
    order by total_travel_cost desc;