Advanced Query Practice: Analytics Dashboard

What You'll Build

You will build the query layer for a cricket-league analytics dashboard, combining everything from this module: joins across teams, players, matches, and appearances; aggregations and window functions for rankings and running totals; CTEs to structure multi-step computations; and a materialized view to serve the expensive standings instantly. The result is a set of queries and one materialized view that a dashboard could read directly.

The aim is to practice analytical SQL end to end: turning raw event rows into the leaderboards, trends, and standings a dashboard displays, and then precomputing the costliest of those into a refreshable materialized view. By the end you will have produced top-scorer leaderboards, per-match rankings, form trends, and a fast standings table backed by a concurrent refresh.

Analogy🏏Cricket

🏏 Think of it like cricket: Just as setting up a new league means first writing the registration rules and eligibility checks, then entering the teams and players, then running the fixtures, you will first define the constrained schema, then load data, then query it. The insight is that the rules come before the play — get the eligibility and scoring regulations right first, and the matches that follow stay orderly because the framework already enforces fair play. Run the sequence backwards to see why the order is non-negotiable: admit teams first and write the eligibility rules afterwards, and you discover mid-season that three registered players were never eligible — now every result they touched is in dispute and unwinding it means re-examining a season of records. That is retrofitting constraints onto a table already full of violating rows: the ALTER fails until you hand-clean the data it was supposed to prevent. Founding the league in the right order also changes how boldly you can operate later: fixtures can be scheduled aggressively because the framework guarantees every listed player is legal, just as queries and application code can stay simple when the schema has already promised the data is sound.

Prerequisites

Completion of lessons 06–09, or equivalent comfort with joins, aggregation, window functions, CTEs, and views.
The league schema from lesson 05 (team, player, match, appearance) with a reasonable amount of sample data.
A running PostgreSQL 14+ instance and the psql client.
Familiarity with GROUP BY/HAVING, OVER/PARTITION BY, WITH (CTEs), and CREATE MATERIALIZED VIEW.
Understanding of concurrent refresh and the unique-index requirement.

Setup & Project Structure

Work in the league database from the previous exercise, ensuring it has enough data to make aggregates interesting — several teams, a couple of dozen players, and many appearances across multiple matches. If needed, generate sample appearances with a quick INSERT ... SELECT using generate_series so leaderboards and trends have substance.

Analogy🏏Cricket

🏏 Think of it like cricket: before a tournament you set up a dedicated ground and a clean scoring system rather than borrowing a crowded club pitch. Just as you create a fresh database so your exercise work is isolated, a groundsman prepares a separate net facility so practice never disturbs the main square. Just as you will build four related tables — team, player, match, and appearance — a league secretary maintains four linked registers: the clubs, the registered players, the fixtures, and the record of who actually took the field in each fixture. Just as an appearance ties a specific player to a specific match, that turnout register is the join that connects players to the games they played. And just as keeping everything in one schema keeps the practice tidy, running one competition under one governing body keeps all records consistent. The payoff: a clean, isolated setup lets you apply type and constraint choices deliberately and see exactly how they behave.

Plan the dashboard's panels before writing SQL: a top-scorers leaderboard, a per-match batting ranking, a player form trend (runs vs previous match), and a team standings table. Each maps to a query technique from this module, and the standings — the most expensive — will become the materialized view.

bash

-- Seed extra appearances so analytics have substance (adjust ids to your data)
INSERT INTO match (format, starts_at)
SELECT 'T20', now() - (g || ' days')::interval
FROM generate_series(1, 10) g;

INSERT INTO appearance (match_id, player_id, runs)
SELECT m.id, p.id, (random() * 80)::int
FROM match m CROSS JOIN player p
ON CONFLICT (match_id, player_id) DO NOTHING;   -- safe if some pairs exist

Step 1 — Leaderboards with Aggregation and Ranking

Build the top-scorers leaderboard: sum each player's runs across all appearances, then rank them. Use a CTE to compute per-player totals and a window RANK over that result so ties are handled, returning the top players with their rank. This combines GROUP BY aggregation with a window ranking in a readable two-step pipeline.

Then build a per-match batting ranking that, within each match, ranks players by runs using PARTITION BY. This keeps every appearance row while annotating it with its position in that match — exactly the window-function pattern for top-N-per-group, which you can filter in an outer query to show only the top three per match.

Analogy🏏Cricket

