Drill 3 of 9 in Hospitality questions
0

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…