How to Use MySQL Window Functions for Data Analysis: Rankings, Running Totals, and Moving Averages

Most database work starts simple. How much did we move last month? Which product brought in the most cash? What does the average number of orders per customer look like? Then things get more interesting. Maybe you want to rank products inside each category, stack this month’s numbers against last month’s, or track a seven-day moving average without losing any of the daily detail.

Regular aggregate queries can handle some of this, but they run into a wall: GROUP BY collapses your rows down to one per group. Window functions take a different approach. They run calculations across related rows while leaving each individual record intact, making them a go-to tool for analytical work in MySQL.

Why Window Functions Change the Game

Say you have a table called sales with these columns:

sale_date
region
salesperson
revenue

Want total revenue by region? A basic aggregate query gets you there:

SELECT region, SUM(revenue) AS total_revenue
FROM sales
GROUP BY region;

You end up with one row per region, which is fine when the region totals are all you need. If you want to keep your data organized and readable while working with multiple columns or tables, learning how to use aliases for columns and tables in MySQL queries helps keep your code clean and manageable.

But what if you want every single sale visible, with the region’s total right next to it? That is where a window function earns its keep:

SELECT
    sale_date,
    region,
    salesperson,
    revenue,
    SUM(revenue) OVER (PARTITION BY region) AS region_total
FROM sales;

The OVER() clause tells MySQL to treat SUM() as a window function rather than collapsing rows. PARTITION BY region splits the data into separate buckets for the calculation. That one idea, running the math without wiping out the rows, is really the backbone of everything else in this guide.

Ranking Rows with Partitioning

Ranking shows off window functions at their best. Say you want to rank salespeople by revenue, region by region:

SELECT
    region,
    salesperson,
    revenue,
    RANK() OVER (
        PARTITION BY region
        ORDER BY revenue DESC
    ) AS sales_rank
FROM sales;

The partition builds a fresh ranking for each region, and ORDER BY revenue DESC puts the top earner first.

MySQL gives you three ranking functions to choose from: ROW_NUMBER(), RANK(), and DENSE_RANK(). They behave the same right up until a tie shows up. ROW_NUMBER() hands out a different number to each row no matter what, even tied ones. RANK() assigns tied rows the same number, then skips the next rank. DENSE_RANK() also ties them together but keeps the next rank consecutive rather than skipping.

Take revenues of 10,000, 8,000, 8,000, and 6,000: RANK() gives you 1, 2, 2, 4. DENSE_RANK() gives you 1, 2, 2, 3. Which one is correct really comes down to what you mean by rank, not which function feels fancier. If you are dealing with massive result sets during this analysis and need to restrict your output size for testing or reporting, you can apply the limit clause to constrain the number of rows returned in MySQL to keep your query results manageable.

Running Totals That Keep the Detail

Running totals answer a different kind of question. Instead of asking for a grand total, you want to see how revenue builds up day by day:

SELECT
    sale_date,
    revenue,
    SUM(revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_revenue
FROM sales;

Now every row shows both the day’s revenue and the cumulative total up to that point. This approach also works seamlessly with multi-group data:

SUM(revenue) OVER (
    PARTITION BY region
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

Now the running total resets at the start of each region. This comes in handy for sales targets, cumulative spend, sign-up counts, and inventory tracking, basically anywhere the trend line matters as much as the final number.

None of this works around bad data architecture, though. No amount of clever SQL fixes a dataset that is poorly structured, messy, or bloated beyond reason. Before running complex window queries, mastering proper data cleaning essentials with SQL ensures your underlying tables are accurate and reliable.

Turning Daily Numbers Into Moving Averages

Where running totals stack everything up, moving averages zoom in on a fixed window of recent observations instead:

SELECT
    sale_date,
    revenue,
    AVG(revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM daily_sales;

This averages the current row with the six before it. As long as your table has one entry per day with no gaps, you have a genuine seven-day moving average.

That last part matters more than it sounds. ROWS counts actual rows in the table, not calendar days. Miss a Tuesday in your data, and MySQL will not fill it in for you. It just moves on. So before calling something a seven-day average, make sure your data actually has seven days in it. Moving averages earn their place because raw daily numbers bounce around a lot. One value spikes, the next drops, and it is hard to tell what is actually happening until the moving average smooths it out and shows you the real direction.

Looking Backward with LAG()

Sometimes what matters is not a group total, but simply what happened last time. MySQL’s LAG() function handles that comparison directly:

SELECT
    sale_date,
    revenue,
    LAG(revenue) OVER (ORDER BY sale_date) AS previous_revenue
FROM daily_sales;

From there, finding the difference is just one line away:

revenue - LAG(revenue) OVER (ORDER BY sale_date)

Now you can see exactly how much a value climbed or dropped from the row before it. Percentage change works the same way, though you should watch out for a previous value of zero or NULL, which will break the math. Also, the very first row will not have anything before it, so LAG() returns NULL by default unless you set a default value.

Window Functions Fit Into a Bigger MySQL Picture

As useful as they are, window functions do not replace everything else in your SQL toolkit. GROUP BY is still the right call when you genuinely need a single summary row per category. Joins are still how you combine tables, and indexes and solid schema design still decide whether your queries run fast, especially when securely managing remote databases, where more information is available on ExpressVPN’s website.

For day-to-day analysis, the real skill is not memorizing syntax. It is knowing which tool fits the question you are actually asking. A grouped aggregate answers what the total is for each region. A window function answers something bigger: what this sale is worth, what its region’s total is, and where it lands in the ranking. That second question keeps the original data intact rather than discarding it.

Keep the Rows, Add the Context

Do not try to memorize every window function out there. Start with the underlying idea: keep the rows and build something meaningful around them.

Once that clicks, the rest falls into place. PARTITION BY sets the groups, ORDER BY sets the sequence, and the function itself decides what gets measured. RANK() shows position, SUM() builds a running total, AVG() smooths things into a moving average, and LAG() shows how today compares to yesterday.

For anyone already comfortable with basic MySQL aggregation, window functions are the natural next step. They mark the point where a query stops just spitting out totals and starts actually telling you something about the trends and relationships hiding in your data.