🏏 Think of it like cricket: Just as a tournament publishes both an overall leading run-scorers table and a per-match top performers list, you produce a global leaderboard and a per-match ranking from the same data. The insight is that the same runs, ranked globally versus partitioned by match, answer two different questions — overall form versus standout single performances — and window partitions are how you switch between them. The switch between the two is a single clause: rank the same runs with no partition and you get the tournament-wide leaderboard; add PARTITION BY match and the ranking restarts fresh inside every fixture, crowning a top performer per game. Same rows, same ordering logic, different frame of comparison. The per-match list then raises the classic subtlety the global one hides: two batters on 74 in the same match. RANK gives both second place and skips third; DENSE_RANK gives both second and continues at third; ROW_NUMBER forces an arbitrary tiebreak — and 'top 3 per match' returns different players depending on which you chose. Getting the partition right picks the question; getting the tie-handling right picks the answer.

sql

-- Global top-scorer leaderboard (aggregate + window rank)
WITH totals AS (
    SELECT player_id, SUM(runs) AS runs FROM appearance GROUP BY player_id)
SELECT p.name, t.runs, RANK() OVER (ORDER BY t.runs DESC) AS rank
FROM totals t JOIN player p ON p.id = t.player_id
ORDER BY rank LIMIT 10;

-- Top 3 batters per match (partitioned ranking, filtered in outer query)
WITH ranked AS (
    SELECT a.match_id, p.name, a.runs,
           RANK() OVER (PARTITION BY a.match_id ORDER BY a.runs DESC) AS r
    FROM appearance a JOIN player p ON p.id = a.player_id)
SELECT * FROM ranked WHERE r <= 3 ORDER BY match_id, r;

Step 2 — Trends with Window Functions and CTEs

Build a player form trend: for each player, show each match's runs alongside the change from their previous match using LAG, and a running cumulative total using a windowed SUM. Partition by player and order by match time so each player's sequence is computed independently and in order.

This panel demonstrates the comparative power of window functions: LAG gives period-over-period change without a self-join, and the running SUM produces a cumulative line for a chart. Both keep every row, so the dashboard can plot the full sequence per player rather than a single summary number.

Analogy🏏Cricket

🏏 Think of it like cricket: Just as a player's form graph plots each innings, the change from the last, and their cumulative season runs, your trend query computes per-match runs, the delta via LAG, and a running total. The insight is that form is inherently sequential — it is about how each innings compares to the last and how they accumulate — which is precisely what ordered window functions express directly from the data. Each column of the form graph maps to one window construct, and each has its edge case. The delta needs LAG ordered by match date — and the first innings of the season has no predecessor, so the delta is NULL unless you supply LAG's default; a form graph that silently drops the season opener is the unhandled-NULL bug made visible. The running total needs SUM OVER with ORDER BY, and it is the frame that makes it cumulative — rows unbounded-preceding to current — rather than a partition-wide constant repeated on every row, the mistake that flattens every form curve. And all of it is PARTITION BY player: forget that, and one batter's late-season slump bleeds into the next player's running total — a corrupt graph that still renders beautifully.

sql

SELECT p.name, a.match_id, m.starts_at, a.runs,
       a.runs - LAG(a.runs) OVER w           AS change_vs_prev,
       SUM(a.runs)          OVER w           AS season_running_total
