Uber: Riders Who Gave Every Rated Trip Five Stars

Easy
979.

Uber: Riders Who Gave Every Rated Trip Five Stars

EasyAggregation

MySQL

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

ColumnType
trip_idint
rider_idint
driver_idint
trip_datedate
rating_givenint

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.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

RiderTrip

trip_idrider_iddriver_idtrip_daterating_given
11502024-04-015
21512024-04-035
31522024-04-075
42502024-04-025
52532024-04-04NULL
62542024-04-055
72512024-04-095
83522024-04-025
93502024-04-064
103552024-04-085
5 more rows

Output

rider_idrated_trips
13
23
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
trip_idint
rider_idint
driver_idint
trip_datedate
rating_givenint

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_idrider_iddriver_idtrip_daterating_given
11502024-04-015
21512024-04-035
31522024-04-075
42502024-04-025
52532024-04-04NULL
62542024-04-055
72512024-04-095
83522024-04-025
93502024-04-064
103552024-04-085
114532024-04-035
124542024-04-045
135502024-04-055
145522024-04-065
155512024-04-07NULL

Output

rider_idrated_trips
13
23

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) = 5

Another 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) = 0

Another 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 →