MySQL Reporting Queries: Revenue Trends, Running Totals

Management asks the same kind of questions: how much did we sell each month, how does it compare with last month, who are the best customers, how many new customers came back? These queries combine what you learned earlier: aggregates, GROUP BY, CTEs and window functions. Here they are put together on one small dataset.

The data

query.sql

SQL

CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(20) NOT NULL, signup_date DATE NOT NULL);
CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT NOT NULL, order_date DATE NOT NULL, amount DECIMAL(8,2) NOT NULL, KEY idx_date (order_date));

INSERT INTO customers VALUES (1, 'Maya', '2026-01-03'), (2, 'Rohan', '2026-01-15'), (3, 'Isha', '2026-02-02'), (4, 'Kabir', '2026-02-20'), (5, 'Zoya', '2026-03-05');
INSERT INTO orders VALUES
(1, 1, '2026-01-05', 120.00), (2, 1, '2026-02-10', 80.00), (3, 1, '2026-03-12', 200.00),
(4, 2, '2026-01-20', 60.00),  (5, 2, '2026-03-02', 90.00),
(6, 3, '2026-02-05', 150.00), (7, 3, '2026-02-25', 50.00), (8, 3, '2026-03-20', 70.00),
(9, 4, '2026-02-22', 300.00),
(10, 5, '2026-03-06', 40.00), (11, 5, '2026-03-28', 60.00);

Output

OUTPUT

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 5 rows affected
Records: 5  Duplicates: 0  Warnings: 0

Query OK, 11 rows affected
Records: 11  Duplicates: 0  Warnings: 0

Revenue per month, with a total

query.sql

SQL

1
2
3

SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, COUNT(*) AS orders, SUM(amount) AS revenue, ROUND(AVG(amount), 2) AS avg_order
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m') WITH ROLLUP;

Output

OUTPUT

+---------+--------+---------+-----------+
| month   | orders | revenue | avg_order |
+---------+--------+---------+-----------+
| 2026-01 |      2 |  180.00 |     90.00 |
| 2026-02 |      4 |  580.00 |    145.00 |
| 2026-03 |      5 |  460.00 |     92.00 |
| NULL    |     11 | 1220.00 |    110.91 |
+---------+--------+---------+-----------+
4 rows in set

WITH ROLLUP adds the grand total row (the NULL month). Grouping on a function of the column is fine for a report. For large tables, a range condition on the raw column is better, because it can use the index (Date Filters):

query.sql

SQL

SELECT SUM(amount) AS february_revenue FROM orders WHERE order_date >= '2026-02-01' AND order_date < '2026-03-01';

Output

OUTPUT

+------------------+
| february_revenue |
+------------------+
|           580.00 |
+------------------+
1 row in set

Running total and month-over-month change

query.sql

SQL

WITH monthly AS (
    SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(amount) AS revenue
    FROM orders GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
SELECT month,
       revenue,
       SUM(revenue) OVER (ORDER BY month) AS running_total,
       LAG(revenue) OVER (ORDER BY month) AS previous_month,
       ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month) * 100, 1) AS change_pct
FROM monthly
ORDER BY month;

Output

OUTPUT

+---------+---------+---------------+----------------+------------+
| month   | revenue | running_total | previous_month | change_pct |
+---------+---------+---------------+----------------+------------+
| 2026-01 |  180.00 |        180.00 |           NULL |       NULL |
| 2026-02 |  580.00 |        760.00 |         180.00 |      222.2 |
| 2026-03 |  460.00 |       1220.00 |         580.00 |      -20.7 |
+---------+---------+---------------+----------------+------------+
3 rows in set

The CTE first makes one row per month. Then window functions add the running total and compare each month with the previous one (LAG, Running Totals). The first month has no previous month, so its change is NULL.

Top customers

query.sql

SQL

SELECT c.name, COUNT(*) AS orders, SUM(o.amount) AS spent,
       ROUND(SUM(o.amount) / SUM(SUM(o.amount)) OVER () * 100, 1) AS share_pct
FROM customers c JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY spent DESC, c.name
LIMIT 3;

Output

OUTPUT

+-------+--------+--------+-----------+
| name  | orders | spent  | share_pct |
+-------+--------+--------+-----------+
| Maya  |      3 | 400.00 |      32.8 |
| Kabir |      1 | 300.00 |      24.6 |
| Isha  |      3 | 270.00 |      22.1 |
+-------+--------+--------+-----------+
3 rows in set

SUM(SUM(o.amount)) OVER () is the grand total used as the denominator. The window function runs after GROUP BY, so it can use the sums. (The share is of all customers, because LIMIT is applied last.)

Customers who ordered in every month

query.sql

SQL

