Interview Question Bank · PERCENTILE_CONT · WITHIN GROUP
4. Median booking lead time by channel
MediumHospitalityQuestion #209
Concept: PERCENTILE_CONT · WITHIN GROUPshow
The median is the middle value, so a handful of bookings made months ahead can't drag it the way they drag an average. Postgres computes it with the ordered-set aggregate PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x), which averages the two middle values when the count is even; PERCENTILE_DISC returns an actual value instead (the lower middle one). PERCENTILE_CONT returns double precision, so cast to numeric before ROUND.
Example (different data):
SELECT region, COUNT(*) AS shipments, ROUND((PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY delivered_on - ordered_on))::numeric, 1) AS median_days FROM shipments GROUP BY region;
Your task
Tidewell Hotels sells rooms directly, through online travel agencies (OTAs) and through corporate travel desks. Marketing wants to know how far ahead each channel typically books, without a few far-ahead bookings skewing it.
For each channel in tr_bookings, return channel, bookings (every booking, cancelled and upcoming included) and median_lead_days: the median of check_in - booked_at, rounded to 1 decimal. With an even number of bookings, the median is the average of the two middle values. Sort by median_lead_days descending, then channel.
Starting Postgres in your browser…
Checking your access…