# Window Functions

Here are some examples of how to use window functions in BigQuery, along with explanations for each function. A window function allows you to perform calculations across multiple rows of a query result set, based on a specified partition and order, without using a self-join.

# ROW_NUMBER()

ROW_NUMBER() assigns a unique number to each row in a query result set, starting from 1. Here's an example:

SELECT name, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM `mydataset.mytable`

In this example, the ROW_NUMBER function is used to assign a unique number to each row, based on the salary column. The result set is ordered by salary in descending order.

# RANK()

RANK() assigns a unique rank to each row in a query result set, based on the value of a specified column. Rows with the same value receive the same rank, and the next rank is skipped. Here's an example:

SELECT name, salary,
    RANK() OVER (ORDER BY salary DESC) AS rank
FROM `mydataset.mytable`

In this example, the RANK function is used to assign a unique rank to each row, based on the salary column. Rows with the same salary value receive the same rank, and the next rank is skipped.

# DENSE_RANK()

DENSE_RANK() is similar to RANK, but does not skip ranks for tied values. Here's an example:

SELECT name, salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM `mydataset.mytable`

In this example, the DENSE_RANK function is used to assign a unique rank to each row, based on the salary column. Rows with the same salary value receive the same rank, and the next rank is not skipped.

# NTILE(n)

NTILE(n) divides a query result set into n equal parts and assigns a number to each row based on which part it belongs to. Here's an example:

SELECT name, salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS ntile
FROM `mydataset.mytable`

In this example, the NTILE function is used to divide the result set into 4 equal parts (or quartiles) and assign a number to each row based on which quartile it belongs to.

Window functions in BigQuery provide a powerful tool for performing complex data analysis tasks, such as ranking, percentiles, and moving averages. By using these functions, you can gain insights into your data that would lomg-winded or inefficient to obtain using other methods like subselects and self-joins.