Interview Question Bank · Self-join on a date window · first match with ROW_NUMBER
8. Cancelled, then rebooked within 7 days
HardHospitalityQuestion #215
Concept: Self-join on a date window · first match with ROW_NUMBERshow
"Did X happen within N days after Y" is a self-join whose ON clause sets a window: same person, the second event on or after the first (>=, so earlier events don't sneak in), and no later than the first + N (<=, so exactly N counts). Measure from the right event: here the cancellation date, not the original booking date. When the window can hold several matches, number them with ROW_NUMBER() OVER (PARTITION BY first_event ORDER BY ...) and keep the first.
Example (different data):
WITH m AS (
SELECT c.id, c.user_id, c.ended_on, n.started_on,
ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY n.started_on, n.id) AS rn
FROM subscriptions c
JOIN subscriptions n ON n.user_id = c.user_id
AND n.id <> c.id
AND n.started_on >= c.ended_on
AND n.started_on <= c.ended_on + 14
)
SELECT * FROM m WHERE rn = 1;Your task
Tidewell Hotels wants to know how often a cancellation is really a change of plans. The CRM team counts a guest as rebooked if they book again soon after cancelling.
A cancelled booking was rebooked if the same guest made another booking (at any hotel) with booked_at on or after the cancelled_at date and no more than 7 days after it. For each cancelled booking that was rebooked, return guest (name), cancelled_booking (its id), cancelled_at, rebooking (the id of the earliest such booking by booked_at, then lowest id), rebooked_at (its booked_at) and days_to_rebook (rebooked_at - cancelled_at). Sort by cancelled_at, then guest.
Starting Postgres in your browser…
Checking your access…