Lesson 30: Real-Time Analytics Query (Capstone)

This capstone combines joins, aggregation, window functions, and CTEs into one realistic analytics query — the kind a real dashboard would run.

Real-time example

Build a 'top 5 customers this month, with their rank and how it compares to last month' report in a single query.

WITH monthly AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', created_at) AS month,
        SUM(amount) AS total
    FROM orders
    GROUP BY customer_id, DATE_TRUNC('month', created_at)
),
ranked AS (
    SELECT *,
        RANK() OVER (PARTITION BY month ORDER BY total DESC) AS rnk,
        LAG(total) OVER (PARTITION BY customer_id ORDER BY month) AS prev_month_total
    FROM monthly
)
SELECT c.name, r.month, r.total, r.rnk, r.prev_month_total
FROM ranked r
JOIN customers c ON c.id = r.customer_id
WHERE r.month = DATE_TRUNC('month', now()) AND r.rnk <= 5
ORDER BY r.rnk;

What's happening

Two CTEs build the query in readable layers: monthly totals first, then ranking and month-over-month comparison — each step is independently understandable.

This is the shape of real dashboard SQL — layered, named, and readable, not one giant nested query.