Interview Question Bank · CASE buckets · COUNT FILTER
1. Cancellation rate by booking lead time
EasyHospitalityQuestion #205
Concept: CASE buckets · COUNT FILTERshow
To compare groups that don't exist as a column, make the column: a CASE expression turns a number (here, days between booking and arrival) into labelled buckets, and you GROUP BY it. Write each bucket's edge once, with < on the upper bound, so a value on the edge lands in exactly one bucket. Text labels sort alphabetically, so order the buckets by something numeric, like the smallest value in each.
Example (different data):
SELECT CASE WHEN age_days < 3 THEN '0-2 days'
WHEN age_days < 14 THEN '3-13 days'
ELSE '14+ days' END AS age_bucket,
COUNT(*) AS tickets,
ROUND(100.0 * COUNT(*) FILTER (WHERE escalated) / COUNT(*), 1) AS escalated_pct
FROM support_tickets
GROUP BY 1
ORDER BY MIN(age_days);Your task
Tidewell Hotels' revenue team suspects that last-minute bookings cancel more often than ones made weeks ahead, and wants the numbers before changing its deposit policy.
Leave out upcoming bookings (they can still be cancelled). Put every other booking in tr_bookings in a bucket by its lead time, check_in - booked_at: '0-6 days', '7-29 days' or '30+ days'. Return lead_bucket, bookings, cancelled (status 'cancelled') and cancel_rate_pct (cancelled as a % of bookings, rounded to 1 decimal). Sort from the shortest bucket to the longest.
Starting Postgres in your browser…
Checking your access…