Lessons learnt from the 8 week sql challenge: window functions & ranking
Tags: sql, bigquery, 8weeksqlchallenge
Published: 2022-05-04 19:40:57

> 👩🏼‍💻 using [Google BigQuery SQL syntax](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax)
📁 check the [Github repository](https://github.com/meerens/8-week-sqlchallenge) for context

For the longest time I've had a love/hate relationship with WINDOW functions in SQL but recently had a moment where I finally started seeing some of the light.
 
`DENSE_RANK` `RANK` and `ROW_NUMBER` are amongst the more powerful WINDOW functions and it's important to understand how they behave differently so you make the right choice for your use case.

Let's take a closer look at an **example** to help frame things up. 

The query below ...

```sql
SELECT 
s.customer_id,
s.order_date,
m.product_name, 
ROW_NUMBER () OVER (PARTITION 
  BY customer_id ORDER BY order_date ASC) AS row_number,
RANK () OVER (PARTITION 
  BY customer_id ORDER BY order_date ASC) AS rank,
DENSE_RANK () OVER(PARTITION 
  BY customer_id ORDER BY order_date ASC) AS dense_rank
FROM sqlchallenge_week1.sales AS s
  JOIN sqlchallenge_week1.menu AS m
    ON s.product_id = m.product_id
ORDER BY customer_id;
```
... will generate the output below.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gq4el9ogvugy5wnq1hye.png)
 
Can you tell from the image above **how a WINDOW function actually behaves** and how it's different from a standard AGGREGATE function?

1. WINDOW functions create a partition of the table based on a set of parameters. In our the case the partition is created for every customer id, the records in that partition are sorted by order date ascending.
2. Next we're calling a function on that partition where for every row in a value is generated that will go into a new column that gets added to the table. 

When you look at the example in our table you'll notice that results are not grouped meaning the same amount of rows (14) went in as came out. And that's how [WINDOW functions](https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts) (also called analytical functions) are different from [AGGREGATE functions](https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions). In case of our example an AGGREGATE function like COUNT() would have grouped all the rows per customer id to then count the rows in each group and return a single value per customer id resulting in the output below.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x0lazhouccvpkh7jhl05.png)

Now let's go back to our WINDOW functions and take a look at  how `RANK`, `DENSE_RANK` and `ROW_NUMBER` are different from each other.

[ROW_NUMBER](https://cloud.google.com/bigquery/docs/reference/standard-sql/numbering_functions#row_number)
Every row in the partition or window gets assigned a number which starts at 1 being the first row in the ordered partition and so on. You'll have noticed every row in the window is assigned a unique number and the order of those numbers is sequential with increments of 1.

[RANK](https://cloud.google.com/bigquery/docs/reference/standard-sql/numbering_functions#rank)
When assigning a number to each row in the partition or window we follow the same logic as ROW_NUMBER. The only difference being that rows with the same order date receive the same rank number, and the rank number for every row in the next subset isn't incremented by 1 but 1 + number of rows in the previous subset.

[DENSE_RANK](https://cloud.google.com/bigquery/docs/reference/standard-sql/numbering_functions#dense_rank)
For DENSE_RANK every row in our subset in the partition gets assigned a unique number which is incremented by 1 for next subset.

Hope you find this useful, let me know in the comments if any questions.

📚 For a broader view on windows functions **I can recommend the following blog posts**:

[An Easy Guide to Advanced SQL Window Functions](https://towardsdatascience.com/a-guide-to-advanced-sql-window-functions-f63f2642cbf9)

[Take your SQL from Good to Great: Part 4 (Window Functions)](https://blog.count.co/take-your-sql-from-good-to-great-part-4/)