PostgreSQL Window Functions in Laravel | Mohamed Said [Skip to content](#main) [ ![](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed SaidLaravel Backend Engineer ](https://www.msaied.com/public) - [Home](https://www.msaied.com/public) - [Projects](https://www.msaied.com/public/projects) - [Articles](https://www.msaied.com/public/articles) - [Certificates](https://www.msaied.com/public/certificates) - [About](https://www.msaied.com/public#about) [ Contact](https://www.msaied.com/public#contact) Menu Menu ---- Close - [HomeStart here](https://www.msaied.com/public) - [ProjectsCase studies](https://www.msaied.com/public/projects) - [ArticlesEngineering notes](https://www.msaied.com/public/articles) - [CertificatesCredentials](https://www.msaied.com/public/certificates) - [AboutHow I work](https://www.msaied.com/public#about) - [ContactGet in touch](https://www.msaied.com/public#contact) [Start a conversation](https://www.msaied.com/public#contact) [WhatsApp](https://wa.me/201094619204) [Email](mailto:hello@msaied.com) 1. [Home](https://www.msaied.com/public) 2. / 3. [Articles](https://www.msaied.com/public/articles) 4. / 5. PostgreSQL Window Functions in Laravel: Ranking, Running Totals, and Gap Detection PostgreSQL Window Functions in Laravel: Ranking, Running Totals, and Gap Detection =================================================================================== Window functions let you compute rankings, running totals, and gaps directly in SQL without pulling rows into PHP. Learn how to use them cleanly from Laravel's query builder and raw expressions. ![](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) [Mohamed Said](https://www.msaied.com/public#person) Published 19 Jun 2026 · Updated 19 Jun 2026 · 3 min read ShareCopy linkCopied ![PostgreSQL Window Functions in Laravel: Ranking, Running Totals, and Gap Detection](https://cdn.msaied.com/239/f588e7cbf8e6d3317a581ce0fa27140d.png) 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) 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. - [laravel](https://www.msaied.com/public/articles?search=laravel) - [postgresql](https://www.msaied.com/public/articles?search=postgresql) - [eloquent](https://www.msaied.com/public/articles?search=eloquent) - [sql](https://www.msaied.com/public/articles?search=sql) Frequently asked questions --------------------------- 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. 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. 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. ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp)About the author ---------------- [Mohamed Said](https://www.msaied.com/public#person)Senior Backend Engineer specializing in Laravel, scalable SaaS platforms, APIs, and cloud infrastructure. I build secure, high-performance web applications that help businesses grow. [About](https://www.msaied.com/public#about) [GitHub ↗](https://github.com/EG-Mohamed) [LinkedIn ↗](https://www.linkedin.com/in/msaiedm/) [WhatsApp ↗](https://wa.me/201094619204) [Email Address ↗](mailto:hello@msaied.com) [My CV ↗](https://drive.google.com/file/u/0/d/1MF20IPRJyzfy32mhEutjL5EpSls0w2Q8/view) [Previous articleMonitor Laravel Queues, Commands, and Schedulers on Any Driver with Vigilance](https://www.msaied.com/public/articles/monitor-laravel-queues-commands-and-schedulers-on-any-driver-with-vigilance) [Next articlePostgreSQL CTEs, Recursive Queries, and Lateral Joins in Laravel](https://www.msaied.com/public/articles/postgresql-ctes-recursive-queries-and-lateral-joins-in-laravel) 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) ### Have a technical challenge? Tell me what you’re building. I reply within two working days. [Start a conversation](https://www.msaied.com/public#contact) Related articles ----------------- [ ![](https://cdn.msaied.com/740/cce86edc21eddcbdd2f2454fadaf9c70.png) · 3 min read### The Pipeline Pattern in Laravel: Custom Pipelines Beyond Middleware 5 Oct 2026 ](https://www.msaied.com/public/articles/the-pipeline-pattern-in-laravel-custom-pipelines-beyond-middleware-1) [ ![](https://cdn.msaied.com/739/2d6897fdcdcf090613f96f72a64b8a78.png) · 4 min read### MySQL Full-Text Search in Laravel: Indexes, Relevance Scoring, and Boolean Mode 4 Oct 2026 ](https://www.msaied.com/public/articles/mysql-full-text-search-in-laravel-indexes-relevance-scoring-and-boolean-mode) [ ![](https://cdn.msaied.com/738/073696a3fefe18bec825beec5ac658f5.png) · 4 min read### Laravel Queue Rate-Limited Middleware: Throttling Jobs Without Losing Work 4 Oct 2026 ](https://www.msaied.com/public/articles/laravel-queue-rate-limited-middleware-throttling-jobs-without-losing-work) Have a technical challenge? ---------------------------- Tell me what you’re building. I reply within two working days. [Discuss your project ↗](https://www.msaied.com/public#contact) © 2026 Mohamed Said · Built with Laravel, meant to last.Senior Backend Engineer specializing in Laravel, scalable SaaS platforms, APIs, and cloud infrastructure. I build secure, high-performance web applications that help businesses grow. - [Home](https://www.msaied.com/public) - [Articles](https://www.msaied.com/public/articles) - [Certificates](https://www.msaied.com/public/certificates) - [GitHub](https://github.com/EG-Mohamed) - [LinkedIn](https://www.linkedin.com/in/msaiedm/) - [WhatsApp](https://wa.me/201094619204) - [Email Address](mailto:hello@msaied.com) - [My CV](https://drive.google.com/file/u/0/d/1MF20IPRJyzfy32mhEutjL5EpSls0w2Q8/view) - [Sitemap](https://www.msaied.com/public/sitemap.xml)