SQL Challenges · WEEKLY QUACK CHALLENGE!
QUACKBER SURGE
Difficulty: medium
Dispatcher Scaup slams a tablet onto the counter, screen still glowing with Saturdays ride map. Payday, Duckbert, and every driver in the lot is claiming the crown. Molly says she drove the most rides. Big Bill says his airport runs carry the biggest base fares. But a fare is only worth what the meter said at the moment the duck hopped in! Price every ride at the surge that was live at pickup. Then tell me who actually banked the most — or nobody gets paid tonight!
Your task
Every ride earns its base fare multiplied by the surge multiplier in effect at pickup. The surge schedule is in `read_csv('data/files/202637_11_quackber_surge/surge_log.csv', header=true)`. Each multiplier applies from its start time up to, but not including, the next multiplier's start time; the last one applies for the rest of the day. Return the name of the driver with the highest total earnings; on a tie, the alphabetically first. You will be working with the `rides` and `drivers` tables and the surge file.
Tables
rides (23 rows)
| column | type |
|---|---|
ride_id | INTEGER |
driver_id | INTEGER |
picked_up_at | TIMESTAMP |
base_fare | DECIMAL(6,2) |
drivers (4 rows)
| column | type |
|---|---|
driver_id | INTEGER |
name | VARCHAR |
Files
data/files/202637_11_quackber_surge/surge_log.csv (10 rows)
| column | type |
|---|---|
started_at | TIMESTAMP |
multiplier | DOUBLE |
Database: https://dbquacks.com/data/databases/weekly_202637_11_quackber_surge.duckdb
This page as markdown: https://dbquacks.com/challenges/weekly/11.md