SQL Challenges · THE GREAT DUCK MIGRATION!
Deputy Formation Leaders
Difficulty: hard
Admiral Aqua surveys the migration formation charts with a critical eye. Every lead position needs a capable deputy! If our top performers falter during the journey, we need immediate replacements ready to step up. Identify the second-ranked athlete in each sport based on 2025 performances - these deputies will shadow the leaders and take command if needed. Not every sport may have a clear second place if there are ties at the top, but find those who do qualify!
Your task
Return the sum of SSNs for all ducks who ranked exactly second in at least one sport during 2025, based on their best score in that sport. Use standard ranking where tied scores receive the same rank. Count each duck only once even if they are second in multiple sports. You will be working with `duck_athletes` and `performance_records` tables.
Tables
performance_records (800 rows)
| column | type |
|---|---|
duck_id | BIGINT |
sport | VARCHAR |
score | DOUBLE |
competition_date | DATE |
duck_athletes (50 rows)
| column | type |
|---|---|
id | BIGINT |
ssn | BIGINT |
name | VARCHAR |
age | BIGINT |
hometown | VARCHAR |
Database: https://dbquacks.com/data/databases/athletic_champions.duckdb
This page as markdown: https://dbquacks.com/challenges/athletic/24.md