Urban Company hard NTH_VALUEWINDOW clauseROWS BETWEENPARTITION BY

Ignoring cancelled bookings, return one row per customer_name with first_service (the service of their earliest booking by booked_at) and second_service (the service of their second booking), using NTH_VALUE(service, 2). Customers with only one non-cancelled booking must show second_service as NULL. Order by customer_name.

Urban Company · Home Services Ops Analytics — practice this real-world SQL scenario live in your browser.

📖 Story Urban Company · Home Services Ops Analytics

Urban Company's growth team is designing cross-sell nudges: if most people who start with AC Repair come back for Home Cleaning, the app should suggest Home Cleaning right after an AC Repair job. To find these paths they need, for every customer, the first service they booked and the second service they booked. A cancelled booking is not a real purchase, so it must be ignored when deciding what was 'first' and 'second'. Customers who have booked only one real service so far should still appear, with second_service = NULL — meaning 'hasn't come back yet', which is exactly the group the nudge campaign is meant to target.

🎯 Your Mission

📋 Table Structure

🗂 uc_bookings

booking_id INTEGER 1

customer_name TEXT Ananya

partner_name TEXT Ravi

service TEXT Home Cleaning

booked_at TEXT 2026-03-01 09:15

scheduled_at TEXT 2026-03-03 10:00

completed_at TEXT 2026-03-03 13:30

status TEXT completed

rating INTEGER 5

⚡ Step-by-Step Walkthrough

List each customer's real (non-cancelled) bookings in order

query.sql

SELECT customer_name, booking_id, service, booked_at, status
FROM uc_bookings
WHERE status <> 'cancelled'
ORDER BY customer_name, booked_at;

💡 Explanation

WHERE status <> 'cancelled' removes bookings 5 and 10, leaving 13 rows across 7 customers; in_progress bookings stay in, because Manoj has genuinely bought both his services even though neither job has finished.
Kabir's very first booking (5, Home Cleaning) was cancelled, so after filtering his only real booking is AC Repair — without the filter his 'first service' would wrongly be a service he never actually received.
Lakshmi is the mirror case: her second booking (10) was the cancelled one, so she has a single real booking left, and Neeraj simply booked once — all three should end up with second_service = NULL.
Ordering by customer_name, booked_at here is only for reading; the window function in the next steps defines its own order with ORDER BY booked_at inside OVER, independent of the outer ORDER BY.

The trap: NTH_VALUE with the default window frame

query.sql

SELECT customer_name, booked_at, service,
       NTH_VALUE(service, 2) OVER (PARTITION BY customer_name ORDER BY booked_at) AS second_service
FROM uc_bookings
WHERE status <> 'cancelled'
ORDER BY customer_name, booked_at;

💡 Explanation

On every customer's first row second_service is NULL — Ananya's 09:15 Home Cleaning row says NULL even though she clearly has a second booking — and only from the second row onward does the real value appear.
With ORDER BY inside OVER and no explicit frame, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so on row 1 the frame holds just one row and there is no 2nd value to return yet.
That creates two kinds of NULL that look identical: a frame NULL (Ananya, Farhan, Ishita and Manoj on their first rows, where a second booking does exist) and a genuine NULL (Kabir, Lakshmi and Neeraj, who really have no second booking).
Picking 'the first row per customer' from this output would label all 7 customers as never having returned — the exact opposite of what the growth team needs, and with no error to warn anyone.

Fix it: a full-partition frame, then one row per customer

query.sql

SELECT DISTINCT customer_name,
       FIRST_VALUE(service) OVER w AS first_service,
       NTH_VALUE(service, 2) OVER w AS second_service
FROM uc_bookings
WHERE status <> 'cancelled'
WINDOW w AS (PARTITION BY customer_name ORDER BY booked_at
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER BY customer_name;

💡 Explanation

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING makes every row's frame the customer's entire booking history, so NTH_VALUE(service, 2) returns the same answer on every row of that customer — the frame NULLs from step 2 are gone.
The named WINDOW w clause defines the partition, order and frame once and lets FIRST_VALUE and NTH_VALUE share it, which keeps the two columns guaranteed to use the identical ordering instead of two copies that could drift apart.
Because every row of a customer now carries identical values, SELECT DISTINCT collapses them to one row each: Ananya goes Home Cleaning then Salon at Home, Farhan and Manoj both go AC Repair then Home Cleaning, Ishita goes Salon at Home then Home Cleaning.
Kabir, Lakshmi and Neeraj keep second_service = NULL, and this time it is honest — NTH_VALUE returns NULL when the frame has fewer than 2 rows — so these three are exactly the 'hasn't come back yet' audience for the nudge campaign.

▶ Practice this live ← Browse all questions