Building Analytics Queries for Reporting Dashboards

1. Why reporting dashboards need more than one window function

A single AVG() OVER() or RANK() OVER() solves exactly one sub-problem. Real analytics queries for reporting dashboards, on the other hand, have to answer several questions at once: how many users of a given cohort are still active after three months? How has revenue changed compared to the previous period? These questions can rarely be answered with an isolated window function, they require a pipeline of several processing steps that logically build on each other.

This is exactly where Common Table Expressions, CTEs for short, come in. A CTE, introduced with WITH name AS (...), encapsulates an intermediate step of the calculation in a named, reusable block. A typical analytics query for a dashboard consists of three to five such CTEs that build on each other: filter raw data, assign cohorts, aggregate, apply window functions for comparison values, and finally format the finished result for the application.

This article uses two of the most common dashboard metrics, cohort retention and period-over-period growth, to show how CTEs and window functions combine into complete, maintainable analytics queries, and how both patterns can ultimately be brought together into a single dashboard query.

2. CTEs as the structural foundation for analytics queries

A CTE is functionally similar to a subquery, but considerably more readable, because it is named at the start of the query and can then be referenced like a temporary table. Instead of deeply nested subqueries that must be read from the inside out, a chain of CTEs reads from top to bottom like a step-by-step guide: first the base data, then the aggregation, then the comparison calculation. That is a significant readability win, especially for complex analytics queries with several processing stages.

Multiple CTEs can be defined in a single WITH clause separated by commas, and every later CTE can access all the previous ones. PostgreSQL, MySQL from version 8, SQL Server and Oracle support this syntax identically. Important for performance: in PostgreSQL before version 12, CTEs were treated as an optimization fence by default, meaning the query planner could not inline them into the surrounding query. From PostgreSQL 12 onward, the planner automatically decides whether a CTE is inlined, which makes CTEs attractive for performance-critical analytics queries too.

3. Cohort retention: cohort assignment as the first step

A cohort retention analysis answers the question of how many users who took their first action in the same time period are still active in the following months. The first step in such an analytics query is assigning every user to their cohort, defined by the month of their first activity. MIN(activity_date) grouped by user, combined with truncation to the start of the month, is well suited for this.

This cohort assignment gets isolated into its own CTE, because it is needed multiple times in the following steps: once to determine the cohort size, once to link every later activity back to the original cohort. This separation not only makes the overall query more readable, it also prevents the same subquery from being written multiple times independently and potentially becoming inconsistent.

-- Step 1: assign every user to their signup cohort month
WITH user_cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('month', MIN(activity_date)) AS cohort_month
    FROM user_activity
    GROUP BY user_id
),
-- Step 2: attach the cohort month to every activity event
cohort_activity AS (
    SELECT
        uc.user_id,
        uc.cohort_month,
        DATE_TRUNC('month', ua.activity_date) AS activity_month
    FROM user_activity ua
    JOIN user_cohorts uc ON uc.user_id = ua.user_id
)
SELECT * FROM cohort_activity ORDER BY cohort_month, user_id;

4. The retention matrix with aggregation and window functions

After cohort assignment comes the step that actually computes the retention metric: the month offset between the cohort month and the activity month, usually called month_number, where 0 is the cohort month itself, 1 the first following month, and so on. A GROUP BY cohort_month, month_number aggregation then counts the active users per cohort and month offset.

To turn that into a retention rate instead of an absolute user count, the number of active users in month n is divided by the original cohort size in month 0. This is exactly where a window function comes in: FIRST_VALUE(active_users) OVER (PARTITION BY cohort_month ORDER BY month_number) returns the cohort size from month 0 for every row of the same cohort, without needing an extra self-join for the cohort size.

