PostgreSQL Window Functions in Laravel: Ranking, Running Totals, and Gap Detection

Why Window Functions Belong in Your Laravel Toolkit

Most Laravel developers reach for Eloquent collections when they need rankings or running totals. The data comes back from the database, then PHP loops over it. For small result sets that is fine. At tens of thousands of rows it becomes a memory and latency problem you did not need to create.

PostgreSQL window functions execute inside the database engine, operate over a defined partition of rows, and return one output row per input row — unlike GROUP BY which collapses rows. Laravel's query builder exposes selectRaw, DB::raw, and fromSub which are all you need to compose them cleanly.

RANK and DENSE_RANK: Leaderboards Without PHP Sorting

Imagine a scores table with user_id, game_id, and points. You want each user's rank within a game.

use Illuminate\Support\Facades\DB;

$ranked = DB::table('scores')
    ->select([
        'user_id',
        'game_id',
        'points',
        DB::raw(
            'RANK() OVER (PARTITION BY game_id ORDER BY points DESC) AS rank'
        ),
    ])
    ->where('game_id', $gameId)
    ->orderBy('rank')
    ->get();

RANK() leaves gaps after ties (1, 1, 3). Use DENSE_RANK() if you want (1, 1, 2). Both are zero-cost to swap.

Running Totals with SUM OVER

A common billing requirement: show each invoice alongside the customer's cumulative spend to date.

$running = DB::table('invoices')
    ->select([
        'id',
        'customer_id',
        'amount',
        'issued_at',
        DB::raw(
            'SUM(amount) OVER (
                PARTITION BY customer_id
                ORDER BY issued_at
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ) AS running_total'
        ),
    ])
    ->where('customer_id', $customerId)
    ->orderBy('issued_at')
    ->get();

The ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame clause is explicit and safe. Without it PostgreSQL defaults to RANGE mode, which can produce surprising results when multiple rows share the same ORDER BY value.

LAG and LEAD: Gap Detection in Sequences

Detecting missing invoice numbers or subscription lapses is a classic gap-and-island problem. LAG() gives you the previous row's value in the partition without a self-join.

$gaps = DB::table(function ($query) use ($customerId) {
    $query->from('invoices')
        ->select([
            'invoice_number',
            DB::raw(
                'LAG(invoice_number) OVER (
                    PARTITION BY customer_id
                    ORDER BY invoice_number
                ) AS prev_number'
            ),
        ])
        ->where('customer_id', $customerId);
}, 'windowed')
->whereRaw('invoice_number <> prev_number + 1')
->whereNotNull('prev_number')
->get();

The subquery alias (windowed) is required by PostgreSQL when you filter on a window result — you cannot put a WHERE on a window function in the same query level. Laravel's closure-based fromSub handles this neatly.

Wrapping Results in Eloquent Models

If you want hydrated models with the computed columns attached, use hydrate:

$models = Invoice::hydrate($running->toArray());
// $models->first()->running_total is now accessible

Or add the window expression to an Eloquent builder directly:

Invoice::query()
    ->addSelect(DB::raw(
        'SUM(amount) OVER (PARTITION BY customer_id ORDER BY issued_at) AS running_total'
    ))
    ->where('customer_id', $customerId)
    ->get();

Eloquent will populate running_total as a dynamic attribute. Cast it to decimal:2 via $casts if you need precision guarantees on the PHP side.

Indexing Considerations

Window functions scan the partition. Make sure the columns in PARTITION BY and ORDER BY are indexed together:

CREATE INDEX idx_invoices_customer_issued
    ON invoices (customer_id, issued_at);

Run EXPLAIN (ANALYZE, BUFFERS) and look for WindowAgg nodes. If you see a Sort node above it that is not using an index scan, the planner is sorting in memory — add or adjust the index.

Key Takeaways

• Use RANK() / DENSE_RANK() for leaderboards; pick based on whether ties should leave gaps.
• Always specify an explicit ROWS or RANGE frame clause for running aggregates.
• Filter on window results via a subquery — Laravel's fromSub closure keeps this readable.
• LAG() / LEAD() eliminate self-joins for gap and sequence analysis.
• Index PARTITION BY + ORDER BY columns together and verify with EXPLAIN ANALYZE.
• Invoice::hydrate() or addSelect(DB::raw(...)) bridges raw window output back into Eloquent.