# THE TRANSFER WINDOW

WEEKLY QUACK CHALLENGE! · challenge 17 · medium · 5 guesses

> 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 before Willow signed him! Seven players swapped jerseys in the transfer window, and a few of them scored on the very day they signed. Credit every goal to the club the scorer actually played for that day, before the engraver goes home!"

## 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 |

## Data

The tables live in a DuckDB database: https://dbquacks.com/data/databases/weekly_202644_17_transfer_window.duckdb

To query it outside the browser, attach it read-only:

```sql
ATTACH 'https://dbquacks.com/data/databases/weekly_202644_17_transfer_window.duckdb' AS db (READ_ONLY);
USE db;
```

Play it in the browser: https://dbquacks.com/challenges/weekly/17

## Answer

The expected answer is not published. Submit on the page to have it checked.