SQL Challenges · THE GREAT DUCK MIGRATION!
Water Specialists Identification
Difficulty: medium
Scout Samantha returns from reconnaissance with urgent news. The migration route crosses three major rivers this year! We need to identify our natural water specialists - ducks who perform better in swimming than in flying_speed. These athletes will lead river crossings and assist weaker swimmers. Compare each ducks swimming average to their flying_speed average and find those with a natural aquatic advantage!
Your task
Return the sum of SSNs for all ducks whose career average score in swimming is higher than their career average score in flying_speed. Only include ducks who have competed in both 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/29.md