Delivery Captains Leading Five or More Riders — SQL Medium Problem

Problem statement

A food-delivery company groups its riders into squads. Each rider reports to a captain, who is also a rider in the same table; captain_id is NULL for a rider who reports to nobody.

Return the name of every rider who has at least five riders reporting directly to them, in a column named captain. A report's own reports do not count. A captain_id that matches no rider in the table (a captain who has left the company) is ignored. Return the rows in any order.

Tables

Table: Rider

Primary key: rider_id.

captain_id is the rider_id of the rider's captain, or NULL. It may name a rider who is no longer in the table.

Examples

Example 1

Rider

Output

How to solve Delivery Captains Leading Five or More Riders

Two questions hide in one: how many riders report to each person, and what is that person's name. The count comes from grouping the reports by captain_id; the name lives on a different row — the captain's own — so the table is used twice.

The reference query self-joins: c is the captain and r each rider whose captain_id points at c. Grouping by the captain's id (and name, so the name can be selected) leaves one group per captain with one row per direct report, and HAVING COUNT(*) >= 5 keeps the big squads. Grouping by rider_id rather than name keeps two captains who share a name apart.

The edges are handled by the join itself. Riders with NULL captain_id match no captain. A captain_id belonging to someone who has left matches no row of c, so their squad, however large, never reaches the answer. Only direct reports are counted because the join follows one link, not a chain.

The alternatives count first and look the names up afterwards — with IN over a grouped subquery, a correlated COUNT(*) per rider, or a join to a derived table of squad sizes. All of them read the table about twice; with an index on captain_id the correlated count is one range scan per rider.

Reference solution (MySQL)

SELECT c.name AS captain
FROM Rider c
JOIN Rider r ON r.captain_id = c.rider_id
GROUP BY c.rider_id, c.name
HAVING COUNT(*) >= 5

Another way

SELECT name AS captain FROM Rider WHERE rider_id IN (SELECT captain_id FROM Rider GROUP BY captain_id HAVING COUNT(*) >= 5)

Another way

SELECT name AS captain FROM Rider c WHERE (SELECT COUNT(*) FROM Rider r WHERE r.captain_id = c.rider_id) >= 5

Another way

SELECT c.name AS captain FROM Rider c JOIN (SELECT captain_id, COUNT(*) AS squad FROM Rider WHERE captain_id IS NOT NULL GROUP BY captain_id) s ON s.captain_id = c.rider_id WHERE s.squad >= 5