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

with
t1 as(
    select
        customers.name as customer_name,
        round(sum(packages.price),2) as total_travel_cost,
        count(packages.id) as order_count,
        round(avg(packages.price),2) as avg_order_price
    from
        bookings
        left join packages on bookings.package_id=packages.id
        left join customers on bookings.customer_id=customers.id
    where
        booking_date between '2024-01-01' and '2024-12-31'
    group by
        customers.name
)

select
    customer_name,
    total_travel_cost,
    order_count,
    avg_order_price
from
    t1
where
    total_travel_cost>10000
order by
    total_travel_cost desc