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 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%2Fwww.msaied.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%2Fwww.msaied.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://www.msaied.com/articles) [ ![Laravel AI SDK v0.10: 4 New Features Explained](https://cdn.msaied.com/540/e74363f048913d3782bc16a7292215db.png) Laravel AI SDK AI Agents Filesystem Tools ### Laravel AI SDK v0.10: 4 New Features Explained Laravel AI SDK v0.10 ships with filesystem tools for agents, human tool approval, and more. This walkthrough c... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 12 Aug 2026 3 min read Read ](https://www.msaied.com/articles/laravel-ai-sdk-v010-4-new-features-explained) [ ![NightOwl: Laravel Monitoring With Flat Pricing and Your Own PostgreSQL Storage](https://cdn.msaied.com/538/60582948c0339b19e872f4f3c03171f2.png) Laravel Monitoring PostgreSQL ### NightOwl: Laravel Monitoring With Flat Pricing and Your Own PostgreSQL Storage NightOwl redirects Laravel Nightwatch telemetry into a PostgreSQL database you own, replacing per-event billin... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 11 Aug 2026 3 min read Read ](https://www.msaied.com/articles/nightowl-laravel-monitoring-with-flat-pricing-and-your-own-postgresql-storage) [ ![Mock PHP Classes in Tests With the Double Library](https://cdn.msaied.com/537/be45e68dffd72aa9bd833c2f409fcb07.png) testing mocking phpunit ### Mock PHP Classes in Tests With the Double Library Double is a PHP 8.3 test double library by Jason McCreary that replaces mocks, spies, and partials with a sing... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 11 Aug 2026 4 min read Read ](https://www.msaied.com/articles/mock-php-classes-in-tests-with-the-double-library) [ ![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)