PostgreSQL Window Functions in Laravel | Mohamed Said [ ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed Said Laravel Backend Engineer ](https://www.msaied.com) [ Home ](https://www.msaied.com) [ Projects ](https://www.msaied.com/projects) [ Articles ](https://www.msaied.com/articles) [ Certificates ](https://www.msaied.com/certificates) [ Contact ](https://www.msaied.com#contact-section) [ ](https://github.com/EG-Mohamed) [ Home ](https://www.msaied.com) [ Projects ](https://www.msaied.com/projects) [ Articles ](https://www.msaied.com/articles) [ Certificates ](https://www.msaied.com/certificates) [ Contact ](https://www.msaied.com#contact-section) [ home ](https://www.msaied.com) [ articles ](https://www.msaied.com/articles) PostgreSQL Window Functions in Laravel: Ranking, Running Totals, and Gap Detection On this page 1. [ Why Window Functions Belong in Your Laravel Toolkit ](#why-window-functions-belong-in-your-laravel-toolkit) 2. [ RANK and DENSE\_RANK: Leaderboards Without PHP Sorting ](#rank-and-dense-rank-leaderboards-without-php-sorting) 3. [ Running Totals with SUM OVER ](#running-totals-with-sum-over) 4. [ LAG and LEAD: Gap Detection in Sequences ](#lag-and-lead-gap-detection-in-sequences) 5. [ Wrapping Results in Eloquent Models ](#wrapping-results-in-eloquent-models) 6. [ Indexing Considerations ](#indexing-considerations) 7. [ Key Takeaways ](#key-takeaways) ![PostgreSQL Window Functions in Laravel: Ranking, Running Totals, and Gap Detection](https://cdn.msaied.com/649/9ce340f6d71d0052cb3d0eaba1f08754.png) #laravel #postgresql #performance #eloquent #sql PostgreSQL Window Functions in Laravel: Ranking, Running Totals, and Gap Detection ==================================================================================== 9 Sep 2026 4 min read ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) Mohamed Said Table of contents 1. [ 01 Why Window Functions Belong in Your Laravel Toolkit ](#why-window-functions-belong-in-your-laravel-toolkit) 2. [ 02 RANK and DENSE\_RANK: Leaderboards Without PHP Sorting ](#rank-and-dense-rank-leaderboards-without-php-sorting) 3. [ 03 Running Totals with SUM OVER ](#running-totals-with-sum-over) 4. [ 04 LAG and LEAD: Gap Detection in Sequences ](#lag-and-lead-gap-detection-in-sequences) 5. [ 05 Wrapping Results in Eloquent Models ](#wrapping-results-in-eloquent-models) 6. [ 06 Indexing Considerations ](#indexing-considerations) 7. [ 07 Key Takeaways ](#key-takeaways) 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. ```php 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. ```php $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. ```php $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`: ```php $models = Invoice::hydrate($running->toArray()); // $models->first()->running_total is now accessible ``` Or add the window expression to an Eloquent builder directly: ```php 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: ```sql 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. Found this useful? [ ](https://twitter.com/intent/tweet?url=https%3A%2F%2Fwww.msaied.com%2Farticles%2Fpostgresql-window-functions-in-laravel-ranking-running-totals-and-gap-detection-2&text=PostgreSQL+Window+Functions+in+Laravel%3A+Ranking%2C+Running+Totals%2C+and+Gap+Detection) [ ](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fwww.msaied.com%2Farticles%2Fpostgresql-window-functions-in-laravel-ranking-running-totals-and-gap-detection-2) Frequently Asked Questions ---------------------------- 3 questions Q01 Can I use window functions directly inside an Eloquent scope? Yes. Call `addSelect(DB::raw('RANK() OVER (...) AS rank'))` inside a local scope. The result is available as a dynamic attribute on each model instance. If you need to filter by the window result, wrap the scope in a `fromSub` subquery. Q02 Do window functions hurt performance compared to PHP-side sorting? Generally no — the database engine processes them in a single pass over an indexed scan. The risk is an unindexed sort node in the query plan. Always run EXPLAIN ANALYZE and ensure the PARTITION BY and ORDER BY columns share a composite index. Q03 Why does filtering on a window function require a subquery? PostgreSQL evaluates window functions after WHERE and HAVING clauses in the same query level. To filter on a window result you must wrap the window query as a derived table (subquery) and apply the WHERE in the outer query. Laravel's fromSub closure makes this straightforward. Continue reading More Articles --------------- [ View all ](https://www.msaied.com/articles) [ ![Event Sourcing in Laravel: Aggregates, Projectors, and Rebuilding Read Models](https://cdn.msaied.com/647/586a0f822614fed8091917a895ebc502.png) laravel event-sourcing ddd ### Event Sourcing in Laravel: Aggregates, Projectors, and Rebuilding Read Models A practical walkthrough of event sourcing in Laravel — defining aggregates, persisting domain events, building... ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) Mohamed Said 9 Sep 2026 3 min read Read ](https://www.msaied.com/articles/event-sourcing-in-laravel-aggregates-projectors-and-rebuilding-read-models) [ ![Queue totalSize() and JobInterrupted Event in Laravel 13.31](https://cdn.msaied.com/648/d0e925e5b65d5b1d925fdaf612af5db2.png) Laravel 13 Queue Eloquent ### Queue totalSize() and JobInterrupted Event in Laravel 13.31 Laravel 13.31 ships Queue::totalSize(), a new JobInterrupted event, chaperone support for BelongsToMany pivot... ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) Mohamed Said 9 Sep 2026 3 min read Read ](https://www.msaied.com/articles/queue-totalsize-and-jobinterrupted-event-in-laravel-1331) [ ![Read/Write Splitting and Sticky Reads in Laravel: A Production Guide](https://cdn.msaied.com/646/4155b1eed8a491a99c3dda6f7dddd80e.png) laravel database performance ### Read/Write Splitting and Sticky Reads in Laravel: A Production Guide Learn how Laravel's read/write connection splitting works under the hood, when sticky reads save you from repl... ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) Mohamed Said 9 Sep 2026 3 min read Read ](https://www.msaied.com/articles/readwrite-splitting-and-sticky-reads-in-laravel-a-production-guide) [ ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed Said Laravel Backend Engineer ](https://www.msaied.com)Senior Backend Engineer specializing in Laravel, scalable SaaS platforms, APIs, and cloud infrastructure. I build secure, high-performance web applications that help businesses grow. Explore - [Home](https://www.msaied.com) - [Projects](https://www.msaied.com/projects) - [Articles](https://www.msaied.com/articles) - [Certificates](https://www.msaied.com/certificates) - [Contact](https://www.msaied.com#contact-section) Connect - [ hello@msaied.com ](mailto:hello@msaied.com) - [ +20 109 461 9204 ](tel:+201094619204) © 2026 Mohamed Said. All rights reserved. [ ](https://github.com/EG-Mohamed) [ ](https://www.linkedin.com/in/msaiedm/) [ ](https://wa.me/201094619204) [ ](mailto:hello@msaied.com) [ ](https://drive.google.com/file/u/0/d/1MF20IPRJyzfy32mhEutjL5EpSls0w2Q8/view)