SQL Challenges · THE GREAT DUCK MIGRATION!
Forward Momentum Scout
Difficulty: hard
Navigator Neptune swoops in with weather forecasting equipment and performance projection charts. Migration success requires predicting future performance! Using each ducks competition history, find who shows the strongest FORWARD momentum - comparing each attempt to their NEXT attempt. The duck with the best next-attempt projection will scout tomorrows dangers!
Your task
Return the SSN of the duck with the largest positive performance gain when comparing consecutive attempts within the same sport (ordered by competition_date). If multiple ducks tie for the largest gain, return the one with the smallest SSN. You will be working with `duck_athletes` and `performance_records` tables. Only consider positive improvements (where the next score is higher than the current score).
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/17.md