SQL Window Functions: Syntax, Usage, and Examples

SQL window functions let you perform calculations across a specific range of rows related to the current row. Unlike aggregate functions, which collapse multiple rows into a single result, window functions keep all rows while applying calculations based on a defined window.

How to Use SQL Window Functions

A window function works with the OVER() clause, which defines how it processes a set of rows.

Basic Syntax

SELECT column_name,
       window_function() OVER (PARTITION BY partition_column ORDER BY sort_column) AS alias
FROM table_name;

• window_function(): The function that performs the calculation.
• OVER(): Specifies how the function is applied.
• PARTITION BY: (Optional) Divides rows into groups before applying the function.
• ORDER BY: (Optional) Sorts rows within each partition.

Example: Assigning Row Numbers

If you want to rank orders per customer based on order date, you can use ROW_NUMBER():

SELECT customer_id, order_date,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_rank
FROM orders;

Each order gets a unique ranking within its customer group, based on the order date.

When to Use SQL Window Functions

Window functions help when you need rankings, running totals, or comparisons between rows—without removing details from your dataset.

Ranking Rows

Use window functions to assign rankings while keeping all records.

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

This ranks employees based on salary within their department.

Tracking Running Totals

If you need cumulative sums or moving averages, window functions do it efficiently.

SELECT customer_id, order_date, amount,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total
FROM orders;

This calculates the running total of orders for each customer.

Comparing Values Across Rows

With offset functions like LAG(), you can compare the current row to previous ones.

SELECT product_id, sale_date, quantity_sold,
       LAG(quantity_sold) OVER (PARTITION BY product_id ORDER BY sale_date) AS previous_day_sales
FROM sales;

This retrieves the quantity sold on the previous day for each product.

Examples of SQL Window Functions

Finding the Top N Rows per Group

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

This assigns a rank while ensuring employees with the same salary get the same ranking, without gaps.

Calculating Moving Averages

SELECT product_id, sale_date, price,
       AVG(price) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM sales;

Here, each row shows the average price over a three-row window.

Finding Percentage Contribution

SELECT department, employee_id, salary,
       salary * 1.0 / SUM(salary) OVER (PARTITION BY department) AS salary_percentage
FROM employees;

Each employee’s salary is expressed as a percentage of their department’s total.

Learn More About SQL Window Functions

Common SQL Window Functions

You can use several types of window functions depending on your needs.

• Ranking Functions
  • ROW_NUMBER(): Assigns a unique row number.
  • RANK(): Assigns a rank but skips numbers when ties occur.
  • DENSE_RANK(): Similar to RANK(), but without gaps.
• Aggregate Window Functions
  • SUM(), AVG(), COUNT(), MIN(), MAX(): Perform calculations over a defined window.
• Offset Functions
  • LAG(): Retrieves the value from the previous row.
  • LEAD(): Retrieves the value from the next row.
• Cumulative Functions
  • FIRST_VALUE(): Returns the first value in the partition.
  • LAST_VALUE(): Returns the last value in the partition.
  • NTILE(n): Divides rows into n groups.

Using Frames in Window Functions

The ROWS BETWEEN clause lets you control how many rows the function considers.

SELECT order_id, order_date, amount,
       SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS rolling_sum
FROM orders;

This calculates a rolling sum that includes the last three rows.

Advanced Window Function Use Cases

Calculating Retention Rates

SELECT user_id, login_date,
       LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS previous_login,
       DATEDIFF(day, LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date), login_date) AS days_since_last_login
FROM user_logins;

This helps track how often users return.

Identifying First Purchases

SELECT customer_id, purchase_date, amount,
       FIRST_VALUE(purchase_date) OVER (PARTITION BY customer_id ORDER BY purchase_date) AS first_purchase_date
FROM purchases;

This extracts the first purchase date for each customer.