Drill 4 of 9 in Hospitality questions
0

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…