Main Window Functions for Analytics

Before we start, imagine you're working with a table of thousands of sales rows. Your task: figure out who's the number one seller in each category, who's second, and so on. Or maybe you need to number all the rows in your selection to keep track of the order. All of this is super easy with window functions.

Window functions are SQL functions that work with a subset of rows (let's call it a "window") from your dataset. Unlike aggregate functions, which squash rows into one (like SUM() or AVG()), window functions leave the rows untouched, just adding calculated values to them.

Difference from Aggregate Functions

Aggregate functions "squeeze" data by grouping rows:

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

Result: just a couple of rows, one for each department.

Compare that to a window function — here, the rows stay put, but you get a new field, like ROW_NUMBER():

SELECT employee_name, department,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_within_department
FROM employees;

Here you'll get all the same rows, but with an extra column rank_within_department, where each employee gets a number within their department.

Main Window Functions

Syntax of OVER()

The most important part of every window function is the magic word OVER(). It defines exactly what "window" of data the function will work with. Inside OVER() you can set grouping (PARTITION BY) and/or sort order (ORDER BY).

General syntax:

<window_function>() OVER (
   [PARTITION BY <group>] 
   [ORDER BY <order>]
)

Components:

• PARTITION BY: Splits rows into groups. For example, "split data by departments".
• ORDER BY: Sets the order for processing rows. For example, "sort employees by salary from highest to lowest".

The ROW_NUMBER() Function

The ROW_NUMBER() function numbers rows starting from 1 inside the specified "window". Sometimes it's handy for just creating a row number in a temp table or figuring out the order of a record.

Example. Table sales:

Query:

SELECT seller_name, product_category, revenue,
       ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS row_number
FROM sales;

Result:

How it works:

1. Data is split into groups by product_category.
2. Each group is sorted by revenue (descending).
3. Rows inside each group get a sequential number.

The RANK() Function

The RANK() function is used for ranking rows. Unlike ROW_NUMBER(), it takes into account identical values and skips numbers (ranks) if there are ties.

Example:

SELECT seller_name, product_category, revenue,
       RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS rank
FROM sales;

Result:

The DENSE_RANK() Function

DENSE_RANK() is similar to RANK(), except for one thing: it doesn't skip rank numbers if there are ties.

Example. Let's add a sale with the same revenue:

Query:

SELECT seller_name, product_category, revenue,
       DENSE_RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS dense_rank
FROM sales;

Result:

Usage Examples: Row Numbering

Task: number all orders in the orders table, sorted by date.

SELECT order_id, customer_name, order_date,
       ROW_NUMBER() OVER (ORDER BY order_date) AS order_number
FROM orders;

Result: you get a list of orders numbered in the order they were made.

Usage Examples: Top-3 Sellers in Each Category

Task: find the top three sellers in each product category.

WITH ranked_sales AS (
    SELECT seller_name, product_category, revenue,
           RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS rank
    FROM sales
)
SELECT seller_name, product_category, revenue
FROM ranked_sales
WHERE rank <= 3;

Usage Examples: Finding Duplicate Metrics

Task: find out if there are sellers with the same revenue in each category.

SELECT seller_name, product_category, revenue,
       DENSE_RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS dense_rank
FROM sales;

Now you can see the ranks where values "stick" together.