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

Why Window Functions Belong in Your SQL Layer

Window functions execute across a partition of rows while keeping each row intact — no GROUP BY collapse, no subquery explosion. For reporting, leaderboards, audit trails, and gap detection, pushing this logic into PostgreSQL is almost always faster and cleaner than iterating in PHP.

Laravel's query builder won't generate window syntax for you, but it gets out of the way cleanly with selectRaw, DB::raw, and subquery wrapping.

ROW_NUMBER and RANK for Leaderboards

Suppose you have an order_items table and you want each product ranked by revenue within its category:

$ranked = DB::table('order_items')
    ->selectRaw("
        product_id,
        category_id,
        SUM(amount) AS revenue,
        RANK() OVER (
            PARTITION BY category_id
            ORDER BY SUM(amount) DESC
        ) AS rank
    ")
    ->groupBy('product_id', 'category_id')
    ->orderBy('category_id')
    ->orderBy('rank')
    ->get();

RANK() leaves gaps after ties; use DENSE_RANK() if you want consecutive integers. ROW_NUMBER() is deterministic but arbitrary for ties — pick the right one for your domain.

Running Totals with SUM OVER

A running balance on a ledger_entries table:

$ledger = DB::table('ledger_entries')
    ->selectRaw("
        id,
        account_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('account_id')
    ->orderBy('created_at')
    ->get();

The ROWS BETWEEN frame clause is explicit here — always specify it when order matters, otherwise PostgreSQL uses a default range frame that can surprise you with ties.

LAG and LEAD for Gap Detection

Detecting gaps in sequential event streams (e.g., missing invoice numbers) is a classic window use-case:

$gaps = DB::table(function ($query) {
    $query->from('invoices')
        ->selectRaw("
            invoice_number,
            LAG(invoice_number) OVER (ORDER BY invoice_number) AS prev_number
        ");
}, 'numbered')
->whereRaw('invoice_number <> prev_number + 1')
->select('prev_number', 'invoice_number')
->get();

The outer query filters rows where the current number is not exactly one more than the previous — those are your gaps. No PHP loop, no loading thousands of rows.

Wrapping Window Queries as Eloquent Results

When you need Eloquent model hydration on top of a window query, use a subquery with fromSub:

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

$latestPerCustomer = Order::fromSub($sub, 'ranked')
    ->where('rn', 1)
    ->get();

This returns hydrated Order models for each customer's most recent order — a pattern that replaces convoluted DISTINCT ON workarounds.

NTILE for Bucketing

Segmenting users into quartiles by lifetime value:

$quartiles = DB::table('customers')
    ->selectRaw("
        id,
        lifetime_value,
        NTILE(4) OVER (ORDER BY lifetime_value DESC) AS quartile
    ")
    ->get();

Pass the result to a collection pipeline for further grouping — the heavy lifting stays in Postgres.

Practical Takeaways

• Use selectRaw or DB::raw to embed window expressions; the query builder won't abstract them, and that's fine.
• Always specify ROWS BETWEEN or RANGE BETWEEN explicitly when using ordered frames to avoid surprising defaults.
• Wrap window queries in a subquery (fromSub) to filter on computed window columns — you cannot WHERE on a window alias in the same query level.
• RANK vs DENSE_RANK vs ROW_NUMBER is a domain decision, not a performance one — choose deliberately.
• Window functions run after WHERE and GROUP BY, so aggregate first, then window if you need both.