> For the complete documentation index, see [llms.txt](https://dailyjournal.gitbook.io/notes/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://dailyjournal.gitbook.io/notes/languages/sql/functions/window-functions.md). # Window Functions #### When do you use window functions over aggregate functions? 1. When you want to measure trends or changes over rows. 2. When you want to rank a column to use for outreach/prioritization. #### Example:

Problem	Window Function
Has the average price of airline tickets gone up this year?	Calculate a running average of ticket prices partitioned by the month and ordered by time
What’s the best way to keep the running total orders of customers?	Calculate the running count of orders and maintain it as a separate row within the table
Use a combination of factors to rank companies most likely to need a loan.	Include a rank column in the output to be used by a BD rep to reach out to potential customers.

#### What is window function? **Window Function:** A window function is a calculation across a set of rows in a table that are somehow related to the current row. This means we’re typically: 1. Calculating running totals that incorporate the current row or, 2. Ranking records across rows, inclusive of the current one A window function is similar to aggregate functions combined with GROUP `BY` clauses but have one key difference: **Window functions retain the total number of rows between the input table and the output table (or result).** Behind the scenes, the window function is able to access more than just the current row of the query result. > a window function allows users to compare one row to another without doing any joins. When window functions are used, you’ll notice new column names like the following: * Average running price * Running total orders * Running sum sales * Rank * Percentile {% embed url="" %} {% embed url="" %} ## 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