Query Guest Retention by Channel | Dataford Interview Questions

Your question is Query Guest Retention by Channel. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Marriott International wants to monitor whether guests who completed a booking return in the following month. Write a PostgreSQL query that calculates monthly guest retention by the guest's booking channel in the cohort month.

Requirements

Consider only bookings with booking_status = 'Completed'.
Treat each guest as one monthly booking-channel cohort member, even if the guest has multiple bookings in that month.
For each cohort month and booking channel, calculate the number of prior-month guests, the number who completed another booking in the next month, and the retention rate as a percentage.
Replace a missing booking channel with Unknown, and sort by retention month and booking channel.

A retained guest may return through any booking channel. The segment represents the channel used in the cohort month.

Schema

guests

Column	Type	Description
guest_idPK	INT	Unique guest identifier
guest_name	VARCHAR(100)	Guest full name
loyalty_tier	VARCHAR(30)	Marriott Bonvoy loyalty tier

bookings

Column	Type	Description
booking_idPK	INT	Unique booking identifier
guest_id	INT	Guest who made the booking
booked_at	TIMESTAMP	Timestamp when the booking was made
booking_channel	VARCHAR(40)	Booking source
booking_status	VARCHAR(20)	Booking lifecycle status