Drill 8 of 9 in Hospitality questions
0

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…