Uber easy JOINGROUP BYCOUNTAVGCOALESCEROUND

For each rider, calculate the total number of trips they've taken and their average fare per trip. Treat any trip with a missing (NULL) fare_amount as 0 when calculating the average, rather than excluding it. Return rider_name, total_trips, and avg_fare (rounded to 2 decimal places), sorted by avg_fare in descending order.

Uber · Driver Earnings — practice this real-world SQL scenario live in your browser.

📖 Story Uber · Driver Earnings

Wednesday morning at Uber's Rider Experience team. A few trips in the system have no fare recorded at all — the ride ended before the fare could be calculated, maybe from an app crash or a cancelled payment. The team wants each rider's average fare per trip, but those fare-less trips still happened and still used a driver's time, so they shouldn't just be dropped from the average — they need to count as zero, not disappear.

🎯 Your Mission

📋 Table Structure

🗂 riders

rider_id INTEGER 1

rider_name TEXT Ravi Shankar

city TEXT Bangalore

🗂 rider_trips

trip_id INTEGER 1

rider_id INTEGER 1

fare_amount REAL 250

⚡ Step-by-Step Walkthrough

Calculate total trips and average fare per rider

query.sql

SELECT r.rider_name,
       COUNT(t.trip_id) AS total_trips,
       AVG(t.fare_amount) AS avg_fare
FROM riders r
JOIN rider_trips t ON t.rider_id = r.rider_id
GROUP BY r.rider_id, r.rider_name
ORDER BY r.rider_name;

💡 Explanation

riders and rider_trips are joined on rider_id so each trip can be attributed to the rider who took it. JOIN naturally drops nothing here, since every trip has a valid rider_id — the missing data problem in this question is the fare, not the relationship between the two tables.
COUNT(t.trip_id) counts trips using the trip_id column specifically, not fare_amount or a bare COUNT(*) — trip_id is never NULL, so every trip a rider took is counted correctly, including the ones with no recorded fare.
AVG(t.fare_amount) computes the average directly on the raw fare column. SQL's AVG() ignores NULL values entirely — both from the sum and from the count it divides by — so a rider with one NULL-fare trip out of three gets averaged over the two priced trips only, not all three.
Look at Priya Desai: her average here is 475, computed from just two priced trips (500 and 450), with her third trip — the one with no fare — silently excluded from the calculation entirely.

Fix the average with COALESCE so missing fares count as zero

query.sql

SELECT r.rider_name,
       COUNT(t.trip_id) AS total_trips,
       AVG(COALESCE(t.fare_amount, 0)) AS avg_fare
FROM riders r
JOIN rider_trips t ON t.rider_id = r.rider_id
GROUP BY r.rider_id, r.rider_name
ORDER BY r.rider_name;

💡 Explanation

The only change from the previous step is wrapping fare_amount in COALESCE(t.fare_amount, 0) inside the AVG — everything else about the query, including which rows are counted, stays the same.
COALESCE(t.fare_amount, 0) replaces a NULL fare with 0 before AVG ever sees it. A trip with no recorded fare now genuinely participates in the average as a zero-value trip, instead of being invisible to the calculation.
This changes the denominator's effective weight, not just which values get summed: for a rider with one NULL-fare trip out of three, the average now divides by all three trips' worth of value (two fares plus one zero), not just the two priced ones.
Priya Desai's average drops from 475 to about 316.67 — same three trips, same two real fares, but now her fare-less trip pulls the average down instead of being erased from it. Kiran Rao's average is cut in half, from 90 to 45, for the same reason.

Final query with clean output

query.sql

SELECT r.rider_name,
       COUNT(t.trip_id) AS total_trips,
       ROUND(AVG(COALESCE(t.fare_amount, 0)), 2) AS avg_fare
FROM riders r
JOIN rider_trips t ON t.rider_id = r.rider_id
GROUP BY r.rider_id, r.rider_name
ORDER BY avg_fare DESC;

💡 Explanation

ROUND(..., 2) rounds the average to two decimal places — AVG on a REAL column can return long trailing decimals (223.333333...) even when the underlying data is clean money values.
ORDER BY avg_fare DESC sorts the final output by the corrected average, highest first — this is a plain ORDER BY on the outer query, separate from anything inside the aggregate functions.
Notice the ranking itself changes between the naive and corrected versions, not just the numbers: Priya Desai was highest in step 1 (475) but drops to second place here (316.67), behind Divya Menon, once her fare-less trip is counted instead of ignored.
This is the complete answer: every rider who has taken at least one trip, with their true trip count and an average fare that accounts for every trip they took — including the ones that ended without a fare on record.

▶ Practice this live ← Browse all questions