Drill 5 of 7 in Ride-sharing questions
0

Interview Question Bank · DISTINCT ON · LEFT JOIN · the MAX(city) trap

5. Each rider's latest completed trip

MediumRide-sharingQuestion #282

Concept: DISTINCT ON · LEFT JOIN · the MAX(city) trapshow

MAX(time) and MAX(city) are worked out independently, so together they can describe a trip that never happened: MAX(city) is just the alphabetically last city. To take several columns from the same latest row, pick the row itself, with DISTINCT ON (person) ... ORDER BY person, time DESC in Postgres, or ROW_NUMBER() = 1. LEFT JOIN that onto the people you must list, so those without a qualifying row still appear with NULLs.

Example (different data):

WITH last AS (
  SELECT DISTINCT ON (pet_id) pet_id, visited_on, vet
  FROM vet_visits
  WHERE outcome = 'treated'
  ORDER BY pet_id, visited_on DESC
)
SELECT p.name, l.visited_on, l.vet
FROM pets p
LEFT JOIN last l ON l.pet_id = p.id;

Your task

Corvo Rides' re-engagement team wants every rider who has ever requested a trip, with when and where they last actually rode. Riders whose requests were all cancelled should still be listed, with blanks.

For every rider with at least one row in rt_trips, return rider (name), last_trip_at (requested_at of their latest completed trip) and last_city (that same trip's city). Both are NULL if the rider has no completed trip. Sort by rider.

Starting Postgres in your browser…

Checking your access…