SELECT c.name, COUNT(DISTINCT DATE_FORMAT(o.order_date, '%Y-%m')) AS active_months
FROM customers c JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
HAVING COUNT(DISTINCT DATE_FORMAT(o.order_date, '%Y-%m')) = 3
ORDER BY c.name;

Output

OUTPUT

+------+---------------+
| name | active_months |
+------+---------------+
| Maya |             3 |
+------+---------------+
1 row in set

Customers with no orders (an anti-join)

query.sql

SQL

1
2
3

INSERT INTO customers VALUES (6, 'Dev', '2026-03-10');

SELECT c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL;

Output

OUTPUT

+------+
| name |
+------+
| Dev  |
+------+
1 row in set

Cohorts: do new customers come back?

A cohort report groups customers by the month they started and counts how many were active in each later month. It is a classic way to measure retention.

query.sql

SQL

WITH first_orders AS (
    SELECT customer_id, DATE_FORMAT(MIN(order_date), '%Y-%m') AS cohort
    FROM orders GROUP BY customer_id
),
activity AS (
    SELECT DISTINCT customer_id, DATE_FORMAT(order_date, '%Y-%m') AS active_month FROM orders
)
SELECT f.cohort,
       a.active_month,
       PERIOD_DIFF(REPLACE(a.active_month, '-', ''), REPLACE(f.cohort, '-', '')) AS months_since_first,
       COUNT(*) AS active_customers
FROM first_orders f
JOIN activity a ON a.customer_id = f.customer_id
GROUP BY f.cohort, a.active_month
ORDER BY f.cohort, a.active_month;

Output

OUTPUT

+---------+--------------+--------------------+------------------+
| cohort  | active_month | months_since_first | active_customers |
+---------+--------------+--------------------+------------------+
| 2026-01 | 2026-01      |                  0 |                2 |
| 2026-01 | 2026-02      |                  1 |                1 |
| 2026-01 | 2026-03      |                  2 |                2 |
| 2026-02 | 2026-02      |                  0 |                2 |
| 2026-02 | 2026-03      |                  1 |                1 |
| 2026-03 | 2026-03      |                  0 |                1 |
+---------+--------------+--------------------+------------------+
6 rows in set

Read it like this: customers whose first order was in 2026-01 (Maya and Rohan) were both active in month 0 (January), only Maya in February (month 1), and both again in March (month 2). Divide by the cohort size to get a retention percentage:

query.sql

SQL

WITH first_orders AS (
    SELECT customer_id, DATE_FORMAT(MIN(order_date), '%Y-%m') AS cohort FROM orders GROUP BY customer_id
),
activity AS (
    SELECT DISTINCT customer_id, DATE_FORMAT(order_date, '%Y-%m') AS active_month FROM orders
),
cohort_size AS (
    SELECT cohort, COUNT(*) AS size FROM first_orders GROUP BY cohort
)
SELECT f.cohort, s.size AS cohort_size, a.active_month,
       ROUND(COUNT(*) / s.size * 100) AS retention_pct
FROM first_orders f
JOIN activity a ON a.customer_id = f.customer_id
JOIN cohort_size s ON s.cohort = f.cohort
WHERE f.cohort = '2026-01'
GROUP BY f.cohort, s.size, a.active_month
ORDER BY a.active_month;

Output

OUTPUT

+---------+-------------+--------------+---------------+
| cohort  | cohort_size | active_month | retention_pct |
+---------+-------------+--------------+---------------+
| 2026-01 |           2 | 2026-01      |           100 |
| 2026-01 |           2 | 2026-02      |            50 |
| 2026-01 |           2 | 2026-03      |           100 |
+---------+-------------+--------------+---------------+
3 rows in set

Making reports fast

Filter first, aggregate after: a date range on an indexed column, not a function of the column.
Check the plan with EXPLAIN for each report query (EXPLAIN).
Reports over millions of rows: build summary tables with a scheduled job (Summary Tables).
Run heavy reports on a replica, so that they do not slow down the shop.
Keep amounts as DECIMAL, and round only for display.
Define “active”, “revenue” and “customer” once (in a view or a documented query), so that two reports do not give two numbers.

Checking the numbers

A report is only useful if it is right. Compare the pieces:

query.sql

SQL

1
2

SELECT (SELECT SUM(amount) FROM orders) AS total_orders,
       (SELECT SUM(spent) FROM (SELECT SUM(amount) AS spent FROM orders GROUP BY customer_id) x) AS total_by_customer;

Output

OUTPUT

+--------------+-------------------+
| total_orders | total_by_customer |
+--------------+-------------------+
|      1220.00 |           1220.00 |
+--------------+-------------------+
1 row in set

The two totals must match. If they do not, a join is dropping or duplicating rows.

lightbulb

Join before you aggregate carefully

If you join orders to another one-to-many table and then SUM(amount), every order is counted once per joined row. Aggregate each table to the same grain first (a CTE), then join.