FROM appearance a
JOIN player p ON p.id = a.player_id
JOIN match  m ON m.id = a.match_id
WINDOW w AS (PARTITION BY a.player_id ORDER BY m.starts_at
             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
ORDER BY p.name, m.starts_at;

Step 3 — Materialized Standings with Concurrent Refresh

Build the team standings as a materialized view: per team, the number of matches, total runs, and average runs per appearance — an expensive multi-table aggregation the dashboard reads constantly. Create a unique index on the team id so the view can be refreshed concurrently without blocking the dashboard's readers.

Then demonstrate the refresh workflow: query the materialized view (fast), simulate new data, and run REFRESH MATERIALIZED VIEW CONCURRENTLY to update it without a stall. In production this refresh would be scheduled with pg_cron at an interval matching how fresh the standings need to be.

Analogy🏏Cricket

🏏 Think of it like cricket: Just as the official standings table is recomputed after each round and published for everyone to read instantly, your materialized standings precompute the heavy aggregation and serve it fast, refreshed on a schedule. The insight is that the most-read, most-expensive panel benefits most from precomputation — the standings are glanced at constantly but only change a few times a day, the ideal case for a refreshable snapshot. The choice hinges on the read-to-write ratio: standings are glanced at thousands of times per change, so paying the aggregation once per round and serving a stored copy thousands of times is the obvious trade — the inverse of the live win-predictor, which changes every ball and must stay a plain view. Operating the snapshot is the real skill: refresh CONCURRENTLY so the dashboard never blanks mid-update (which requires the unique index on the view), schedule the refresh to follow the events that change standings rather than a blind timer, and put a 'last updated' stamp on the panel — because the one unforgivable dashboard sin is a stale figure presented as live. The panel's speed comes from precomputation; its trustworthiness comes from the refresh discipline around it.

sql

CREATE MATERIALIZED VIEW dashboard_standings AS
SELECT t.id AS team_id, t.name,
       COUNT(DISTINCT a.match_id)       AS matches_played,
       SUM(a.runs)                      AS total_runs,
       ROUND(AVG(a.runs), 1)            AS avg_runs_per_innings
FROM team t
JOIN player p     ON p.team_id = t.id
JOIN appearance a ON a.player_id = p.id
GROUP BY t.id, t.name;

CREATE UNIQUE INDEX ON dashboard_standings (team_id);   -- enables concurrent refresh

SELECT * FROM dashboard_standings ORDER BY total_runs DESC;   -- fast read
REFRESH MATERIALIZED VIEW CONCURRENTLY dashboard_standings;   -- non-blocking update

Step 4 — Testing & Verification

Confirm each dashboard panel produces correct, sensible results and that the materialized view refreshes without blocking. Cross-check the materialized standings against a live equivalent query to ensure the precomputation matches reality, and verify the leaderboard ranks and running totals are internally consistent.

Analogy🏏Cricket

🏏 Think of it like cricket: before a match counts, you check the ground, the kit, and the Laws all behave as intended. Just as you inspect the table definitions with \d, the ground authority reviews the official team sheets and pitch report to confirm everything is set up as designed. Just as you verify that constraint-violating inserts fail, an umpire confirms that an illegal action — a bowler exceeding their over limit, an unregistered player trying to bat — is correctly rejected rather than slipping through. Just as you check that queries return correct, deterministically ordered results, the scorers confirm the batting order and totals come out the same every time, not shuffled at random. And just as you run the constraint and referential checks to prove the database enforces what you intended, the officials run through the rulebook to prove the game will hold to its Laws. The payoff: verifying the enforcement, not just the happy path, is what proves the schema is genuinely doing its job.

sql

-- Materialized standings should match a live aggregation (same numbers)
SELECT team_id, total_runs FROM dashboard_standings ORDER BY team_id;
SELECT p.team_id, SUM(a.runs)
FROM player p JOIN appearance a ON a.player_id = p.id
GROUP BY p.team_id ORDER BY p.team_id;     -- compare to the row above

-- Running total at a player's last match should equal their grand total
WITH t AS (
  SELECT player_id, SUM(runs) AS grand FROM appearance GROUP BY player_id)
SELECT * FROM t ORDER BY grand DESC LIMIT 5;

-- Confirm the unique index exists (required for concurrent refresh)
SELECT indexname FROM pg_indexes WHERE tablename = 'dashboard_standings';

Warning: A materialized view serves stale data until refreshed, so a dashboard reading it can silently show outdated standings if no refresh is scheduled. Decide the acceptable staleness, schedule a concurrent refresh to match, and never point a panel that must be transactionally current at a materialized view — use a live query or regular view there instead.

Extension Challenge: Add a 'last 5 matches form' panel using a window frame of ROWS BETWEEN 4 PRECEDING AND CURRENT ROW to compute a rolling average, and a percentile ranking of players using PERCENT_RANK or NTILE(4) to bucket them into quartiles. Then schedule the materialized-view refresh with pg_cron (previewed now, covered fully in a later lesson) and measure the read latency difference versus the live aggregation.

Combine CTEs, aggregation, and window ranking to build leaderboards and top-N-per-group panels.
Use LAG for period-over-period change and windowed SUM for running totals — sequential, row-preserving analytics.
Precompute expensive, read-heavy aggregations (standings) into a materialized view for instant reads.
Create a unique index on the materialized view to enable non-blocking REFRESH ... CONCURRENTLY.
Validate a materialized view against a live equivalent query to ensure the snapshot is correct.
Match refresh frequency to required freshness; never use a stale snapshot where current data is required.