-- Step 3: count active users per cohort and month offset
WITH user_cohorts AS (
    SELECT user_id, DATE_TRUNC('month', MIN(activity_date)) AS cohort_month
    FROM user_activity GROUP BY user_id
),
cohort_activity AS (
    SELECT uc.user_id, uc.cohort_month,
           DATE_TRUNC('month', ua.activity_date) AS activity_month
    FROM user_activity ua JOIN user_cohorts uc ON uc.user_id = ua.user_id
),
monthly_counts AS (
    SELECT
        cohort_month,
        EXTRACT(YEAR FROM AGE(activity_month, cohort_month)) * 12
            + EXTRACT(MONTH FROM AGE(activity_month, cohort_month)) AS month_number,
        COUNT(DISTINCT user_id) AS active_users
    FROM cohort_activity
    GROUP BY cohort_month, activity_month
)
-- Step 4: divide by cohort size at month 0 using a window function
SELECT
    cohort_month,
    month_number,
    active_users,
    FIRST_VALUE(active_users) OVER (
        PARTITION BY cohort_month ORDER BY month_number
    ) AS cohort_size,
    ROUND(
        active_users::numeric
        / FIRST_VALUE(active_users) OVER (PARTITION BY cohort_month ORDER BY month_number),
        3
    ) AS retention_rate
FROM monthly_counts
ORDER BY cohort_month, month_number;

5. Period-over-period growth: LAG() as the foundation

The second common dashboard metric is period-over-period growth, the percentage change of a metric compared to the previous period. The window function LAG(column, n) is the direct tool for this: it returns the value of the same column from the n-th preceding row, sorted by the time column. With n = 1, the default, LAG() returns the value of the immediately preceding period.

The growth rate itself is a simple percentage calculation based on the current value and the previous value fetched with LAG(): (current_value - previous_value) / previous_value. It matters to guard against division by zero, for example with NULLIF(previous_value, 0), so the query doesn't abort with a database error when a period had a baseline value of zero.

-- Month-over-month revenue growth with LAG()
SELECT
    revenue_month,
    monthly_revenue,
    LAG(monthly_revenue) OVER (ORDER BY revenue_month) AS previous_month_revenue,
    ROUND(
        (monthly_revenue - LAG(monthly_revenue) OVER (ORDER BY revenue_month))
        / NULLIF(LAG(monthly_revenue) OVER (ORDER BY revenue_month), 0)::numeric,
        4
    ) AS mom_growth_rate
FROM monthly_revenue_summary
ORDER BY revenue_month;

-- revenue_month | monthly_revenue | previous_month_revenue | mom_growth_rate
-- 2026-04-01    | 42000.00        | NULL                   | NULL
-- 2026-05-01    | 45500.00        | 42000.00                | 0.0833
-- 2026-06-01    | 43200.00        | 45500.00                | -0.0505

6. Growth rates across multiple periods and year comparisons

For a year-over-year comparison instead of a month-over-month one, a simple offset adjustment is enough: LAG(monthly_revenue, 12) returns the value of the same row one year earlier, provided the time series has no gaps. This flexibility makes LAG() a universal tool for year-over-year comparisons, without needing a self-join with a shifted date condition.

A dashboard often shows several growth rates at once: month-over-month and year-over-year side by side, to make both short-term and seasonal trends visible. Both calculations can be combined in the same SELECT clause with different LAG() offsets, without the query becoming structurally more complex for it.

-- Combine month-over-month and year-over-year growth in one query
SELECT
    revenue_month,
    monthly_revenue,
    ROUND(
        (monthly_revenue - LAG(monthly_revenue, 1) OVER (ORDER BY revenue_month))
        / NULLIF(LAG(monthly_revenue, 1) OVER (ORDER BY revenue_month), 0)::numeric,
        4
    ) AS mom_growth_rate,
    ROUND(
        (monthly_revenue - LAG(monthly_revenue, 12) OVER (ORDER BY revenue_month))
        / NULLIF(LAG(monthly_revenue, 12) OVER (ORDER BY revenue_month), 0)::numeric,
        4
    ) AS yoy_growth_rate
