Turn a Vague Retention Request into a Metric and SQL at a Home-Sharing Marketplace

A two-sided home-sharing marketplace, where guests book stays at listings that hosts publish, is hiring an analytics lead. In a case-oriented SQL round you are given a short business background and a few datasets, and leadership's request is deliberately general: "we want to look at retention." Explain how you interpret what leadership is asking for, which metric you would use and exactly how it is calculated, and then write the SQL that computes it from the datasets. The report describes this round's format, with retention as its example topic, but does not list the datasets. This version assumes the tables below; treat them as the data you were handed, and say if you would need anything else. ### Assumed Datasets **`users`**: one row per account. | Column | Type | Meaning | | --- | --- | --- | | `user_id` | INTEGER | Primary key | | `signup_date` | DATE | Date the account was created | | `country` | TEXT | Country of the account | **`listings`**: one row per listing. | Column | Type | Meaning | | --- | --- | --- | | `listing_id` | INTEGER | Primary key | | `host_id` | INTEGER | Host who owns the listing (`users.user_id`) | | `created_date` | DATE | Date the listing was published | **`bookings`**: one row per booking. | Column | Type | Meaning | | --- | --- | --- | | `booking_id` | INTEGER | Primary key | | `guest_id` | INTEGER | Guest who made the booking (`users.user_id`) | | `listing_id` | INTEGER | Booked listing | | `booked_at` | TIMESTAMP | When the booking was made | | `checkin_date` | DATE | First night of the stay | | `nights` | INTEGER | Length of the stay | | `status` | TEXT | Current state: `confirmed` or `cancelled` | **`sessions`**: one row per logged-in visit to the website or app. | Column | Type | Meaning | | --- | --- | --- | | `session_id` | INTEGER | Primary key | | `user_id` | INTEGER | Visitor (`users.user_id`) | | `session_date` | DATE | Date of the visit | ### Clarifying Questions - What decision will the number support: a recurring leadership dashboard, a company goal, or a diagnosis of a recent change? - Does leadership mean guests, hosts, or both sides of the marketplace? - Is there an existing company definition of an active guest or host that this metric must stay consistent with? - How much history do the tables cover, and what is the last date with complete data? ### Part 1 — Interpret the request Before writing any SQL, explain what "retention" could mean for this business and how you would narrow it down with leadership. ```hint How often do people travel? Compare how often a typical guest books a trip with how often a typical user of a messaging app opens it. What does that imply for the retention window? ``` #### What This Part Should Cover - The candidate populations (guests, hosts) and the behaviors that could count as "retained" for each - How booking frequency and seasonality shape a sensible time window - The questions to ask leadership, each tied to the decision the metric will inform ### Part 2 — Define the metric Choose a primary retention metric and write it as a precise specification: the population, the cohort, the event that counts as retention, the window, the numerator and denominator, and the rules for edge cases. Name any supporting metrics you would report next to it. ```hint Cohorts that have not finished A guest who first booked last month has not yet had a full window in which to come back. Decide how such guests should enter the metric. ``` #### What This Part Should Cover - An unambiguous numerator, denominator, cohort definition and window - Treatment of cancellations, repeat bookings that belong to the same trip, and cohorts that are not yet complete - At least one leading or companion metric, and why the primary metric alone is not enough ### Part 3 — Write the SQL Using the tables above, write a PostgreSQL query that computes your primary metric, broken down the way you would report it to leadership. ```hint Anchor on the first event Find each guest's first qualifying booking before looking for anything that comes after it. ``` #### What This Part Should Cover - A correct per-user anchor event and a correctly bounded window - Exclusion of cohorts whose window runs past the end of the data - Counting that does not double-count users with several later bookings ### What a Strong Answer Covers - Turns a vague request into a defined metric by asking about the decision it supports, not by applying a familiar template - Fits the retention event and window to how often guests actually travel and to seasonality - A specification precise enough that two analysts would compute the same number - SQL that matches the specification, including incomplete cohorts and cancellations - Awareness of mix effects across cohorts (market, acquisition channel, type of first trip) when the metric moves ### Follow-up Questions - Suppose your primary metric uses a 12-month window, so a cohort is complete only a year after it starts. What leading indicator would you put on a weekly dashboard, and how would you check that it predicts the slower metric? - The metric fell for the three most recent complete cohorts. How would you tell a change in the mix of new guests apart from a real decline in their experience? - How would you define host retention, and what would change in the query?