SQL Window Functions in MySQL 8: Running Totals, Rankings and Top-N per Group

Every analytics request eventually lands on the same desk: "Can you show revenue per month with a running total?" or "Who are the top three customers in each city?" Before MySQL 8 the honest answer involved session variables, self-joins, or exporting a CSV and finishing the job in a spreadsheet. Window functions turn all of that into a single, readable query that runs where the data lives.

What a window function actually does

A normal aggregate such as SUM() collapses rows: one output row per group. A window function also calculates across a set of related rows — the window — but it keeps every input row in the result. That is the whole trick, and it is why you can show a row's value next to its group total, its rank or the previous row's value.

The OVER() clause defines the window:

• PARTITION BY splits rows into independent groups, like a GROUP BY that doesn't collapse anything.
• ORDER BY sets the order inside each partition, which matters for running totals, rankings and LAG().
• A frame clause (ROWS BETWEEN ...) optionally narrows which rows count, for moving averages.

The examples below use a simple orders table with id, customer_id, city, total and created_at columns.

Running totals and month-over-month growth

Finance teams love two numbers: cumulative revenue and growth versus last month. A common table expression builds the monthly totals, and the outer query adds both with a named window so the definition isn't repeated:

WITH monthly AS (
    SELECT DATE_FORMAT(created_at, '%Y-%m-01') AS month_start,
           SUM(total)                          AS revenue
    FROM orders
    GROUP BY month_start
)
SELECT month_start,
       revenue,
       SUM(revenue) OVER (w ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
       LAG(revenue) OVER w                                                    AS prev_month,
       ROUND(100 * (revenue - LAG(revenue) OVER w) / LAG(revenue) OVER w, 1)   AS growth_pct
FROM monthly
WINDOW w AS (ORDER BY month_start)
ORDER BY month_start;

Notice the explicit ROWS frame on the running total. When a window has an ORDER BY but no frame, MySQL defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which treats rows with equal sort values as peers and adds them all at once. With one row per month it makes no difference, but on raw order data with duplicate timestamps it produces confusing jumps. Being explicit costs nothing.

Ranking: ROW_NUMBER, RANK and DENSE_RANK

All three number rows inside a partition. They differ only in how they treat ties. Given four customers who spent 900, 750, 750 and 600:

Use ROW_NUMBER() when you need exactly N rows, RANK() for leaderboards where a tie should push the next person down, and DENSE_RANK() when you want "the top three spending levels" regardless of how many people share them.

Top-N per group

This is the query that used to require a correlated subquery. Rank customers by spend within each city, then keep the top three:

WITH ranked AS (
    SELECT customer_id,
           city,
           SUM(total) AS spend,
           ROW_NUMBER() OVER (PARTITION BY city ORDER BY SUM(total) DESC) AS rn
    FROM orders
    GROUP BY customer_id, city
)
SELECT city, customer_id, spend
FROM ranked
WHERE rn <= 3
ORDER BY city, rn;

Moving averages with frames

Daily revenue is noisy. A seven-day moving average smooths it out, and the frame clause does all the work:

SELECT day,
       revenue,
       ROUND(AVG(revenue) OVER (
           ORDER BY day
           RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
       ), 2) AS avg_7d
FROM daily_revenue
ORDER BY day;

We use RANGE with an interval here on purpose. ROWS BETWEEN 6 PRECEDING counts rows, so if a day has no sales and no row, your "seven-day" average quietly spans eight or nine calendar days. A temporal RANGE frame always covers exactly seven days.

Using window functions from Laravel

Laravel's query builder has no dedicated window methods, but selectRaw() and fromSub() get you there without dropping to a raw SQL string:

use Illuminate\Support\Facades\DB;

$ranked = DB::table('orders')
    ->selectRaw('customer_id, city, SUM(total) AS spend')
    ->selectRaw('ROW_NUMBER() OVER (PARTITION BY city ORDER BY SUM(total) DESC) AS rn')
    ->groupBy('customer_id', 'city');

$topCustomers = DB::query()
    ->fromSub($ranked, 'ranked')
    ->where('rn', '<=', 3)
    ->orderBy('city')
    ->orderBy('rn')
    ->get();

Window functions still need to sort data, so the usual performance rules apply. An index that matches your PARTITION BY and ORDER BY columns lets MySQL read rows already in order, and EXPLAIN will tell you whether it's resorting to a filesort.

If you find yourself exporting query results just to add a running total, the query isn't finished yet.

Start with the three patterns that cover most reporting work — running totals, top-N per group and LAG() for period-over-period change — and the spreadsheet round-trips will disappear.