# Day 46: A Guide to ClickHouse® Window Functions

# Day 46: A Guide to ClickHouse® Window Functions

## Introduction

Analyzing data often requires comparing rows, calculating rankings, finding running totals, or computing moving averages without changing the number of rows returned. Traditionally, these tasks required complex self-joins or nested subqueries, making SQL queries harder to write and maintain.

ClickHouse® Window Functions simplify these analytical operations by allowing calculations across a group of related rows while preserving every row in the result set. This enables cleaner SQL, better readability, and more powerful analytics.

In this guide, you'll learn what window functions are, how the `OVER()` clause works, and how to use the most common window functions in ClickHouse® with practical examples.

---

# What Are Window Functions?

A window function performs calculations over a set of rows called a **window**.

Unlike `GROUP BY`, which combines multiple rows into a single result, window functions calculate values while keeping every original row.

Consider the following employee salary data:

| Employee | Department | Salary |
| -------- | ---------- | -----: |
| Alice    | HR         | 50,000 |
| Bob      | HR         | 65,000 |
| Charlie  | HR         | 55,000 |

Using `GROUP BY`, you can calculate the average salary for HR, but the result contains only one row.

With a window function, every employee remains visible while also displaying the department's average salary.

| Employee | Salary | Department Average |
| -------- | -----: | -----------------: |
| Alice    | 50,000 |             56,666 |
| Bob      | 65,000 |             56,666 |
| Charlie  | 55,000 |             56,666 |

This ability to retain individual rows while performing analytical calculations is what makes window functions so powerful.

---

# Basic Syntax

Every window function uses the `OVER()` clause.

```sql
SELECT
    column_name,
    window_function() OVER (
        PARTITION BY column
        ORDER BY column
    )
FROM table_name;
```

The `OVER()` clause defines how the window is created.

It can specify:

* How rows are grouped (`PARTITION BY`)
* How rows are ordered (`ORDER BY`)
* Which rows belong to the window (`ROWS BETWEEN`)

---

# Sample Table

We'll use the following table throughout the examples.

```sql
CREATE TABLE employee_sales
(
    Employee String,
    Department String,
    SaleDate Date,
    Sales UInt32
)
ENGINE = MergeTree
ORDER BY SaleDate;
```

Sample data:

| Employee | Department  | SaleDate   | Sales |
| -------- | ----------- | ---------- | ----: |
| Alice    | Electronics | 2025-01-01 |   120 |
| Bob      | Electronics | 2025-01-02 |   150 |
| Charlie  | Electronics | 2025-01-03 |   180 |
| David    | Furniture   | 2025-01-01 |    90 |
| Emma     | Furniture   | 2025-01-01 |   110 |

---

# Ranking Functions

## ROW_NUMBER()

Assigns a unique sequential number to every row.

```sql
SELECT
    Employee,
    Sales,
    ROW_NUMBER() OVER (ORDER BY Sales DESC) AS RowNum
FROM employee_sales;
```

Output:

| Employee | Sales | RowNum |
| -------- | ----: | -----: |
| Charlie  |   180 |      1 |
| Bob      |   150 |      2 |
| Alice    |   120 |      3 |
| Emma     |   110 |      4 |

### Common Use Cases

* Ranking reports
* Pagination
* Top-N queries

---

## RANK()

Assigns the same rank to equal values.

```sql
SELECT
    Employee,
    Sales,
    RANK() OVER (ORDER BY Sales DESC) AS Rank
FROM employee_sales;
```

Output:

| Employee | Sales | Rank |
| -------- | ----: | ---: |
| Alice    |   200 |    1 |
| Bob      |   180 |    2 |
| Charlie  |   180 |    2 |
| David    |   150 |    4 |

Notice that **Rank 3 is skipped** because two employees share Rank 2.

---

## DENSE_RANK()

Works like `RANK()`, but does not leave gaps.

```sql
SELECT
    Employee,
    Sales,
    DENSE_RANK() OVER (ORDER BY Sales DESC) AS DenseRank
FROM employee_sales;
```

Output:

| Employee | Sales | DenseRank |
| -------- | ----: | --------: |
| Alice    |   200 |         1 |
| Bob      |   180 |         2 |
| Charlie  |   180 |         2 |
| David    |   150 |         3 |

---

## NTILE()

Splits ordered rows into approximately equal-sized buckets.

```sql
SELECT
    Employee,
    Sales,
    NTILE(2) OVER (ORDER BY Sales DESC) AS SalesGroup
FROM employee_sales;
```

Output:

| Employee | Sales | SalesGroup |
| -------- | ----: | ---------: |
| Charlie  |   180 |          1 |
| Bob      |   150 |          1 |
| Alice    |   120 |          2 |
| David    |    90 |          2 |

### Common Use Cases

* Customer segmentation
* Sales quartiles
* Performance grading
* Ranking analysis

---

# Aggregate Window Functions

Traditional aggregate functions such as `SUM()`, `AVG()`, `MIN()`, `MAX()`, and `COUNT()` can also operate as window functions.

---

## SUM() OVER()

Calculates running totals.

