Interview Question Bank · generate_series · LEFT JOIN · date subtraction
3. Length-of-stay distribution, gaps included
MediumHospitalityQuestion #208
Concept: generate_series · LEFT JOIN · date subtractionshow
A distribution should show the values nobody hit, and GROUP BY can only return values that exist. Generate every value with generate_series, LEFT JOIN the data onto it, and count a column from the data side, because COUNT(*) counts the one all-NULL row an empty value still gets. For shares, divide by the total number of rows, computed once in a subquery.
Example (different data):
SELECT s.size, COUNT(o.items) AS orders FROM generate_series(1, (SELECT MAX(items) FROM orders)) AS s(size) LEFT JOIN orders o ON o.items = s.size GROUP BY s.size ORDER BY s.size;
Your task
Tidewell Hotels is redesigning its rate plans around typical stay lengths and wants to see how long guests actually stay, including the lengths no one booked.
For completed stays (status 'stayed'), the length is check_out - check_in nights. Return nights for every length from 1 up to the longest stay, stays (how many stayed bookings had that length, 0 if none) and pct_of_stays (stays as a % of all stayed bookings, rounded to 1 decimal). Sort by nights.
Starting Postgres in your browser…
Checking your access…