SQL CONCEPTS - Window Functions, Joins, Common Table Expressions & Subqueries
1. SQL Window Functions These are functions that are used to perform calculations. They return one output row per one input row. Examples of Window functions include: ROW_NUMBER (), RANK (), DENSE_RANK (), NTILE (), LAG (), LEAD (), FIRST_VALUE (), LAST_VALUE (), SUM (), AVG (), COUNT (), MIN (), and MAX (). ROW NUMBER() It assigns a unique sequential number to each row within an ordered…
SQL window functions are a powerful tool for performing calculations across a set of rows related to the current row. These functions return one output row per input row, making them useful for tasks such as ranking, cumulative totals, and moving averages. Examples of window functions include ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), SUM(), AVG(), COUNT(), MIN(), and MAX().
ROW_NUMBER() is particularly useful for assigning a unique sequential number to each row within an ordered partition, while RANK() and DENSE_RANK() assign ranks to rows based on a specified order, with DENSE_RANK() handling ties differently by not leaving gaps in the ranking sequence. NTILE() divides an ordered result set into a specified number of equal groups, assigning a group number to each row.
Meanwhile, LAG() and LEAD() allow access to data from previous or subsequent rows within the same result set, which can be used for comparison purposes, such as calculating the difference in sales between consecutive months.
Brief written by urgent.news from Dev.to's own syndicated text. Machine-written — may contain errors; check the original before relying on it.
