Drivers Rated Above the Fleet Average

Easy
305.

Drivers Rated Above the Fleet Average

EasySubqueries

MySQL

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

ColumnType
driver_idint
namevarchar
cityvarchar
avg_ratingdecimal

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.

Example

The rows must come back in the order shown.

Driver

driver_idnamecityavg_rating
1RameshBengaluru4.75
2ImranMumbai4.25
3LakshmiChennaiNULL
4GurpreetDelhi4.5
5SureshBengaluru4
6FaridaHyderabad4.75

Output

driver_idnameavg_rating
1Ramesh4.75
6Farida4.75
4Gurpreet4.5
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.

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

ColumnType
driver_idint
namevarchar
cityvarchar
avg_ratingdecimal

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_idnamecityavg_rating
1RameshBengaluru4.75
2ImranMumbai4.25
3LakshmiChennaiNULL
4GurpreetDelhi4.5
5SureshBengaluru4
6FaridaHyderabad4.75

Output

driver_idnameavg_rating
1Ramesh4.75
6Farida4.75
4Gurpreet4.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_id

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

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