Drivers with the most completed rides

Medium

Question

A ride app wants to thank its best drivers. The best drivers are the ones with the highest number of rides with status completed.

If several drivers share the highest number, return all of them. Cancelled rides do not count.

Return driver and completed_rides. The order of the rows does not matter.

subquery
group by
having
ties
max
rank

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.

rides5 columns · 19 rowsnot run yet

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

Example rows of the rides table
id	driver	rider	status	fare
1	Suresh	Ananya	completed	180
2	Suresh	Bimal	completed	420
3	Lata	Chitra	completed	250
4	Mohan	Deepak	cancelled	30
5	Mohan	Ananya	completed	640
6	Mohan	Esha	cancelled	30
7	Suresh	Bimal	cancelled	30
8	Lata	Chitra	completed	310
9	Mohan	Deepak	cancelled	30
10	Suresh	Esha	completed	90
11	Lata	Ananya	completed	150
12	Imran	Bimal	completed	200
13	Mohan	Chitra	completed	120
14	Suresh	Deepak	completed	330
15	Lata	Esha	completed	275
16	Mohan	Bimal	cancelled	30
17	Imran	Chitra	completed	410
18	Suresh	Ananya	cancelled	30
19	Mohan	Esha	completed	95

About this SQL

ORDER BY ... LIMIT 1 returns a single row and silently drops ties. To keep ties, compare with the maximum (a subquery or a CTE) or use RANK() = 1. You cannot nest aggregates like MAX(COUNT(*)) directly: use a CTE or a subquery in FROM first.

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