FROM monthly_revenue_summary
ORDER BY revenue_month;

7. Combining both patterns: a complete dashboard query

In practice, a reporting dashboard rarely lives off a single metric. A realistic dashboard query often combines cohort retention and growth rates in separate CTEs within the same query, or as separate views that the dashboard tool queries as needed. The structural advantage of the CTE chain remains: every metric is produced in its own, clearly named processing step that can be tested independently and adjusted when needed.

A proven pattern is to build a dedicated view or materialized view per dashboard metric based on a CTE chain, instead of building a single monolithic query with all metrics at once. The BI tool or frontend then specifically queries the view it needs for a given visualization, which improves both maintainability and cacheability of individual metrics.

-- Combined dashboard view: retention and growth as separate CTEs
CREATE MATERIALIZED VIEW dashboard_summary AS
WITH cohort_retention AS (
    SELECT cohort_month, month_number, retention_rate
    FROM retention_by_cohort
),
revenue_growth AS (
    SELECT
        revenue_month,
        monthly_revenue,
        ROUND(
            (monthly_revenue - LAG(monthly_revenue) OVER (ORDER BY revenue_month))
            / NULLIF(LAG(monthly_revenue) OVER (ORDER BY revenue_month), 0)::numeric,
            4
        ) AS mom_growth_rate
    FROM monthly_revenue_summary
)
SELECT
    r.revenue_month,
    r.monthly_revenue,
    r.mom_growth_rate,
    c.cohort_month,
    c.month_number,
    c.retention_rate
FROM revenue_growth r
LEFT JOIN cohort_retention c ON c.cohort_month = DATE_TRUNC('month', r.revenue_month);
-- Two independent metrics, joined only for a single dashboard export

8. Performance and materialized views for large data volumes

Cohort retention queries are computationally heavy, because they typically aggregate over the entire activity history of all users, often with a COUNT(DISTINCT ...), which is not trivial to optimize. With millions of activity rows growing daily, such an analytics query quickly becomes too slow to run live on every dashboard load. A materialized view, recomputed only once per night or hour, decouples the expensive calculation from the fast read access of the dashboard.

For period-over-period growth, the computational load is usually lower, because the underlying time series is typically already pre-aggregated, for example as monthly revenue totals instead of individual transactions. Still, an index on the time column is worthwhile here too, so the sort order LAG() needs can be read efficiently from the index instead of being resorted on every dashboard query.

9. Maintainability: CTEs vs. nested subqueries

The main maintainability advantage of CTEs over nested subqueries shows up especially with analytics queries that grow over time. A deeply nested subquery structure with four or five levels quickly becomes unreadable, because every change to an inner step means counting through several levels of parentheses to find the right spot. A chain of named CTEs, on the other hand, lets you understand, test, and, if needed, debug each step in isolation with a simple SELECT * FROM cte_name.

Another practical advantage: multiple window functions that use the same OVER() definition can be named and reused with a WINDOW clause, which prevents typos in repeated PARTITION BY and ORDER BY definitions. Combined with clearly named CTEs, this produces an analytics query structure that remains understandable even months later, without the original author having to explain it again.

10. Summary

Real analytics queries for reporting dashboards rarely come from a single window function, they come from a chain of CTEs that transform raw data step by step into the desired metric. Cohort retention tables combine a cohort assignment, an aggregation by month offset, and FIRST_VALUE() to determine the cohort size. Period-over-period growth builds on LAG() with different offsets for month-over-month and year-over-year comparisons.

For large data volumes, materialized views decouple the expensive calculation from fast dashboard read access, while well-named CTEs keep even complex, multi-stage analytics queries maintainable. Once you have implemented these two patterns, cohort retention and period-over-period growth, cleanly once, you can reuse them as a template for nearly any further dashboard metric in pure SQL.

11. FAQ: Analytics Queries for Reporting Dashboards