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) - [Home](https://www.msaied.com)
- [Projects](https://www.msaied.com/projects)
- [Articles](https://www.msaied.com/articles)
- [Certificates](https://www.msaied.com/certificates)
- [About](https://www.msaied.com#about)

           [  Contact](https://www.msaied.com#contact) Menu 

Menu
----

Close 

 - [HomeStart here](https://www.msaied.com)
- [ProjectsCase studies](https://www.msaied.com/projects)
- [ArticlesEngineering notes](https://www.msaied.com/articles)
- [CertificatesCredentials](https://www.msaied.com/certificates)
- [AboutHow I work](https://www.msaied.com#about)
- [ContactGet in touch](https://www.msaied.com#contact)

  [Start a conversation](https://www.msaied.com#contact) [WhatsApp](https://wa.me/201094619204) [Email](mailto:hello@msaied.com) 

 1. [Home](https://www.msaied.com)
2. /
3. [Articles](https://www.msaied.com/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.

 ![](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) [Mohamed Said](https://www.msaied.com#person) Published 17 Oct 2026 · Updated 17 Oct 2026 · 2 min read

ShareCopy linkCopied

 ![PostgreSQL Window Functions in Laravel: Ranking, Running Totals, and Gap Detection](https://cdn.msaied.com/790/3b305e067cf21c59b93a26f96786fabd.png) 

  On this page +1. [Why Window Functions Belong in Your Laravel Toolkit](#why-window-functions-belong-in-your-laravel-toolkit)
2. [Anatomy of a Window Function](#anatomy-of-a-window-function)
3. [Pattern 1 — Ranking Within a Partition](#pattern-1-ranking-within-a-partition)
4. [Pattern 2 — Running Totals](#pattern-2-running-totals)
5. [Pattern 3 — Gap Detection with LAG](#pattern-3-gap-detection-with-lag)
6. [Encapsulating Window Expressions](#encapsulating-window-expressions)
7. [Key Takeaways](#key-takeaways)

 Why Window Functions Belong in Your Laravel Toolkit
---------------------------------------------------

Most Laravel developers reach for PHP collections the moment a query needs ranking or cumulative math. That works at small scale, but once you're aggregating thousands of rows the round-trip cost — fetching raw data, hydrating models, iterating — becomes the bottleneck. PostgreSQL window functions execute the aggregation inside the database, return only the shaped result set, and keep your application layer thin.

This article focuses on three practical patterns: **ranking within a partition**, **running totals**, and **detecting gaps in sequential data**.

---

Anatomy of a Window Function
----------------------------

A window function adds `OVER (...)` to an aggregate or ranking function. The `OVER` clause defines the *window* — the subset of rows the function sees for each output row.

```sql
SELECT
    user_id,
    amount,
    SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS running_total
FROM orders;

```

Unlike `GROUP BY`, every source row is preserved in the output. That distinction is what makes running totals and rankings possible.

---

Pattern 1 — Ranking Within a Partition
--------------------------------------

Suppose you need the top-3 orders per customer for a leaderboard. `RANK()` (or `DENSE_RANK()` for no gaps) handles this cleanly.

```php
use Illuminate\Support\Facades\DB;

$ranked = DB::table('orders')
    ->select([
        'user_id',
        'id as order_id',
        'amount',
        DB::raw(
            'RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rank'
        ),
    ])
    ->get();

$topThree = $ranked->where('rank', 'select([
            'user_id',
            'ends_at',
            DB::raw(
                'LEAD(starts_at) OVER (PARTITION BY user_id ORDER BY starts_at) AS next_start'
            ),
        ]),
    'sub'
)
->whereRaw('next_start > ends_at + INTERVAL \'1 day\'')
->select(['user_id', 'ends_at', 'next_start'])
->get();

```

Rows where `next_start` is more than one day after `ends_at` represent coverage gaps — no PHP loop required.

---

Encapsulating Window Expressions
--------------------------------

Raw strings scattered across controllers are hard to test. Extract them into dedicated query classes or repository methods:

```php
class OrderRepository
{
    public function topPerCustomer(int $limit = 3): \Illuminate\Support\Collection
    {
        return DB::table(
            DB::table('orders')
                ->select([
                    'user_id', 'id', 'amount',
                    DB::raw('RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rnk'),
                ]),
            'ranked'
        )->where('rnk', '