SQL Challenges · WEEKLY QUACK CHALLENGE!
THE TRANSFER WINDOW
Difficulty: medium
Scorekeeper Smew flaps in with a soggy scoresheet, whistle still swinging. "The Golden Paddle goes to the pond polo club that scored the most goals this season, Duckbert, and ten clubs are claiming it. The Mudflat Maulers counted every goal their current squad ever scored. The Lily Pad Lancers say Flip Featherstone's goals are theirs because they found him first. The Backwater Brawlers released Freddie Freewing in September, and they still want the goals he scored in the free-agent friendlies b…
Your task
Return the name of the club credited with the most goals this season. Each row of `roster_history` is one stint: the player belonged to `club` from `valid_from` (inclusive) up to, but not including, `valid_to`; a NULL `valid_to` means the player is still there. Credit each goal to the club whose stint covers the day it was scored, so a player who transfers on a match day scores for the new club that day. A goal scored on a day that none of the player's stints covers, such as while they were between clubs, counts for no club. Only goals with status `allowed` count; `disallowed` goals do not. On a tie, return the alphabetically first club name. You will be working with the `goals` and `roster_history` tables.
Tables
goals (105 rows)
| column | type |
|---|---|
goal_id | INTEGER |
player | VARCHAR |
scored_on | DATE |
status | VARCHAR |
roster_history (35 rows)
| column | type |
|---|---|
player | VARCHAR |
club | VARCHAR |
valid_from | DATE |
valid_to | DATE |
Database: https://dbquacks.com/data/databases/weekly_202644_17_transfer_window.duckdb
This page as markdown: https://dbquacks.com/challenges/weekly/17.md
More: practice SQL exercises online · SQL interview questions