SQL Academy · Lesson

Real-World Reporting Patterns

Implement classic dashboards: retention curves, top-N per category, sessionisation — all with window functions.

Real-World Reporting Patterns is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Pattern: Top-N Per Group

Top 3 orders per user:

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) AS rn
  FROM orders
)
SELECT * FROM ranked WHERE rn <= 3;

Pattern: Running Totals

Cumulative revenue over time:

SELECT day, revenue,
       SUM(revenue) OVER (ORDER BY day) AS running_total
FROM daily_revenue;

Pattern: First Occurrence

First time each user did each action:

SELECT user_id, action, MIN(ts) AS first_at
FROM events
GROUP BY user_id, action;

-- Or with window functions for full row:
WITH firsts AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id, action ORDER BY ts) AS rn
  FROM events
)
SELECT * FROM firsts WHERE rn = 1;

Pattern: Cohort Retention

Users grouped by signup week, retention by week N:

WITH cohorts AS (
  SELECT id AS user_id, date_trunc('week', created_at) AS cohort_week
  FROM users
),
activities AS (
  SELECT user_id, date_trunc('week', ts) AS active_week FROM events
)
SELECT c.cohort_week,
       (a.active_week - c.cohort_week) / 7 AS week_offset,
       COUNT(DISTINCT a.user_id) AS active
FROM cohorts c
JOIN activities a USING (user_id)
WHERE a.active_week >= c.cohort_week
GROUP BY c.cohort_week, week_offset
ORDER BY c.cohort_week, week_offset;

Pattern: Funnel Analysis

How many users reach each step:

SELECT
  COUNT(*)                                  AS signed_up,
  COUNT(*) FILTER (WHERE first_login_at IS NOT NULL) AS logged_in,
  COUNT(*) FILTER (WHERE first_purchase_at IS NOT NULL) AS purchased
FROM users;

Pattern: Sessionisation

Group events into sessions when gap > 30 min:

WITH gaps AS (
  SELECT user_id, ts,
    CASE
      WHEN ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts)
           > INTERVAL '30 min'
      THEN 1 ELSE 0
    END AS new_session
  FROM events
)
SELECT user_id, ts,
       SUM(new_session) OVER (PARTITION BY user_id ORDER BY ts) AS session_id
FROM gaps;

Pattern: Period-over-Period

Compare current vs previous month:

SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_month,
       revenue - LAG(revenue) OVER (ORDER BY month) AS delta,
       (revenue::FLOAT / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) - 1) * 100 AS pct_change
FROM monthly_revenue
ORDER BY month;

Pattern: Pivoted Output

Wide-format with FILTER:

SELECT user_id,
       SUM(amount) FILTER (WHERE month = '2024-01') AS jan,
       SUM(amount) FILTER (WHERE month = '2024-02') AS feb,
       SUM(amount) FILTER (WHERE month = '2024-03') AS mar
FROM monthly_spend
GROUP BY user_id;

Pattern: Active Users Today

DAU / WAU / MAU:

SELECT
  COUNT(DISTINCT user_id) FILTER (WHERE ts >= NOW() - INTERVAL '1 day')  AS dau,
  COUNT(DISTINCT user_id) FILTER (WHERE ts >= NOW() - INTERVAL '7 days') AS wau,
  COUNT(DISTINCT user_id) FILTER (WHERE ts >= NOW() - INTERVAL '30 days') AS mau
FROM events;

Pattern: Gap Filling

Days with no events should show 0, not be missing:

SELECT day, COALESCE(COUNT(e.id), 0) AS events
FROM generate_series(CURRENT_DATE - 30, CURRENT_DATE, INTERVAL '1 day') AS day
LEFT JOIN events e ON date_trunc('day', e.ts) = day
GROUP BY day
ORDER BY day;

Combining Window Functions for Insight

Multiple windowed columns in one query — readable, fast:

SELECT day, revenue,
       LAG(revenue) OVER w               AS prev,
       AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d,
       SUM(revenue) OVER (ORDER BY day)  AS running_total
FROM daily_revenue
WINDOW w AS (ORDER BY day)
ORDER BY day;

Recap

Most reports are a handful of patterns: top-N, running totals, cohorts, funnels, sessionisation, period-over-period, pivots, gap-filling. Master those and you can build any dashboard SQL needs.

Quick Check

You're building a "top 5 products per category" report. Which idiomatic SQL pattern?

Frequently asked questions

Is the “Real-World Reporting Patterns” lesson free?

Yes — the full text of “Real-World Reporting Patterns” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Real-World Reporting Patterns”?

Implement classic dashboards: retention curves, top-N per category, sessionisation — all with window functions. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Real-World Reporting Patterns” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy