SQL Window Functions: Complete Guide with Sakila Database Examples

Ranking Window Functions

These functions assign ranks or row numbers to rows within a partition.

1. ROW_NUMBER()
• Assigns a unique sequential integer to each row within a partition.
• Example: ROW_NUMBER() OVER (PARTITION BY group ORDER BY order_column)
2. RANK()
• Assigns a rank to each row within a partition, with gaps for ties.
• Example: RANK() OVER (PARTITION BY group ORDER BY order_column)
3. DENSE_RANK()
• Assigns a rank to each row within a partition, without gaps for ties.
• Example: DENSE_RANK() OVER (PARTITION BY group ORDER BY order_column)
4. NTILE()
• Divides rows into a specified number of buckets (e.g., quartiles, deciles).
• Example: NTILE(4) OVER (PARTITION BY group ORDER BY order_column)
5. PERCENT_RANK()
• Calculates the relative rank of a row as a percentage (0 to 1).
• Example: PERCENT_RANK() OVER (PARTITION BY group ORDER BY order_column)
6. CUME_DIST()
• Calculates the cumulative distribution of a value within a partition (0 to 1).
• Example: CUME_DIST() OVER (PARTITION BY group ORDER BY order_column)

Introduction to Window Functions

Window functions perform calculations across a set of rows that are related to the current row, without collapsing the result set like aggregate functions do. They are called "window" functions because they operate on a "window" of rows defined by the OVER clause.

Summary Comparison Table

Key Takeaways

1. ROW_NUMBER(): Always unique, perfect for pagination and removing duplicates
2. RANK(): Reflects ties with gaps, like Olympic medal standings
3. DENSE_RANK(): Reflects ties without gaps, good for tier classifications
4. NTILE(): Splits data into equal buckets, ideal for segmentation
5. PERCENT_RANK(): Shows relative position, answers "better than X%"
6. CUME_DIST(): Shows cumulative percentage, answers "X% are at or below this"

Additional Tips

PARTITION BY Usage

All these functions support PARTITION BY to reset calculations for each group:

-- Separate rankings for each category
RANK() OVER (PARTITION BY category ORDER BY value DESC)

ORDER BY is Required

Most window ranking functions require an ORDER BY clause (except in special cases):

-- This works
ROW_NUMBER() OVER (ORDER BY amount)

-- This may cause unexpected results
ROW_NUMBER() OVER ()  -- Avoid this

Multiple Window Functions

You can use multiple window functions in the same query:

SELECT
    name,
    value,
    ROW_NUMBER() OVER (ORDER BY value) AS row_num,
    RANK() OVER (ORDER BY value) AS rank,
    DENSE_RANK() OVER (ORDER BY value) AS dense_rank,
    NTILE(4) OVER (ORDER BY value) AS quartile
FROM table_name;

Performance Considerations

• Window functions are computed after WHERE but before ORDER BY
• Use indexes on PARTITION BY and ORDER BY columns when possible
• Consider materializing results for complex repeated window calculations

Practice Exercises

Try these exercises with the Sakila database:

1. Exercise 1: Find the 3rd most rented film using DENSE_RANK()
2. Exercise 2: Divide films into 5 equal groups by length using NTILE()
3. Exercise 3: Find customers in the top 10% by spending using PERCENT_RANK()
4. Exercise 4: For each category, find films that are longer than 75% of films in that category using CUME_DIST()
5. Exercise 5: Number all payments sequentially by payment date using ROW_NUMBER()

This guide covers the six main ranking window functions in SQL. Practice with the Sakila database examples to master these powerful analytical tools!