SQL Challenges · THE GREAT DUCK MIGRATION!
Three in a Row
Difficulty: hard
Scout Samantha traces a line climbing through four marks on a training chart. One improvement gets applause, Duckbert. Three in a row gets my attention! I need scouts who can build on yesterdays progress. Find ducks whose scores climbed three times running, across four consecutive competition dates in the same sport. Sum their identification codes so we can put that growing confidence to work on the migration route!
Your task
Return the sum of SSNs for all ducks who have four consecutive competition dates in the same sport with strictly increasing scores: each of the second, third, and fourth scores must exceed the one before it. Consider all years; consecutive means successive dates on which that duck competed in that sport, not consecutive calendar days. Count each duck only once even if they have multiple qualifying sequences or sports. 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/41.md