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. ```sql 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:** ```sql 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. ```sql 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. ```sql 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: ```sql EXPLAIN ANALYZE SELECT employee_id, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) FROM employees; ``` 3. **Limit the number of rows.** If you need the top 3, use `LATERAL` or a subquery with `LIMIT`. ## Comparison of Approaches | Function | Key Behavior | Typical Use Case | |----------|--------------|------------------| | ROW_NUMBER | Unique number, no duplicates | Pagination, deduplication | | RANK | Gaps on ties | Top-N with ties | | LAG | Access to previous row | Delta calculation, moving metrics | ## 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!

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

Comments

Recent articles

On This Page: How AI Is Revolutionizing Web Page Interaction and Vibe Coding

Synthetic Data Generation with LLMs: Techniques, Tools, and Best Practices

Aiogram 3: Basics and Handlers for Telegram Bots — Learning with AI on ASI Biont

AI for Accessibility: How LLMs Are Empowering People with Disabilities

AI for Marketplaces: How to Manage Listings, Prices, and Analytics on Wildberries and Ozon

Excel and Google Sheets: Learning with AI on ASI Biont — Your Key to Automation and Analytics

Building AI-Powered APIs: Best Practices for LLM Integration in Production

OpenAI o3 vs DeepSeek R2: The 2026 Reasoning Model Showdown

Fundamentals of Banking Law: Learning to Navigate the Russian Banking System with AI on ASI Biont