SQL Challenges · THE GREAT DUCK MIGRATION!
Backup Roster Assembly
Difficulty: easy
Scout Samantha reviews backup rosters with tactical charts. We need reliable backup athletes! For each sport, find ducks who performed ABOVE that sports average score - these above-average performers can substitute for injured teammates. Excellence matters when lives depend on it during the perilous 3,000-mile journey!
Your task
Return the sum of SSNs for all ducks whose best score in at least one sport exceeds that sport's average score (calculated across all athletes and all attempts in that sport). Count each duck only once. 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/15.md