Capstone: Part 3
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — The Analytics Dashboard
Third and last final challenge: the council wants the official leaderboard — with a twist: how long each trainer waited between two badges. CTEs and window functions, assembled in one query.
This challenge combines CTEs, LAG and julianday, GROUP BY, COALESCE, RANK and a window aggregate. As before, end your script with SELECT * FROM waits; (or any other CTE) to check a step on its own.
Step 1: a CTE waits with, for every badge, its trainer_id and wait_days: the number of days since that trainer's previous badge (julianday and LAG partitioned by trainer, NULL for a trainer's first badge).
Step 2: a CTE badge_stats with, per trainer_id, badges (how many) and longest_wait (the largest wait_days, cast to an integer).
Step 3: a CTE wins_per_trainer (battles won per winner_id), then a CTE scores with every trainer: name, badges and wins (both 0 when none), points = badges × 3 + wins × 2, and longest_wait.
Step 4: the final query: points_rank (RANK() by points, highest first), name, badges, wins, points, gap_to_leader (the highest points minus this trainer's, computed with a window) and longest_wait (NULL for a trainer without badges), sorted by points_rank then name.
Expected result (the last SELECT):
points_rank | name | badges | wins | points | gap_to_leader | longest_wait
1 | Morgan | 5 | 3 | 21 | 0 | 23
2 | Alex | 3 | 1 | 11 | 10 | 25
3 | Taylor | 2 | 2 | 10 | 11 | 11
4 | Jordan | 0 | 0 | 0 | 21 | NULL-- Step 1: waits
-- Step 2: badge_stats
-- Step 3: wins_per_trainer and scores
-- Step 4: the leaderboard