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

Why Window Functions Belong in Your Laravel Toolkit

Window functions execute across a set of rows related to the current row without collapsing them into a single output row the way GROUP BY does. That distinction matters: you keep every row while still computing aggregates, ranks, or offsets across a logical partition. Doing the same work in PHP means loading thousands of rows into memory and iterating — a trade-off you should rarely accept.

PostgreSQL has supported window functions since version 8.4. Laravel's query builder does not have a dedicated API for them, but selectRaw, DB::raw, and subquery wrapping give you everything you need.

ROW_NUMBER for Per-Partition Ranking

Imagine a orders table and you want the most recent order per customer without a correlated subquery.

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

$latest = DB::query()
    ->fromSub($ranked, 'ranked')
    ->where('rn', 1)
    ->get();

The inner query assigns a rank; the outer query filters to rank 1. PostgreSQL executes this as a single pass with a window sort — far cheaper than a MAX self-join on large tables.

Running Totals with SUM OVER

A running total is the canonical window function example, but it comes up constantly in financial dashboards and audit trails.

$ledger = DB::table('transactions')
    ->where('account_id', $accountId)
    ->selectRaw(
        'id, amount, created_at,
         SUM(amount) OVER (
             PARTITION BY account_id
             ORDER BY created_at
             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         ) AS running_balance'
    )
    ->orderBy('created_at')
    ->get();

The ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame clause is explicit about what "running" means. Omitting it relies on the default frame, which changes when you add ORDER BY — being explicit prevents subtle bugs.

Gap Detection with LAG

LAG and LEAD access the previous or next row's value without a self-join. This is useful for detecting gaps in sequential data — invoice numbers, ticket IDs, or scheduled slots.

$gaps = DB::query()->fromSub(
    DB::table('invoices')
        ->selectRaw(
            'invoice_number,
             LAG(invoice_number) OVER (ORDER BY invoice_number) AS prev_number'
        ),
    'lagged'
)
->whereRaw('invoice_number <> prev_number + 1')
->whereNotNull('prev_number')
->get();

Each row in lagged carries the previous invoice number. The outer filter surfaces any row where the sequence is broken. A PHP loop doing the same work would require the entire result set in memory first.

Wrapping Results in Eloquent Models

You can hydrate Eloquent models from raw window-function queries using hydrate:

$rows = DB::select(
    'SELECT *, RANK() OVER (ORDER BY score DESC) AS rank
     FROM leaderboard_entries
     WHERE season_id = ?',
    [$seasonId]
);

$entries = LeaderboardEntry::hydrate($rows);
// $entries[0]->rank is accessible as a dynamic attribute

The extra columns (rank here) become accessible as dynamic properties. They will not be persisted if you call save(), but they are perfect for read-heavy display logic.

Query Plan Sanity Check

Always verify the plan when adding window functions to hot paths:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, SUM(amount) OVER (PARTITION BY account_id ORDER BY created_at)
FROM transactions
WHERE account_id = 42;

Look for WindowAgg in the plan. If you see a sequential scan on a large table, a partial index on (account_id, created_at) will typically convert it to an Index Scan and eliminate the sort step entirely.

Key Takeaways

• Use selectRaw or DB::raw to embed window functions; no special query builder API is needed.
• Always specify the frame clause (ROWS BETWEEN ...) to avoid frame-default surprises.
• Wrap window queries in a subquery (fromSub) when you need to filter on the computed column.
• LAG/LEAD replace self-joins for sequential comparisons — cleaner SQL, better plans.
• Hydrate Eloquent models from raw results to keep presentation logic in the model layer.
• Verify execution plans and add partial covering indexes on partition + order columns for hot queries.