Sql - Capstone: Part 3
Code Life
By Pierre Marsaa
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.
Loading data...
Loading data...
Loading data...
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):
Loading editor...
↗️
Loading editor...
↶
↷
💡 Show Solution
Finish