Advanced Window Functions for Complex Data Analysis in SQL

Window functions in SQL are powerful tools for performing complex calculations across sets of rows related to the current query row without collapsing the result set. While basic window functions such as ROW_NUMBER() are well-known, advanced window functions open up new possibilities for data analysis.

In this tutorial, we will explore some advanced window functions including RANK(), DENSE_RANK(), LEAD(), LAG(), and NTILE(), demonstrating how to use them effectively for complex data analysis scenarios.

Let's start with RANK() and DENSE_RANK(). Both functions assign ranks to rows within a partition of data, but they handle ties differently.

SELECT
  employee_id,
  department,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_salary_rank
FROM employees;

Here, employees in each department are ranked based on their salary. RANK() leaves gaps when there are ties, whereas DENSE_RANK() does not.

Next, LEAD() and LAG() let you access data from subsequent or previous rows without joining the table to itself.

SELECT
  employee_id,
  department,
  salary,
  LAG(salary) OVER (PARTITION BY department ORDER BY salary) AS previous_salary,
  LEAD(salary) OVER (PARTITION BY department ORDER BY salary) AS next_salary
FROM employees;

The query retrieves the salary of the previous and next employee in each department ordered by their salary. This is useful for comparing rows within a partition.

Finally, NTILE() divides rows into a specified number of roughly equal groups, which can be useful for percentile analysis.

SELECT
  employee_id,
  department,
  salary,
  NTILE(4) OVER (PARTITION BY department ORDER BY salary) AS quartile
FROM employees;

This example divides employees in each department into 4 salary quartiles, helping to identify top or bottom segments.

By combining these advanced window functions, you can perform sophisticated analyses directly in SQL, improving query efficiency and clarity.

Try these techniques on your datasets to discover powerful insights without complicated joins or subqueries!