Skip to content
Tutorials Center
My learning

Topics

Lesson 71 of 75 ยท Projects

Project: Analytics and Reporting Queries

Write reporting queries in MySQL: month-over-month growth with window functions, rollup subtotals, cohort retention, summary tables and caching of aggregates.

Sooner or later every application gets a dashboard: revenue by month, growth compared with last month, the best country, how many new users came back. The data is already in MySQL, but analytics queries behave differently from the page queries you wrote for the application. They read a lot of rows, group and rank them, and run on a schedule or when a manager opens a report.

This project is a toolbox of reporting techniques: window functions for growth and ranking, rollups for subtotals, a cohort retention table, a summary table that makes dashboards fast, and caching so the same expensive aggregate is not computed on every visit. It builds on window functions, GROUP BY and aggregates, CTEs and upserts.

What runs live here. The queries run in the SQLite stand-in, which has window functions, CTEs and upserts. SQLite has no WITH ROLLUP, so the rollup is emulated with UNION ALL live, and the real MySQL syntax is shown read-only. Dates are stored as text such as 2026-03-02, the way SQLite stores them.

A reporting toolbox

Window functions Growth, running totals, rankings.
Rollups Subtotals and a grand total.
Cohorts Do people come back?
Summary tables Pre-computed numbers for dashboards.
1 / 2

The sample data

All the revenue queries use a tiny orders table. Real tables have millions of rows, but the SQL is identical. Amounts are whole currency units to keep the numbers easy to check by hand.

Growth and ranking with window functions

Management asks two classic questions: "How much did we sell each month, and how did it change?" and "Which country led each month?" A monthly aggregate in a CTE feeds LAG for the change and SUM() OVER for the running total. A second query ranks countries inside each month and keeps rank 1.

SQL Month-over-month growth and top country

Press Run (or Ctrl + Enter) to see the result here.

Monthly revenue is 490, 550 and 1,000. Growth is NULL for the first month (nothing to compare with), 12.2 percent for February and 81.8 percent for March, and the running total ends at 2,040. The second query returns the US as the leader in all three months (250, 350 and 620).

Three habits make these queries reliable. Use RANK rather than ROW_NUMBER when ties should all be shown. Aggregate first and window second: the window function runs on the grouped result, which is why RANK() OVER (ORDER BY SUM(amount) DESC) is legal after a GROUP BY. And be careful with percentages: divide by the previous period, and expect NULL for the first period or when the previous value is 0 (MySQL returns NULL with a warning when a SELECT divides by zero, while many other databases raise an error, so write NULLIF(previous, 0) to make the intent explicit).

In MySQL, replace substr(order_date, 1, 7) with DATE_FORMAT(order_date, '%Y-%m'). If the table is large, an expression like that cannot use an index on order_date; filter first with a range (order_date >= '2026-01-01' AND order_date < '2026-04-01') and group afterwards (query optimization).

Rollups: subtotals and a grand total

A report often wants each country's monthly numbers, a subtotal per country and a grand total on one page. MySQL has a dedicated clause, WITH ROLLUP, which appends the subtotal rows with NULL in the grouped column. This block is read-only:

SQL
SELECT country,
       DATE_FORMAT(order_date, '%Y-%m') AS month,
       SUM(amount) AS revenue,
       GROUPING(country) AS is_total_country,
       GROUPING(DATE_FORMAT(order_date, '%Y-%m')) AS is_total_month
FROM orders
GROUP BY country, DATE_FORMAT(order_date, '%Y-%m') WITH ROLLUP;

GROUPING() returns 1 for the rows that the rollup added, so you can tell a subtotal's NULL from a genuine NULL in the data and replace it with a label such as "All". Without WITH ROLLUP you can get the same result by stacking three aggregates with UNION ALL, which is also how you do it in SQLite:

SQL Subtotals with UNION ALL (a rollup stand-in)

Press Run (or Ctrl + Enter) to see the result here.

The result lists DE with its monthly rows (240, 120, 180) and an ALL subtotal of 540, then FR (80, 200; subtotal 280), then US (250, 350, 620; subtotal 1,220), and finally the grand total 2,040 on the last row. Notice that the subtotals are computed from the already grouped CTE, not from the raw table again, which is both cheaper and guarantees that the parts add up to the whole.

Cohort retention

Revenue tells you how much. Retention tells you whether people stay. A cohort is a group of users who signed up in the same month, and retention is the share of each cohort that is still active 0, 1, 2 months later. It is the most useful chart in a subscription business, and it is a pure SQL problem: join users to their activity, compute the month offset between sign-up and activity, and count distinct users per offset.

Months stored as YYYY-MM text are converted to a number (year * 12 + month) so that subtracting two of them gives the offset.

SQL Monthly cohort retention table

Press Run (or Ctrl + Enter) to see the result here.

The January cohort has 4 users: all 4 were active in month 0, 2 in month 1 (50 percent) and 2 in month 2 (50 percent). The February cohort has 3 users, with 1 returning in month 1 (33.3 percent). The March cohort has only month 0, so its later cells are NULL.

