---
title: "window functions"
description: "Window functions allow you to perform calculations across a set of table rows that are somehow related to the current row, similar to aggregate functions,…"
canonical: "https://motherduck.com/glossary/window-functions/"
related:
  - title: "Window functions | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/window-functions/"
  - title: "MotherDuck Window Functions"
    url: "https://motherduck.com/blog/motherduck-window-functions-in-sql/"
  - title: "DuckDB SQL | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/"
---

# window functions

> Window functions allow you to perform calculations across a set of table rows that are somehow related to the current row, similar to aggregate functions,…

## Overview

Window functions allow you to perform calculations across a set of table rows that are somehow related to the current row, similar to aggregate functions, but without grouping the rows into a single output row. This lets you compare and analyze values while maintaining the individual rows of your data.

<glossary-callout guide="duckdb-cheatsheet-full" />

## Key Concepts

A window function operates on a "window" - a set of rows determined by the `OVER` clause. The window can be defined using `PARTITION BY` to group rows, `ORDER BY` to sequence them, and optional frame clauses to limit which rows are included in the calculation.

## Common Use Cases

Window functions excel at calculating running totals, finding ranks within groups, comparing values to previous/next rows, and computing moving averages. They're especially valuable for time-series analysis and financial calculations.

## DuckDB Examples

Basic ranking example:
```sql
SELECT 
  product_name,
  sales_amount,
  RANK() OVER (ORDER BY sales_amount DESC) as sales_rank
FROM sales;
```

Partitioned ranking by category:
```sql
SELECT 
  category,
  product_name,
  sales_amount,
  RANK() OVER (PARTITION BY category ORDER BY sales_amount DESC) as category_rank
FROM sales;
```

Running total with frame clause:
```sql
SELECT 
  date,
  amount,
  SUM(amount) OVER (
    ORDER BY date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) as running_total
FROM transactions;
```

Moving average:
```sql
SELECT 
  date,
  temperature,
  AVG(temperature) OVER (
    ORDER BY date 
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) as moving_avg_3day
FROM weather_data;
```

## DuckDB-Specific Features

DuckDB supports named windows, which can make complex window function queries more readable:
```sql
SELECT 
  date,
  amount,
  SUM(amount) OVER w1 as running_total,
  AVG(amount) OVER w1 as running_avg
FROM transactions
WINDOW w1 AS (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
```

DuckDB also allows window functions to be used in `UPDATE` statements and supports a wide range of window frame specifications, including `GROUPS` mode alongside the standard `ROWS` and `RANGE` modes.