Challenge
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Season awards: Step 1
Professor Pi is preparing the end-of-season awards ceremony and needs three queries from you.
Step 1: the leaderboard. In a CTE per_trainer, count the badges of every trainer (0 for a trainer without badges — do not use the badges column of trainers). Then return badge_rank (a RANK() by badge count, highest first), name and badges, sorted by badge_rank then name.
-- Step 1: the leaderboardExercise 2 — Season awards: Step 2
Step 2: the patience award.
In a CTE waits, compute for every badge the trainer's name, earned_on and days_since_previous: the whole number of days since that trainer's previous badge (LAG partitioned by trainer, julianday, cast to an integer; NULL for a first badge). Then keep only the waits of 20 days or more and return name, earned_on and days_since_previous, longest wait first, then by earned_on.
-- Step 2: the patience awardExercise 3 — Season awards: Step 3
Step 3: the team leaders.
Return the strongest Codémon of each trainer (ignore wild Codémons): columns trainer (the trainer's name), codemon (the Codémon's name) and level, strongest first. Number the Codémons inside each team with ROW_NUMBER() in a CTE ranked, then keep position 1.
-- Step 3: the team leaders