# Window Functions Every Data Engineer Uses

> Deduplication, latest-record-per-key, running totals and gaps — the window patterns that show up in every pipeline and interview.

- Source: https://akshaythakare.com/notes/sql/window-functions
- Topic: SQL
- Level: beginner
- Reading time: 3 min
- Tags: sql, window-functions, interview
- Updated: 2026-10-11

## The anatomy

```sql
function_name(...) OVER (
  PARTITION BY <group>      -- restart the calculation per group
  ORDER BY <sequence>       -- order inside the group
  ROWS BETWEEN ...          -- optional frame
)
```

Unlike `GROUP BY`, a window function **keeps every row** and adds a computed column.

## Pattern 1 — latest record per key (deduplication)

```sql
SELECT *
FROM (
  SELECT c.*,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn
  FROM raw_customers c
) t
WHERE rn = 1;
```

Snowflake, BigQuery and Databricks support `QUALIFY`, which avoids the subquery:

```sql
SELECT * FROM raw_customers
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1;
```

> **Warning: Ties are a data-quality question**
>
> If two rows share the same key **and** the same `updated_at` but differ in content, `ROW_NUMBER` picks one arbitrarily. Detect that case and fail or quarantine instead.

## Pattern 2 — ROW_NUMBER vs RANK vs DENSE_RANK

| value | ROW_NUMBER | RANK | DENSE_RANK |
| --- | --- | --- | --- |
| 100 | 1 | 1 | 1 |
| 100 | 2 | 1 | 1 |
| 90 | 3 | 3 | 2 |

## Pattern 3 — running totals and moving averages

```sql
SELECT order_date,
       daily_sales,
       SUM(daily_sales) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS running_total,
       AVG(daily_sales) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d
FROM daily_sales;
```

Use `ROWS` (physical rows) rather than the default `RANGE` frame when dates can repeat.

## Pattern 4 — change detection with LAG

```sql
SELECT customer_id, loyalty_tier, observed_at,
       LAG(loyalty_tier) OVER (PARTITION BY customer_id ORDER BY observed_at) AS previous_tier
FROM customer_observations;
-- rows where loyalty_tier <> previous_tier are tier changes (the basis of SCD2)
```