Challenge

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

▶ Do the exercises

Exercise 1 — Season dashboard: Step 1

Professor Pi is building the season dashboard and needs three queries from you.

Step 1: badges per gym. Write a CTE per_gym counting the badges handed out by each gym_id, then return every gym's name and badges_given (0 when none), most generous first, then by name.

Starter code
-- Step 1: badges per gym

Exercise 2 — Season dashboard: Step 2

Step 2: the share of wins.

Chain two CTEs: wins_per_trainer (battles won per winner_id, as wins) and shares, which reads the first one and adds win_share = wins × 100 / the total of all wins (a scalar subquery on wins_per_trainer). Then return name, wins and win_share of the trainers who won at least once, most wins first.

Starter code
-- Step 2: the share of wins

Exercise 3 — Season dashboard: Step 3

Step 3: the strongest Codémon of each type.

Write a CTE best_per_type with, for each type, the highest level as top_level. Then join codemon to that CTE on both the type and the level, and return type, name and level of each type's champion, sorted by type.

Starter code
-- Step 3: the strongest of each type
Back to the course