What are Window Functions?
27 February 2023 14:18
Window functions in SQL are a type of analytical function that perform calculations
across a set of rows that are related to the current row, called a "window". A
window function calculates a value for each row in the result set based on a subset
of the rows that are defined by a window specification.
The window specification is defined using the OVER() clause in SQL, which specifies
the partitioning and ordering of the rows that the window function will operate
on. The partitioning divides the rows into groups based on a specific column or
expression, while the ordering defines the order in which the rows are processed
within each group.
Window Function Page 1
Aggregate Function with OVER()
27 February 2023 16:41
Find all the students who have marks higher than the avg marks of
their respective branch
Window Function Page 2
RANK/DENSE_RANK/ROW_NUMBER
27 February 2023 16:56
1. Find top 2 most paying customers of each month
2. Create roll no from branch and marks
Window Function Page 3
FIRST_VALUE/LAST VALUE/NTH_VALUE
27 February 2023 16:56
1. Find the branch toppers
2. FRAME Clause
3. Find the last guy of each branch
4. Alternate way of writing Window functions
5. Find the 2nd last guy of each branch, 5th topper of each branch
Window Function Page 4
Frames
27 February 2023 19:08
A frame in a window function is a subset of rows within the partition that
determines the scope of the window function calculation. The frame is defined
using a combination of two clauses in the window function: ROWS and BETWEEN.
The ROWS clause specifies how many rows should be included in the frame
relative to the current row. For example, ROWS 3 PRECEDING means that the
frame includes the current row and the three rows that precede it in the partition.
The BETWEEN clause specifies the boundaries of the frame.
Examples
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW - means that the
frame includes all rows from the beginning of the partition up to and including the
current row.
• ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING: the frame includes the
current row and the row immediately before and after it.
• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING: the
frame includes all rows in the partition.
• ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING: the frame includes the
current row and the three rows before it and the two rows after it.
Window Function Page 5
LEAD & LAG
27 February 2023 17:12
Find the MoM revenue growth of Zomato
Window Function Page 6