Weekday and weekend classes available

Book a free 20-minute session with a mentor

No payment on this website: confirm your schedule and payment options with our team

SQL and MySQL

SQL window functions for analysts: running totals and month-on-month change

Vivek Kumar, Co-Founder & Chief Learning Officer4 min read
Window functions for analysts

Window functions answer the questions managers ask every week: how are we tracking so far this year, what changed since last month, and who is ahead. This article shows three patterns on one small table, with the results, so you can see exactly what each one does.

The data

Monthly revenue for two regions. Copy this into MySQL 8 (or any database with window functions) and run the queries as you read.

CREATE TABLE monthly_sales (month DATE, region VARCHAR(10), revenue INT);
INSERT INTO monthly_sales VALUES
 ('2026-01-01','North',12000), ('2026-02-01','North',13500),
 ('2026-03-01','North',12800), ('2026-04-01','North',15100),
 ('2026-01-01','South',9800),  ('2026-02-01','South',9100),
 ('2026-03-01','South',10400), ('2026-04-01','South',11000);

GROUP BY collapses rows. A window function keeps them.

With GROUP BY you get one row per region:

SELECT region, SUM(revenue) AS revenue
FROM monthly_sales
GROUP BY region;
regionrevenue
North53400
South40300

With a window function you keep every month and add the region total next to it. The OVER (PARTITION BY region) part says which rows to look at; it does not remove any.

SELECT region, month, revenue,
  SUM(revenue) OVER (PARTITION BY region) AS region_total
FROM monthly_sales;

Each North row now shows 53,400 and each South row 40,300. That is what makes a window useful: the detail and the summary sit on the same row, so you can compare them.

Pattern 1: running total

SELECT region, month, revenue,
  SUM(revenue) OVER (PARTITION BY region ORDER BY month
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM monthly_sales
ORDER BY region, month;
regionmonthrevenuerunning_total
North2026-01-011200012000
North2026-02-011350025500
North2026-03-011280038300
North2026-04-011510053400
South2026-01-0198009800
South2026-02-01910018900
South2026-03-011040029300
South2026-04-011100040300

Why the ROWS BETWEEN line? When you add ORDER BY inside OVER without a frame, the database uses a RANGE frame. If two rows share the same date they get the same running total, which is rarely what a report needs. Writing the frame makes the result predictable.

Pattern 2: change since last month with LAG

SELECT region, month, revenue,
  LAG(revenue) OVER (PARTITION BY region ORDER BY month) AS prev_month,
  ROUND(100 * (revenue - LAG(revenue) OVER (PARTITION BY region ORDER BY month))
        / LAG(revenue) OVER (PARTITION BY region ORDER BY month), 1) AS pct_change
FROM monthly_sales
ORDER BY region, month;
regionmonthrevenueprev_monthpct_change
North2026-01-0112000NULLNULL
North2026-02-01135001200012.5
North2026-03-011280013500-5.2
North2026-04-01151001280018.0
South2026-01-019800NULLNULL
South2026-02-0191009800-7.1
South2026-03-0110400910014.3
South2026-04-0111000104005.8

January has no previous month, so LAG returns NULL and so does the change. Leave it as NULL rather than 0: a 0% change and "no data" mean different things on a dashboard.

The business reading: North dipped in March and recovered strongly in April; South fell in February and has grown since. That sentence, not the query, is what goes in the report.

Three mistakes to avoid

  1. Filtering a window function in WHERE. SQL calculates WHERE before window functions, so WHERE pct_change < 0 fails. Put the query in a CTE and filter outside it.
  2. Forgetting PARTITION BY. Without it, the running total runs across both regions as one series, and LAG compares North's January with South's April.
  3. Gaps in the months. LAG takes the previous row, not the previous month. If a month is missing, the comparison silently skips it. Join to a calendar table first if gaps are possible.

Practise

Using the same table, write a query that shows each month's share of that region's total revenue, rounded to one decimal place. Hint: divide revenue by SUM(revenue) OVER (PARTITION BY region).

More from Insights

Artificial Intelligence

How to check SQL an AI assistant writes for you

AI-written SQL often runs and is still wrong. A worked example of a double-counting join, and a five-step routine to check any AI-written query.

Vivek Kumar3 min read

Newsletter

Stay Ahead in Data and AI

Get new learning resources, practical guides, project ideas, and AI insights delivered to your inbox.

  • New free PDFs and Insights articles
  • We confirm by email before adding you
  • Unsubscribe in one click, any time
  • Learn data
  • Use AI
  • Build real projects
  • Build your portfolio
  • Develop professional skills
  • Get guided support
  • Apply it at work

Find your program.

A few short questions and a personalized report. Or talk to a mentor for free.

Talk to a mentorFind my program

Your cookie choices

Choose which optional cookies we may use. Essential storage is always on because the site needs it.

EssentialAlways on

Your theme and currency, this choice, and spam protection on forms.

Analytics

Google Analytics, to count visits and see which pages and links help people. Also lets us note which link brought you here and send it with a request you make.

Advertising

Meta (Facebook, Instagram) and Google Ads, to measure whether our ads lead to enquiries.

Cookie policy ยท Privacy policy ยท