That last point needs care. A NULL here means "no activity rows exist for that offset". For a cohort that is simply too young, it is the right answer ("not observed yet"). For an old cohort where nobody came back, it would hide a real 0 percent. In a production report, decide the last month of data and show a dash only for offsets beyond it, and 0 for observed offsets with no activity.

Building the pivot columns with SUM(CASE WHEN ...) is the portable way to turn rows into columns in MySQL as well, because MySQL has no PIVOT keyword. The CAST and substr calls become PERIOD_DIFF(202603, 202601) or TIMESTAMPDIFF(MONTH, ...) in MySQL. For real data, give activity an index on (user_id, active_month) and make it unique, so a user counts once per month.

Summary tables

The queries above scan the whole orders table. That is fine for 10 rows and for a nightly job, but not for a dashboard opened a hundred times an hour over 200 million rows. A summary table (also called a rollup or aggregate table) stores the answer, usually one row per day, and the dashboard reads a few hundred rows instead of millions.

Two rules make summary tables safe:

  1. They are derived data. The raw table is the source of truth. You must be able to rebuild any day from it.
  2. Refresh idempotently. The refresh should be an upsert that can run twice with the same result. In MySQL that is INSERT ... ON DUPLICATE KEY UPDATE (upserts), which SQLite spells ON CONFLICT ... DO UPDATE.

Late data is the trap: an order arriving at 00:10 for yesterday changes yesterday's total. Re-compute the last few days on every refresh, not only today. The demo does exactly that, and then verifies the summary against the raw table:

SQL Incremental refresh of a daily summary

Press Run (or Ctrl + Enter) to see the result here.

Before the refresh, 2026-03-02 still shows 1 order and 200, and 2026-03-03 is missing. After it the table reads 150 for 03-01, 230 from 2 orders for 03-02 and 70 for 03-03, and the check reports 0 mismatched days. In MySQL the upsert reads INSERT ... SELECT ... ON DUPLICATE KEY UPDATE orders = VALUES(orders), revenue = VALUES(revenue); MySQL 8.0.20 deprecated VALUES() in this position in favour of a row alias, which is the form shown in the upsert lesson.

How do you refresh? Options, from simplest to most automatic:

  • Scheduled job (cron or a MySQL event) every few minutes, recomputing the last two days. Simple and robust.
  • Write-time updates: the code that inserts an order also increments the daily row (ON DUPLICATE KEY UPDATE revenue = revenue + 100). Always current, but you must update it everywhere, including refunds and deletes.
  • Triggers do the write-time update inside the database. They cover every writer but add cost to every insert (triggers).

MySQL has no materialized views, so a summary table plus one of these refresh strategies is the standard replacement.

Caching aggregates

Even a summary query can be repeated thousands of times for identical inputs. A dashboard for the same account and date range gives the same answer for the next few minutes, so keep it in a cache (Redis, Memcached or the framework's cache) with a time to live.

  • Key the cache entry by everything that changes the answer: report name, tenant, date range, filters.
  • Prefer a short TTL (1 to 15 minutes) over clever invalidation. Dashboards can be slightly stale. Financial totals that must be exact should come from the database.
  • Protect against a stampede: when a popular entry expires, hundreds of requests may recompute it at once. Use a lock so only one rebuilds it, or refresh it in the background before it expires.
  • Cache the result, not the page, so several views can share it.
flowchart LR
  R["Dashboard request"] --> C{"In cache?"}
  C -- "yes" --> O["Return cached numbers"]
  C -- "no" --> S["Read daily_sales"]
  S --> P["Store with TTL"]
  P --> O

The cache protects the summary table, and the summary table protects the raw orders.

When even summaries are too heavy, for example ad-hoc questions over billions of events, move analytics off MySQL: read from a replica, or copy data into a column store such as ClickHouse or a cloud warehouse. MySQL is excellent at transactions, and a poor place to scan everything for every chart.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which function gives you the previous month's revenue next to the current month's?

    LAG reads a value from an earlier row in the window order. LEAD reads from a later row.
  2. 2. What does GROUPING(country) = 1 tell you in a WITH ROLLUP result?

    GROUPING distinguishes the NULLs that ROLLUP adds from real NULLs in the data.
  3. 3. How do you compute a cohort's retention in month N?

    Retention counts each user at most once per offset and divides by the number of users who started in that cohort.
  4. 4. Why should a summary-table refresh be an upsert and recompute the last few days?

    An idempotent refresh gives the same result when repeated, and re-reading recent days fixes totals changed by late rows.
  5. 5. What is a good first choice for caching a dashboard aggregate?

    A short TTL is simple and safe. The key must contain everything that changes the result, or users will see each other's numbers.

Summary

  • Aggregate first, then use window functions: LAG for change, SUM() OVER for running totals, RANK for top-N per group.
  • GROUP BY ... WITH ROLLUP and GROUPING() give subtotals in MySQL. UNION ALL over a grouped CTE does the same in portable SQL.
  • Cohort retention joins users to activity, computes the month offset and counts distinct users per offset. Pivot with SUM(CASE WHEN ...).
  • Summary tables hold derived daily totals. Refresh them with an idempotent upsert, re-reading recent days, and verify against the raw data.
  • Cache results with a short TTL and a complete key, then move heavy analytics to a replica or a warehouse.

Next: MySQL Exercises with Solutions.