Window functions in SQL allow you to perform calculations across a set of rows related to the current row without collapsing the result set. Unlike GROUP BY, which aggregates rows into a single output, window functions retain individual rows while adding calculated values.
Ranking functions are a subset of window functions that assign ranks to rows based on specific conditions, making them useful for scenarios like leaderboards, top performers, and percentile calculations.
A window function operates over a partition (subset) of data while keeping all original rows in the result. The OVER() clause defines how the function is applied by specifying:
window_function() OVER (
PARTITION BY column_name
ORDER BY column_name
)Ranking functions assign a unique rank to each row within a partition based on the ORDER BY clause.
SELECT employee_id, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;RANK(), but does not skip numbers for ties.SELECT employee_id, department, salary,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;RANK(), DENSE_RANK() does not create gaps in ranking.SELECT employee_id, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;n equal parts and assigns a group number.SELECT employee_id, department, salary,
NTILE(4) OVER (PARTITION BY department ORDER BY salary DESC) AS quartile
FROM employees;Unlike standard aggregate functions, window aggregates retain individual rows while computing cumulative values.
SELECT order_id, customer_id, order_amount,
SUM(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total
FROM orders;order_amount per customer.SELECT order_id, customer_id, order_amount,
AVG(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM orders;SELECT department, employee_id,
COUNT(*) OVER (PARTITION BY department) AS dept_employee_count
FROM employees;SELECT employee_id, department, salary,
LAG(salary, 1) OVER (PARTITION BY department ORDER BY salary DESC) AS previous_salary
FROM employees;SELECT employee_id, department, salary,
LEAD(salary, 1) OVER (PARTITION BY department ORDER BY salary DESC) AS next_salary
FROM employees;Suppose we want to rank sales representatives by total sales in each region.
SELECT region, sales_rep, total_sales,
RANK() OVER (PARTITION BY region ORDER BY total_sales DESC) AS rank
FROM sales;| Feature | Window Functions | GROUP BY |
|---|---|---|
| Retains individual rows | Yes | No |
| Performs row-wise calculations | Yes | No |
| Aggregates data | Yes | Yes |
| Requires ORDER BY | Often | Not always |
Use window functions when you need both aggregated and detailed data in the same query.
Window functions provide advanced analytical capabilities in SQL without losing row-level details. They are essential for ranking, running totals, moving averages, and trend analysis. Understanding and using them efficiently can significantly improve query performance and reporting accuracy.
Sign in to join the discussion and post comments.
Sign in