# R2 SQL Now Speaks Window Functions, DISTINCT, and Set Operations!

Hi everyone, it's me, Shiichan! Today R2 SQL got an update that analytics fans are going to love.

## What was announced?

According to Cloudflare's Changelog, the serverless SQL engine [R2 SQL](https://developers.cloudflare.com/r2-sql/) now supports window functions, DISTINCT, and set operations. You can write these analytical queries directly against [Apache Iceberg](https://iceberg.apache.org/) tables, with no external preprocessing.

## The story so far

R2 SQL could already query Iceberg data, but going a step further — ranking rows, dropping duplicates, or combining and subtracting two result sets — often meant reaching for another tool or a preprocessing step. With this update, those bread-and-butter queries now run entirely inside R2 SQL.

## What changes

Aggregations like a per-region sales ranking now fit into a single SQL statement. Here's a window function example:

```sql
SELECT customer_id, region,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY total_amount DESC) AS rank_in_region
FROM my_namespace.sales_data
```

When you want the difference between two tables, the EXCEPT set operation comes in handy:

```sql
SELECT customer_id FROM my_namespace.sales_data
EXCEPT
SELECT customer_id FROM my_namespace.archived_sales
```

## Dive Deep

Here's everything that landed:

- Window functions: ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE
- The QUALIFY clause for filtering window function results
- SELECT DISTINCT and DISTINCT ON (...) syntax
- Set operations: UNION, UNION ALL, INTERSECT, EXCEPT
- Grouping extensions: GROUPING SETS, ROLLUP, CUBE
- Exact aggregates: MEDIAN, PERCENTILE_CONT, ARRAY_AGG, STRING_AGG

The full syntax is covered in the [SQL reference](https://developers.cloudflare.com/r2-sql/sql-reference/), and it's worth reading the [limitations and best practices](https://developers.cloudflare.com/r2-sql/reference/limitations-best-practices/) before you write your queries. For where the data lives, pair it with the [R2 Data Catalog](https://developers.cloudflare.com/r2/data-catalog/).

## Wrap-up

- R2 SQL now supports window functions, DISTINCT, and set operations
- QUALIFY, GROUPING SETS, ROLLUP, CUBE, and exact aggregates like MEDIAN were added too
- You can write analytical queries directly against Apache Iceberg tables with no preprocessing

If you keep data in R2 and want to analyze it in place — or trim down external transformation pipelines — this one's for you.