PostgreSQL Window Functions in Laravel | Mohamed Said [ ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed Said Laravel Backend Engineer ](https://msaied.com) [ Home ](https://msaied.com) [ Projects ](https://msaied.com/projects) [ Articles ](https://msaied.com/articles) [ Certificates ](https://msaied.com/certificates) [ Contact ](https://msaied.com#contact-section) [ ](https://github.com/EG-Mohamed) [ Home ](https://msaied.com) [ Projects ](https://msaied.com/projects) [ Articles ](https://msaied.com/articles) [ Certificates ](https://msaied.com/certificates) [ Contact ](https://msaied.com#contact-section) [ home ](https://msaied.com) [ articles ](https://msaied.com/articles) PostgreSQL Window Functions in Laravel: Ranking, Running Totals, and Gap Detection On this page 1. [ Why Window Functions Belong in Your SQL Layer ](#why-window-functions-belong-in-your-sql-layer) 2. [ ROW\_NUMBER and RANK for Leaderboards ](#row-number-and-rank-for-leaderboards) 3. [ Running Totals with SUM OVER ](#running-totals-with-sum-over) 4. [ LAG and LEAD for Gap Detection ](#lag-and-lead-for-gap-detection) 5. [ Wrapping Window Queries as Eloquent Results ](#wrapping-window-queries-as-eloquent-results) 6. [ NTILE for Bucketing ](#ntile-for-bucketing) 7. [ Practical Takeaways ](#practical-takeaways) ![PostgreSQL Window Functions in Laravel: Ranking, Running Totals, and Gap Detection](https://cdn.msaied.com/239/f588e7cbf8e6d3317a581ce0fa27140d.png) #laravel #postgresql #eloquent #sql PostgreSQL Window Functions in Laravel: Ranking, Running Totals, and Gap Detection ==================================================================================== 19 Jun 2026 3 min read ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said Table of contents 1. [ 01 Why Window Functions Belong in Your SQL Layer ](#why-window-functions-belong-in-your-sql-layer) 2. [ 02 ROW\_NUMBER and RANK for Leaderboards ](#row-number-and-rank-for-leaderboards) 3. [ 03 Running Totals with SUM OVER ](#running-totals-with-sum-over) 4. [ 04 LAG and LEAD for Gap Detection ](#lag-and-lead-for-gap-detection) 5. [ 05 Wrapping Window Queries as Eloquent Results ](#wrapping-window-queries-as-eloquent-results) 6. [ 06 NTILE for Bucketing ](#ntile-for-bucketing) 7. [ 07 Practical Takeaways ](#practical-takeaways) 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: ```php $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: ```php $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: ```php $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`: ```php $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: ```php $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. Found this useful? [ ](https://twitter.com/intent/tweet?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fpostgresql-window-functions-in-laravel-ranking-running-totals-and-gap-detection&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%2Fmsaied.com%2Farticles%2Fpostgresql-window-functions-in-laravel-ranking-running-totals-and-gap-detection) Frequently Asked Questions ---------------------------- 3 questions Q01 Can I use WHERE on a window function result in the same query? No. Window functions are evaluated after WHERE and HAVING. Wrap the query as a subquery using fromSub() or a CTE, then filter on the computed column in the outer query. Q02 Do window functions hurt performance compared to PHP-side aggregation? Generally no — they avoid transferring large result sets to PHP and leverage PostgreSQL's optimized executor. Add an index on the PARTITION BY and ORDER BY columns to support efficient sorting within partitions. Q03 Can I combine GROUP BY aggregates with window functions in the same SELECT? Yes. Aggregate first with GROUP BY, then apply window functions over the grouped result. The window operates on the post-aggregation rows, which is often exactly what reporting queries need. Continue reading More Articles --------------- [ View all ](https://msaied.com/articles) [ ![PostgreSQL CTEs, Recursive Queries, and Lateral Joins in Laravel](https://cdn.msaied.com/241/32858f9c67eae0649999c32a6d31818f.png) laravel postgresql query-builder ### PostgreSQL CTEs, Recursive Queries, and Lateral Joins in Laravel Go beyond basic Eloquent with raw PostgreSQL power: composable CTEs, recursive tree traversal, and LATERAL joi... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 19 Jun 2026 3 min read Read ](https://msaied.com/articles/postgresql-ctes-recursive-queries-and-lateral-joins-in-laravel) [ ![Custom Eloquent Casts: Encapsulating Domain Logic Inside Model Attributes](https://cdn.msaied.com/238/8e843e57a34f81f853eedefae629c09b.png) laravel eloquent domain-driven-design ### Custom Eloquent Casts: Encapsulating Domain Logic Inside Model Attributes Custom Eloquent casts let you push value-object logic directly into model attributes, keeping controllers and... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 19 Jun 2026 3 min read Read ](https://msaied.com/articles/custom-eloquent-casts-encapsulating-domain-logic-inside-model-attributes) [ !['The Story of PHP' Documentary Teaser Is Out — Watch It Now](https://cdn.msaied.com/237/78daf2c90e319b7a740ec4d48d5280c6.png) PHP Laravel Documentary ### 'The Story of PHP' Documentary Teaser Is Out — Watch It Now CultRepo has released a teaser for 'The Story of PHP', an upcoming documentary sponsored by JetBrains. It feat... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 18 Jun 2026 3 min read Read ](https://msaied.com/articles/the-story-of-php-documentary-teaser-is-out-watch-it-now) [ ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed Said Laravel Backend Engineer ](https://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://msaied.com) - [Projects](https://msaied.com/projects) - [Articles](https://msaied.com/articles) - [Certificates](https://msaied.com/certificates) - [Contact](https://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)