Uber-style Company ChallengeBeginnerVerified answerSQLite live

Completed Trips Count

How many completed trips has each rider taken?

  • Joins
  • Aggregation
  • HAVING
  • Filtering
  • Sorting

Challenge brief

Understand the request

Rider Analytics is reviewing rider activity and needs to see how many completed trips each rider has taken.

Show completed trip count per rider, ordered by most trips first.

Return

  • rider_name (first + last)
  • completed_trips

Constraints

  • Count only trips whose status is 'completed'
  • Return riders with at least one completed trip
  • Order by completed-trip count 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
  • trip_statusVARCHAR(20)

Hints, when you need them

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

Hint 1

Rider names are in riders. Trip records are in trips. JOIN on rider_id. Filter to completed trips. COUNT per rider.

Hint 2

INNER JOIN riders to trips on rider_id. WHERE trip_status = 'completed'. GROUP BY rider_id. COUNT(t.trip_id) AS completed_trips. HAVING COUNT >= 1.

Hint 3

Join riders to their trip facts, filter the qualifying status before aggregation, and use the grouped count in both the threshold and ordering.

Verified SQL answer

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

Reveal solution and explanation
SELECT r.first_name || ' ' || r.last_name AS rider_name, COUNT(t.trip_id) AS completed_trips FROM riders r INNER JOIN trips t ON r.rider_id = t.rider_id WHERE t.trip_status = 'completed' GROUP BY r.rider_id, r.first_name, r.last_name HAVING COUNT(t.trip_id) >= 1 ORDER BY completed_trips DESC, r.rider_id

Why this works

Alice Anderson has 2 completed trips (trip_id 1001 and 1006). INNER JOIN with trip_status filter automatically excludes riders with no completed trips, making the HAVING redundant here — but HAVING COUNT >= 1 is good practice.

Success check

5 riders — Alice Anderson has 2 (the only repeat rider); all others have 1

Expected result

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

rider_namecompleted_trips
Alice Anderson2
Bob Baker1
Carol Clark1
Daniel Davis1
Emma Evans1

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.