CASE FILE · No. 004 · 1888
The Cab Ranks
MARYLEBONE, LONDON · DIFFICULTY: APPRENTICE
THE BRIEFING
A house on Vere Street was emptied on the night of 19 June. Whoever did it knew when the family left, when the maid came back, and which window had a broken catch. They had been watching. The cab company keeps a book of every fare: which driver, when, and which street they set the passenger down in. Five drivers, four weeks, a couple of hundred journeys. Far too many rows to read. You are not looking for a journey. Lots of drivers set down in Vere Street, because people live there. You are looking for a driver who did it far more often than the rest. That is a counting question, and reading rows will not answer it. GROUP BY collapses all the rows for one driver into one row, and COUNT tells you how many went into it.
THIS CASE TEACHES
GROUP BY and COUNT. How to ask how many, per person, instead of reading rows.
CLUES ON THE TABLE
- 01.The house is in Vere Street. Filter to that street first, then count.
- 02.GROUP BY driver_id turns many rows per driver into one row per driver.
- 03.COUNT(*) counts the rows that fell into each group. Give it a name with AS so you can sort by it.
- 04.The journeys table holds driver_id, not names. You already know how to turn a number into a name.
- 05.Every other driver went there once or twice over four weeks. One did not.
/ THE EVIDENCE
Database Schema
These are the tables you can query. The lines are the keys that join them. Hover a table to see what it connects to.
- driver_idINT
- nameVARCHAR(60)
- badge_noVARCHAR(12)
- journey_idINT
- driver_idINT→ drivers.driver_id
- picked_up_atDATETIME
- set_down_streetVARCHAR(60)
/ THE QUERY TERMINAL
Run your queries
Use SQL to explore the data. Each table holds part of the answer.
/ NOTES
Your notepad
Write down anything useful as you go.
Your notes and your last query are kept in this browser.
/ THE ACCUSATION
Name your suspect
Type the suspect's full name and submit.
NEXT CASE
The Blue Lantern Affair →"The window was smashed and the till was open. But the silver was left on the floor, and the thief took the cheapest thing in the shop."