```sql
SELECT
    SaleDate,
    Sales,
    SUM(Sales)
    OVER (ORDER BY SaleDate) AS RunningTotal
FROM employee_sales;
```

Output:

| SaleDate | Sales | RunningTotal |
| -------- | ----: | -----------: |
| Jan 1    |   120 |          120 |
| Jan 2    |   150 |          270 |
| Jan 3    |   180 |          450 |

Ideal for cumulative sales reports.

---

## AVG() OVER()

Calculates averages without collapsing rows.

```sql
SELECT
    Employee,
    Department,
    Sales,
    AVG(Sales)
    OVER (PARTITION BY Department) AS DepartmentAverage
FROM employee_sales;
```

Output:

| Employee | Department  | Sales | DepartmentAverage |
| -------- | ----------- | ----: | ----------------: |
| Alice    | Electronics |   120 |               150 |
| Bob      | Electronics |   150 |               150 |
| Charlie  | Electronics |   180 |               150 |

---

## Moving Average

Moving averages become very simple with window functions.

```sql
SELECT
    SaleDate,
    Sales,
    AVG(Sales)
    OVER (
        ORDER BY SaleDate
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS MovingAverage
FROM employee_sales;
```

Output:

| SaleDate | Sales | MovingAverage |
| -------- | ----: | ------------: |
| Jan 1    |   120 |           120 |
| Jan 2    |   150 |           135 |
| Jan 3    |   180 |           150 |

The calculation includes:

* Current row
* Previous row
* Two rows before the current row

---

# Understanding Window Frames

Window frames determine which rows participate in the calculation.

Example:

```sql
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
```

This means:

* **2 PRECEDING** → Include the previous two rows.
* **CURRENT ROW** → Include the current row.

Window frames are commonly used for:

* Moving averages
* Rolling sums
* Sliding calculations
* Time-series analytics

---

# Navigation Functions

## LAG()

Returns the value from the previous row.

```sql
SELECT
    SaleDate,
    Sales,
    LAG(Sales)
    OVER (ORDER BY SaleDate) AS PreviousSales
FROM employee_sales;
```

Output:

| SaleDate | Sales | PreviousSales |
| -------- | ----: | ------------: |
| Jan 1    |   120 |          NULL |
| Jan 2    |   150 |           120 |
| Jan 3    |   180 |           150 |

Useful for day-over-day comparisons.

---

## LEAD()

Returns the value from the next row.

```sql
SELECT
    SaleDate,
    Sales,
    LEAD(Sales)
    OVER (ORDER BY SaleDate) AS NextSales
FROM employee_sales;
```

Output:

| SaleDate | Sales | NextSales |
| -------- | ----: | --------: |
| Jan 1    |   120 |       150 |
| Jan 2    |   150 |       180 |
| Jan 3    |   180 |      NULL |

Useful for forecasting and sequential analysis.

---

## FIRST_VALUE() and LAST_VALUE()

Retrieve the first or last value from the window.

```sql
SELECT
    Employee,
    Sales,
    FIRST_VALUE(Sales)
    OVER (
        ORDER BY Sales DESC
    ) AS HighestSale,
    LAST_VALUE(Sales)
    OVER (
        ORDER BY Sales
        ROWS BETWEEN UNBOUNDED PRECEDING
        AND UNBOUNDED FOLLOWING
    ) AS LastSale
FROM employee_sales;
```

Output:

| Employee | Sales | HighestSale | LastSale |
| -------- | ----: | ----------: | -------: |
| Charlie  |   180 |         180 |      180 |
| Bob      |   150 |         180 |      180 |
| Alice    |   120 |         180 |      180 |

Here:

* `FIRST_VALUE()` returns the highest sales value after sorting in descending order.
* `LAST_VALUE()` returns the final value across the complete window.

---

# Window Functions vs GROUP BY

| GROUP BY                               | Window Functions                                              |
| -------------------------------------- | ------------------------------------------------------------- |
| Combines rows                          | Keeps all rows                                                |
| One result per group                   | One result per row                                            |
| Used for summaries                     | Used for row-level analytics                                  |
| Cannot easily compare neighboring rows | Easily compares previous, next, ranked, and cumulative values |

---

# Performance Best Practices

For better performance when using window functions in ClickHouse®:

* Choose an efficient `ORDER BY` key.
* Use `PARTITION BY` only when necessary.
* Filter rows before applying window functions whenever possible.
* Keep window frames as small as practical.
* Use `MergeTree` tables for analytical workloads.
* Avoid unnecessary sorting on very large datasets.

---

# Conclusion

Window Functions are one of the most powerful SQL features available in ClickHouse® for analytical workloads. They allow you to perform rankings, running totals, moving averages, cumulative calculations, and row-to-row comparisons while preserving every row in the result set.

By understanding the `OVER()` clause and mastering functions such as `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `NTILE()`, `SUM()`, `AVG()`, `LAG()`, `LEAD()`, `FIRST_VALUE()`, and `LAST_VALUE()`, you can write cleaner SQL and solve complex analytical problems without relying on complicated joins or subqueries.

For anyone working with ClickHouse®, mastering window functions is an essential skill for building efficient, readable, and high-performance analytical queries.