SQL Window Functions Explained by Example

Introduction

SQL window functions are among the most powerful tools in modern SQL. They allow developers to perform calculations across sets of rows that are related to the current query row—without grouping or collapsing results. This makes window functions perfect for tasks like ranking, running totals, moving averages, and percentage contributions. In this post, we’ll break down SQL window functions using examples from a realistic e-commerce database, focusing on ranking, partitioning, and cumulative aggregations.

1. Setting Up the E-Commerce Dataset

Let’s assume a simple orders table representing e-commerce transactions:

CREATE TABLE orders (\n    order_id SERIAL PRIMARY KEY,\n    customer_id INT,\n    order_date DATE,\n    total_amount DECIMAL(10, 2)\n);

Let’s populate it with a few rows of sample data:

INSERT INTO orders (customer_id, order_date, total_amount) VALUES\n(101, '2024-01-01', 250.00),\n(102, '2024-01-02', 100.00),\n(101, '2024-01-03', 150.00),\n(103, '2024-01-03', 300.00),\n(101, '2024-01-04', 75.00),\n(102, '2024-01-04', 200.00);

This simple dataset gives us enough material to explore how window functions work across different customers and date ranges.

2. Ranking Orders by Total Amount

Ranking is one of the most common use cases for window functions. We can quickly find the rank of each order based on total amount:

SELECT\n    order_id,\n    customer_id,\n    total_amount,\n    RANK() OVER (ORDER BY total_amount DESC) AS rank_by_amount\nFROM orders;

This query ranks all orders from highest to lowest total amount. Unlike ROW_NUMBER(), RANK() will assign the same rank to equal values. If two orders share the same total amount, they’ll get the same rank, and the next rank will skip accordingly. This technique is particularly useful when generating leaderboards, top-selling product lists, or best-performing customers.

3. Partitioning the Ranking by Customer

What if you want to rank orders not globally but within each customer’s set of orders? That’s where PARTITION BY shines. You can partition the data into groups—one per customer—and apply window functions independently within each partition:

SELECT\n    customer_id,\n    order_id,\n    total_amount,\n    RANK() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) AS rank_within_customer\nFROM orders\nORDER BY customer_id, rank_within_customer;

This query outputs ranks per customer rather than overall. The beauty here is that you can analyze customer-specific performance while still keeping individual rows. It’s highly efficient for segment-based analytics like ‘top three orders per customer’ or ‘top five transactions per region.’

4. Calculating Running Totals

Running totals (or cumulative sums) are another essential use case. Let’s say you want to see how each customer’s spending evolves over time:

SELECT\n    customer_id,\n    order_date,\n    total_amount,\n    SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total\nFROM orders\nORDER BY customer_id, order_date;

Here, the window is defined by PARTITION BY customer_id ORDER BY order_date. The cumulative sum grows as the query moves chronologically through each customer’s orders. This pattern is invaluable for financial reporting, monthly revenue tracking, and retention-based spending analysis.

5. Combining Multiple Window Functions

Window functions are composable. You can calculate multiple metrics within a single query. For example, getting both rank and running total in one shot:

SELECT\n    customer_id,\n    order_date,\n    total_amount,\n    RANK() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) AS rank_within_customer,\n    SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS cumulative_spend\nFROM orders\nORDER BY customer_id, order_date;

This combined approach is often useful in dashboards or data pipelines that power BI tools. You can prepare all relevant analytics measures for each row while keeping the dataset rich in individual details.

6. Performance Tips & Best Practices

Window functions are generally efficient, but as datasets grow, a few optimizations can help:

Indexing: Having indexes on your PARTITION BY and ORDER BY columns speeds up sorting and partitioning.
Limit partitions when possible: Avoid global windows unless necessary, as PARTITION BY reduces sorting overhead.
Use ROW_NUMBER for pagination: It’s lighter and deterministic compared to RANK or DENSE_RANK.
Consider materialized views: For frequently accessed analytical queries, materialized views can precompute window aggregations efficiently.

Conclusion

SQL window functions elegantly bridge the gap between aggregate queries and raw row-level data access. They’re the backbone of advanced analytics in SQL, enabling developers to express complex analytical computations concisely and efficiently. By mastering ranking, partitioning, and cumulative calculations, you can transform a simple e-commerce dataset into actionable insights with a single query.

Useful links: