Uber: Riders Who Gave Every Rated Trip Five Stars
The ratings-integrity team treats a rider's ratings as less informative when they always give five stars; it wants to know who those riders are before…
- Difficulty: Easy
- Topics: Aggregation
- Dialect: MySQL
- Problem: #979
Problem statement
The ratings-integrity team treats a rider's ratings as less informative when they always give five stars; it wants to know who those riders are before weighting them.
Return the riders who rated at least 3 trips and gave 5 stars on every trip they rated. A trip the rider did not rate has a NULL rating_given and is ignored — it neither counts towards the 3 nor breaks the run of fives. Return rider_id and rated_trips (the number of trips the rider rated), in any order.
A practice scenario set at Uber: the tables and data are invented, and this is not a question from, or endorsed by, Uber.
Tables
Table: RiderTrip
| Column | Type |
|---|---|
| trip_id | int |
| rider_id | int |
| driver_id | int |
| trip_date | date |
| rating_given | int |
Primary key: trip_id.
One row per completed trip. rating_given is the 1–5 star rating the rider gave the driver, or NULL if they skipped it.
Examples
Example 1
RiderTrip
| trip_id | rider_id | driver_id | trip_date | rating_given |
|---|---|---|---|---|
| 1 | 1 | 50 | 2024-04-01 | 5 |
| 2 | 1 | 51 | 2024-04-03 | 5 |
| 3 | 1 | 52 | 2024-04-07 | 5 |
| 4 | 2 | 50 | 2024-04-02 | 5 |
| 5 | 2 | 53 | 2024-04-04 | NULL |
| 6 | 2 | 54 | 2024-04-05 | 5 |
| 7 | 2 | 51 | 2024-04-09 | 5 |
| 8 | 3 | 52 | 2024-04-02 | 5 |
| 9 | 3 | 50 | 2024-04-06 | 4 |
| 10 | 3 | 55 | 2024-04-08 | 5 |
| 11 | 4 | 53 | 2024-04-03 | 5 |
| 12 | 4 | 54 | 2024-04-04 | 5 |
| 13 | 5 | 50 | 2024-04-05 | 5 |
| 14 | 5 | 52 | 2024-04-06 | 5 |
| 15 | 5 | 51 | 2024-04-07 | NULL |
Output
| rider_id | rated_trips |
|---|---|
| 1 | 3 |
| 2 | 3 |
How to solve Uber: Riders Who Gave Every Rated Trip Five Stars
Both conditions describe a rider's whole history, so the query groups by rider_id and tests the groups in HAVING.
Two aggregate facts do the work. COUNT(rating_given) counts only the trips that carry a rating — unlike COUNT(*), it skips the NULLs, which is exactly the "ignored" rule — so rider 5, with two fives and a skipped rating, has only 2 rated trips and is out. And "every rating is five" is the same as "the minimum rating is five": MIN also skips NULLs, so rider 2's skipped trip does not break the run, while rider 3's single 4 does.
The first alternative filters out unrated trips in WHERE and counts the non-five ratings, requiring zero of them. The second uses NOT EXISTS to reject any rider who has a rating below five. All three read the table once (the NOT EXISTS version with a lookup per rider), and none depends on row order.
Reference solution (MySQL)
SELECT rider_id, COUNT(rating_given) AS rated_trips
FROM RiderTrip
GROUP BY rider_id
HAVING COUNT(rating_given) >= 3 AND MIN(rating_given) = 5Another way
SELECT rider_id, COUNT(*) AS rated_trips FROM RiderTrip WHERE rating_given IS NOT NULL GROUP BY rider_id HAVING COUNT(*) >= 3 AND SUM(CASE WHEN rating_given <> 5 THEN 1 ELSE 0 END) = 0Another way
SELECT t.rider_id, COUNT(*) AS rated_trips FROM RiderTrip t WHERE t.rating_given IS NOT NULL AND NOT EXISTS (SELECT 1 FROM RiderTrip x WHERE x.rider_id = t.rider_id AND x.rating_given < 5) GROUP BY t.rider_id HAVING COUNT(*) >= 3← Uber: Airport Queue Dispatches per Terminal · Uber: Cities With Two or More Critical Safety Reports →