Window Functions ROW_NUMBER, RANK, LAG: SQL to Expert Level with AI on ASI Biont

Introduction

In the world of modern data analytics, SQL remains an indispensable tool. However, basic queries with GROUP BY and ORDER BY often fall short for tasks requiring ranking, moving averages, or row comparisons. This is where window functions come to the rescue—a powerful mechanism that allows computations over sets of rows without losing granularity. In this article, we'll break down three key functions: ROW_NUMBER, RANK, and LAG, and show how to combine them with CTEs and indexes for production-level performance. The SQL Mastery course on the ASI Biont platform, powered by AI-based learning, will help you master these and other advanced topics: from B-tree to full-text search.

Concept of Window Functions

Window functions operate within a "window"—a subset of rows defined by PARTITION BY and ORDER BY. Unlike aggregate functions, they do not collapse rows: each row is preserved, and the result is computed for its context. This is ideal for ranking, cumulative sums, and lags.

Syntax and Examples

ROW_NUMBER: Row Numbering

ROW_NUMBER() assigns a unique number to each row within the window. It is often used for deduplication or pagination.

SELECT 
    employee_id,
    department_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_in_dept
FROM employees;

Example with CTE for Deduplication:

WITH deduped AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY created_at DESC) AS rn
    FROM orders_history
)
SELECT * FROM deduped WHERE rn = 1;

RANK and DENSE_RANK: Ranking with Gaps

RANK() leaves gaps when values are tied, while DENSE_RANK() does not.

SELECT 
        product_name,
        sales,
        RANK() OVER (ORDER BY sales DESC) AS rank_standard,
        DENSE_RANK() OVER (ORDER BY sales DESC) AS rank_dense
FROM products;

LAG: Accessing the Previous Row

LAG() allows access to data from the previous row in the window—for example, to compute the difference from the previous period.

SELECT 
    date,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY date) AS prev_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY date) AS daily_change
FROM daily_revenue;

Query Optimization with EXPLAIN ANALYZE

Window functions can be expensive, especially with large data volumes. Key optimization points:

1. Indexes for ORDER BY and PARTITION BY. If you frequently filter by department_id and sort by salary, create a composite index: CREATE INDEX idx_dept_salary ON employees(department_id, salary DESC);.
2. Use EXPLAIN ANALYZE. Check whether the query uses an Index Scan or Seq Scan. For example:

EXPLAIN ANALYZE
SELECT 
    employee_id,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) 
FROM employees;

1. Limit the number of rows. If you need the top 3, use LATERAL or a subquery with LIMIT.

Comparison of Approaches

Conclusion

Window functions are not just syntactic sugar but a fundamental tool for analysts and developers working with relational databases. By mastering ROW_NUMBER, RANK, and LAG, you can write elegant and efficient queries that previously required complex subqueries or stored procedures. Combined with CTEs, proper indexes, and an understanding of query planning, these techniques elevate your SQL to expert level.

Ready to dive deeper? The ASI Biont platform with AI-based learning offers a hands-on SQL Mastery course, where you'll explore window functions, B-tree and GIN indexes, partitioning, and full-text search. Start with a free module today!