" %}
## Core
* **`PARTITION BY`:** A sub-clause of the OVER clause. Similar to GROUP BY.
* **`OVER`:** Typically precedes the partition by that signals what to “GROUP BY”.
* **Aggregates:** Aggregate functions that are used in window functions, too (e.g., sum, count, avg).
```sql
AGGREGATE_FUNCTION (column_1) OVER
(PARTITION BY column_2 ORDER BY column_3)
AS new_column_name;
```
{% embed url="" %}
| GROUP BY | PARTITION BY |
| ------------------------------------------------------------------------ | ---------------------------------------------------------------------------- |
| The ouput has a **lessened # of records** based on the `GROUP BY` column | The output **maintains the # of records** in the original table. |
| The output is one row per `GROUP BY` in the result set. | If the original table had 10 rows, the `PARTITION BY` will maintain 10 rows. |
## Ranking
* **`ROW_NUMBER()`:** Ranking is **distinct** amongst records even with ties in what the table is ranked against.
* **`RANK()`:** Ranking function where a row could get the **same** rank if they have the same value and ranks **skip** for subsequent values.
* **`DENSE_RANK()`:** Ranking function similar to `RANK()` but ranks are **not skipped** with ties.
SELECT ROW_NUMBER() OVER(ORDER BY date_time) AS rwrank,
RANK() OVER(ORDER BY date_time) AS rrank,
DENSE_RANK() OVER(ORDER BY date_time) AS drrank,
date_time
FROM db;
## Advanced
* **Aliases:** Shorthand that can be used if there are several window functions in one query.
SELECT order_id,
order_total,
order_price,
SUM(order_total) OVER monthly_window AS running_monthly_sales,
COUNT(order_id) OVER monthly_window AS running_monthly orders,
AVG(order_price) OVER monthly_window AS average_monthly_price
FROM amazon_sales_db
WHERE order_date < '2017-01-01'
WINDOW monthly_window AS
(PARTITION BY month(order_date) ORDER BY order_date);
* **Percentiles:** Defines what percentile a value falls into over the entire table.
```sql
NTILE(# of buckets) OVER (ORDER BY ranking_column) AS new_column_name
```
```sql
SELECT customer_id,
composite_score,
NTILE(100) OVER(ORDER BY composite_score) AS percentile
FROM customer_lead_score;
```
* **Lag/Lead:** Calculating differences between rows’ values.
SELECT account_id,
standard_sum,
LAG(standard_sum) OVER(ORDER BY standard_sum) AS lag,
LEAD(standard_sum) OVER (ORDER BY standard_sum) AS lead,
standard_sum - LAG(standard_sum) OVER (ORDER BY standard_sum) AS lag_diff,
LEAD(standard_sum) OVER (ORDER BY standard_sum) - standard_sum AS lead_diff
FROM (
SELECT account_id,
SUM(standard_qty) AS standard_sum
FROM orders
GROUP BY 1
) sub
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://dailyjournal.gitbook.io/notes/languages/sql/functions/window-functions.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.