Uber-style Company ChallengeEasyVerified answerSQLite live

Latest Trip for Each Rider

What is each rider's most recent trip — when, from where, and how much did it cost?

  • Window functions
  • Joins
  • Subqueries
  • Date analysis
  • Filtering

Challenge brief

Understand the request

Rider Experience is building a trip history summary and needs to surface the most recent ride for each rider.

Find exactly one latest trip per rider with a stable rule for tied pickup times.

Return

  • rider_name (full name)
  • latest_trip_date
  • pickup_location
  • fare_amount

Constraints

  • Return exactly one most-recent trip per rider
  • When pickup times tie, prefer the larger trip ID
  • Order by latest trip date descending, then rider ID

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

riders

  • rider_idINTEGER
  • first_nameVARCHAR(50)
  • last_nameVARCHAR(50)

trips

  • trip_idINTEGER
  • rider_idINTEGER
  • pickup_datetimeDATETIME
  • pickup_locationVARCHAR(200)
  • fare_amountREAL

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

You need the single most recent trip per rider. The technique: correlated subquery WHERE pickup_datetime = (SELECT MAX(pickup_datetime) FROM trips WHERE rider_id = outer.rider_id). This returns only the latest row per rider.

Hint 2

INNER JOIN riders to trips. WHERE t.pickup_datetime = (SELECT MAX(t2.pickup_datetime) FROM trips t2 WHERE t2.rider_id = r.rider_id). ORDER BY latest_trip_date DESC.

Hint 3

Assign a newest-first position within each rider, include a unique trip key in that ordering, and retain only the first position.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
WITH ranked_trips AS (SELECT t.*, ROW_NUMBER() OVER (PARTITION BY t.rider_id ORDER BY t.pickup_datetime DESC, t.trip_id DESC) AS rn FROM trips t) SELECT r.first_name || ' ' || r.last_name AS rider_name, t.pickup_datetime AS latest_trip_date, t.pickup_location, t.fare_amount FROM riders r INNER JOIN ranked_trips t ON r.rider_id = t.rider_id WHERE t.rn = 1 ORDER BY latest_trip_date DESC, r.rider_id

Why this works

Trips are ranked newest-first within each rider. The trip ID provides a stable tie-break, and retaining rank 1 guarantees one latest trip per rider.

Success check

5 riders — Alice Anderson is most recent (Jan 16), all others from Jan 15

Expected result

Use this output to verify values, aliases, ordering, and row count.

rider_namelatest_trip_datepickup_locationfare_amount
Alice Anderson2024-01-16 14:00:00Castro22
Emma Evans2024-01-15 12:30:00Hollywood35
Daniel Davis2024-01-15 11:00:00Manhattan55
Carol Clark2024-01-15 10:15:00Times Square28
Bob Baker2024-01-15 09:00:00Financial District45

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.