# 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:

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

#### 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="https://www.postgresql.org/docs/current/tutorial-window.html" %}

{% embed url="https://www.postgresql.org/docs/current/functions-window.html" %}

## 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="https://blog.sqlauthority.com/2015/11/04/sql-server-what-is-the-over-clause-notes-from-the-field-101/" %}

| 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.

<pre class="language-sql"><code class="lang-sql"><strong>SELECT ROW_NUMBER() OVER(ORDER BY date_time) AS rwrank,
</strong><strong>       RANK() OVER(ORDER BY date_time) AS rrank,
</strong><strong>       DENSE_RANK() OVER(ORDER BY date_time) AS drrank,
</strong>       date_time
FROM   db;
</code></pre>

## Advanced

* **Aliases:** Shorthand that can be used if there are several window functions in one query.

<pre class="language-sql"><code class="lang-sql">SELECT order_id,
       order_total,
       order_price,
<strong>       SUM(order_total) OVER monthly_window AS running_monthly_sales,
</strong><strong>       COUNT(order_id) OVER monthly_window AS running_monthly orders,
</strong><strong>       AVG(order_price) OVER monthly_window AS average_monthly_price
</strong>FROM   amazon_sales_db
WHERE  order_date &#x3C; '2017-01-01'
<strong>WINDOW monthly_window AS
</strong><strong>       (PARTITION BY month(order_date) ORDER BY order_date);
</strong></code></pre>

* **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.

<pre class="language-sql"><code class="lang-sql">SELECT account_id,
       standard_sum,
<strong>       LAG(standard_sum) OVER(ORDER BY standard_sum) AS lag,
</strong><strong>       LEAD(standard_sum) OVER (ORDER BY standard_sum) AS lead,
</strong><strong>       standard_sum - LAG(standard_sum) OVER (ORDER BY standard_sum) AS lag_diff,
</strong><strong>       LEAD(standard_sum) OVER (ORDER BY standard_sum) - standard_sum AS lead_diff
</strong>FROM (
       SELECT account_id,
              SUM(standard_qty) AS standard_sum
       FROM orders
       GROUP BY 1
) sub
</code></pre>
