Interview Question Bank · ROW_NUMBER · filter before numbering
4. Each rider's third completed trip
EasyRide-sharingQuestion #277
Concept: ROW_NUMBER · filter before numberingshow
To get the Nth event per person, number each person's rows with ROW_NUMBER() OVER (PARTITION BY person ORDER BY time) and keep row number N in an outer query. People with fewer than N rows have no row N, so they drop out on their own. Filter out rows that shouldn't count in the CTE's WHERE, before numbering, or they use up numbers.
Example (different data):
WITH n AS (
SELECT student_id, submitted_at, score,
ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY submitted_at) AS attempt
FROM quiz_attempts
WHERE status = 'graded'
)
SELECT * FROM n WHERE attempt = 2;Your task
Corvo Rides, a ride-hailing app, gives riders a loyalty badge on their third completed trip and wants to check who has earned it so far. Cancelled requests don't count.
For every rider with at least three completed trips, return rider (the rider's name), trip_id, requested_at and city of their third completed trip, counting in requested_at order. Sort by rider.
Starting Postgres in your browser…
Checking your access…