Challenge
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 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.
-- Step 1: badges per gymExercise 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.
-- Step 2: the share of winsExercise 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.
-- Step 3: the strongest of each type