SQL Challenges · THE GREAT DUCK MIGRATION!
The Rescue Pair
Difficulty: hard
Admiral Aqua holds up two rescue harnesses beside the migration map. Two places, Duckbert. Four disciplines. Your finest swimmers wont help much if neither can clear the ridge! I need a pair who cover each others weaknesses. In each sport, the stronger duck will take the lead. Judge the pair by the weakest of those four lead scores, then find our strongest rescue team. Add their identification codes to register the pair!
Your task
Return the sum of the two SSNs in the highest-rated rescue pair. A pair consists of two different ducks. Use each duck's all-time best score in each of the four sports: swimming, running, diving, and flying_speed. For each sport, the pair's score is the higher of the two ducks' best scores; the pair's overall rating is the lowest of its four sport scores. Choose the pair with the highest overall rating. On a tie, choose the pair with the smaller sum of the two SSNs. You will be working with the `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/42.md