Rides that cost more than the average

Easy

Question

A ride app wants to look at its expensive rides.

First find the average fare of all completed rides. Then return every completed ride whose fare is higher than that average.

Cancelled rides pay a small cancellation fee, but they are not part of the average and they are not part of the result.

Return ride_id, rider and fare. Sort by fare from high to low, then by ride_id.

The order of the rows matters for this question.

subquery
scalar subquery
avg
where

Read first: Lesson 46, SQL queries inside queries: subqueries, EXISTS, CTEs and recursion

Tables

1 table

These are the example rows. Run uses only these rows. Submit also tests your query on a second set of rows that you cannot see.

rides6 columns · 10 rowsnot run yet

CREATE TABLE rides (id INTEGER PRIMARY KEY, rider TEXT NOT NULL, driver TEXT NOT NULL, fare INTEGER NOT NULL, status TEXT NOT NULL, ride_date TEXT NOT NULL);

Example rows of the rides table
id	rider	driver	fare	status	ride_date
1	Ananya	Suresh	180	completed	2026-04-01
2	Bimal	Suresh	420	completed	2026-04-01
3	Chitra	Lata	250	completed	2026-04-02
4	Deepak	Lata	90	completed	2026-04-02
5	Ananya	Mohan	640	completed	2026-04-03
6	Esha	Mohan	30	cancelled	2026-04-03
7	Bimal	Suresh	30	cancelled	2026-04-04
8	Chitra	Lata	310	completed	2026-04-04
9	Deepak	Mohan	150	completed	2026-04-05
10	Esha	Suresh	30	cancelled	2026-04-05

About this SQL

A subquery that returns one value can be used wherever a number is allowed, in every database. You cannot write `fare > AVG(fare)` directly in WHERE: aggregates are not allowed there, so the average must come from a subquery or a window function.

SQLite

Opening the code editor…

Result

Run shows what your query gives on the example rows.Submit checks it on the example rows and on a hidden set of rows.

SQL · ready