Phase Project: Analytical Dashboard Queries — SQL Course

What you'll learn

Build a reusable analytical query layer with trends, rankings, cohorts, subtotals, JSON responses, and documented metric definitions. The lab uses PostgreSQL while identifying the semantics that transfer to other relational systems.

By the end of this lesson, you'll be able to:

Apply Metric contracts to a realistic data question
Apply CTE pipelines to a realistic data question
Apply Window analytics to a realistic data question
Apply Advanced grouping to a realistic data question

Core mental model

SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.

Concept	What it means	Decision rule
Metric contract	A versioned definition of a dashboard number	Record grain, filters, time zone, late data, and refresh cadence
Cohort	Entities grouped by a shared start period	Separate cohort assignment from later activity measurement
Freshness	How old analytical output may be	Choose live views or materialization from an explicit latency budget

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the dashboard analytics layer question and the exact grain of the expected result.
Inspect table definitions, keys, constraints, representative values, and row counts.
Write the smallest correct query with explicit columns, aliases, and predicates.
Test missing, duplicate, boundary, and NULL cases before trusting the result.
Inspect the execution plan or affected rows when cost or data change matters.
Save the query with its assumptions, parameters, verification, and recovery notes.

Make results explainable

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Publish metric metadata beside SQL

The catalog gives each query an owner, grain, and freshness expectation rather than leaving meaning inside a chart.

metric_catalog.sql

CREATE TABLE reporting.metric_catalog (
  metric_key text PRIMARY KEY,
  description text NOT NULL,
  grain text NOT NULL,
  owner_role text NOT NULL,
  freshness_interval interval NOT NULL,
  query_view regclass NOT NULL
);

Production practice

Contract

Define the expected row grain, inputs, output columns, invariants, and failure or empty-result behavior before writing SQL.

Verification

Use representative fixtures and independent row-count, uniqueness, NULL, and boundary checks; compare plans when cost matters.

Operations

Save reviewed SQL with explicit schema names where appropriate, bounded scope, least privilege, observability, and a recovery path for changes.

Common failure mode

A dashboard can show internally consistent but mutually incompatible metrics when each query uses a different time zone, status rule, or grain.

Independent workshop

Deliver a documented dashboard query layer with trends, top-N, cohorts, subtotals, and drill-down JSON.

Your finished workshop must include:

Metric contracts
CTE pipelines
Window analytics
Advanced grouping
Views
Verification notes and edge-case evidence

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

Metric contract: Record grain, filters, time zone, late data, and refresh cadence
Cohort: Separate cohort assignment from later activity measurement
Freshness: Choose live views or materialization from an explicit latency budget

Quick check

1. Which rule best applies to Metric contract?

2. Which rule best applies to Cohort?

3. Which rule best applies to Freshness?

Next: Index Fundamentals & B-Tree Design