Drivers Rated Above the Fleet Average — SQL Problem
Drivers whose rating is above the fleet's average get priority on airport queues.
- Difficulty: Easy
- Topics: Subqueries
- Dialect: MySQL
- Problem: #305
Problem statement
Drivers whose rating is above the fleet's average get priority on airport queues. New drivers have no rating yet (avg_rating is NULL); they are left out of the average and never qualify.
Return driver_id, name and avg_rating of every driver whose rating is strictly greater than the average rating of all rated drivers. Order by avg_rating descending, then driver_id ascending.
Tables
Table: Driver
| Column | Type |
|---|---|
| driver_id | int |
| name | varchar |
| city | varchar |
| avg_rating | decimal |
Primary key: driver_id.
avg_rating is the driver's lifetime rating out of 5 (in steps of 0.25), or NULL for a driver with no rated trips.
Examples
Example 1
Driver
| driver_id | name | city | avg_rating |
|---|---|---|---|
| 1 | Ramesh | Bengaluru | 4.75 |
| 2 | Imran | Mumbai | 4.25 |
| 3 | Lakshmi | Chennai | NULL |
| 4 | Gurpreet | Delhi | 4.5 |
| 5 | Suresh | Bengaluru | 4 |
| 6 | Farida | Hyderabad | 4.75 |
Output
| driver_id | name | avg_rating |
|---|---|---|
| 1 | Ramesh | 4.75 |
| 6 | Farida | 4.75 |
| 4 | Gurpreet | 4.5 |
How to solve Drivers Rated Above the Fleet Average
The comparison value is a single number, the fleet's average rating, so compute it in a scalar subquery — (SELECT AVG(avg_rating) FROM Driver) — and compare every row against it in WHERE.
NULLs need no special handling. AVG ignores NULL values, so unrated drivers do not drag the average down; and for an unrated driver NULL > 4.4 is unknown, which WHERE treats as false, so they never qualify. A driver exactly at the average is not above it, which is why the comparison is strict.
The alternatives compute the same average once and attach it to every row: a derived table joined with CROSS JOIN, or the window AVG(avg_rating) OVER (), which has an empty window — the whole table. All three read the table about twice. Ratings come in steps of 0.25 here, so the average and the ratings compare exactly in every engine.
Reference solution (MySQL)
SELECT driver_id, name, avg_rating
FROM Driver
WHERE avg_rating > (SELECT AVG(avg_rating) FROM Driver)
ORDER BY avg_rating DESC, driver_idAnother way
SELECT d.driver_id, d.name, d.avg_rating FROM Driver d CROSS JOIN (SELECT AVG(avg_rating) AS fleet_avg FROM Driver) f WHERE d.avg_rating > f.fleet_avg ORDER BY d.avg_rating DESC, d.driver_idAnother way
SELECT driver_id, name, avg_rating FROM (SELECT driver_id, name, avg_rating, AVG(avg_rating) OVER () AS fleet_avg FROM Driver) x WHERE avg_rating > fleet_avg ORDER BY avg_rating DESC, driver_id← Undelivered Shipments Past Their Promised Date · Dispatch Offer Acceptance Rate per Driver →