Challenge

The full text of this chapter, to read or revisit. To practise, do the exercises.

▶ Do the exercises

Exercise 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.

Starter code
-- Step 1: the leaderboard

Exercise 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.

Starter code
-- Step 2: the patience award

Exercise 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.

Starter code
-- Step 3: the team leaders
Back to the course