Capstone: Part 1
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — The Season Report
Welcome to your first final challenge! The league council wants a single table that sums up every trainer's season. Build it step by step with CTEs, then assemble everything in one final query.
This challenge combines CTEs, joins, GROUP BY, CASE, UNION ALL and COALESCE. Take your time: only the result of the last SELECT is displayed, so while you work you can end your script with SELECT * FROM badge_counts; (or any other CTE) to check a step on its own.
Step 1: a CTE badge_counts with, for each trainer_id of the badges table, the number of badges as badges.
Step 2: a CTE win_counts with, for each winner_id of the battles table, wins (battles won) and home_wins (battles won in a gym whose city is the winner's hometown). Join gyms and trainers, and count the home wins with SUM(CASE … END).
Step 3: a CTE last_events with, for each trainer, last_event: the most recent date among their badges (earned_on) and the battles they won (battle_date). Stack the two lists of dates with UNION ALL, then take the MAX per trainer.
Step 4: the final query. Every trainer (Jordan included) with name, hometown, badges, wins, home_wins (0 when none) and last_event (NULL when none): trainers joined to the three CTEs with LEFT JOIN, sorted by wins descending, then badges descending, then name.
Expected result (the last SELECT):
name | hometown | badges | wins | home_wins | last_event
Morgan | Azure City | 5 | 3 | 2 | 2023-03-20
Taylor | Stone Ridge | 2 | 2 | 1 | 2023-03-18
Alex | Sunset Valley | 3 | 1 | 1 | 2023-03-01
Jordan | Azure City | 0 | 0 | 0 | NULL-- Step 1: badge_counts
-- Step 2: win_counts
-- Step 3: last_events
-- Step 4: the final report