Learning
SQL
75 Windowing
Functions
Shwetank Singh
GritSetGrow - GSGLearn.com
Shwetank Singh
GritSetGrow - GSGLearn.com
1
1. WHAT IS A WINDOW
FUNCTION IN SQL?
A window function performs a
calculation across a set of table
rows related to the current row. It
is used mainly for analytical
purposes and operates within a
specified window of rows.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
2
2. HOW DO YOU
DEFINE A WINDOW IN
SQL?
A window is defined using the
OVER clause in SQL, which
specifies the partitioning and
ordering of rows.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
3
3. WHAT ARE THE
TYPES OF WINDOW
FUNCTIONS
SUPPORTED IN SQL
SERVER?
Aggregate functions, ranking
functions, statistical functions,
and offset functions.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
4
4. WHAT IS THE
DIFFERENCE BETWEEN
A WINDOW FUNCTION
AND AN AGGREGATE
FUNCTION?
An aggregate function groups
rows and returns a single result
for each group, while a window
function returns a result for each
row within the specified window.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
5
5. HOW DO YOU USE
THE ROW_NUMBER()
FUNCTION?
ROW_NUMBER() OVER (PARTITION
BY column ORDER BY column)
assigns a unique sequential
integer to rows within the
partition of a result set.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
6
6. EXPLAIN THE RANK()
FUNCTION.
RANK() OVER (PARTITION BY
column ORDER BY column)
assigns a rank to each row within
the partition of a result set, with
gaps in the rank values if there
are ties.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
7
7. WHAT IS THE
DIFFERENCE BETWEEN
RANK() AND
DENSE_RANK()?
DENSE_RANK() is similar to RANK()
but does not leave gaps in the
ranking values.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
8
8. DESCRIBE THE
NTILE() FUNCTION.
NTILE(n) OVER (PARTITION BY
column ORDER BY column)
distributes rows into a specified
number of roughly equal groups.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
9
9. WHAT DOES THE
LAG() FUNCTION DO?
LAG(column, offset, default) OVER
(PARTITION BY column ORDER BY
column) provides access to a row
at a specified physical offset
before the current row within the
partition.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
10
10. HOW DOES THE
LEAD() FUNCTION
DIFFER FROM LAG()?
LEAD(column, offset, default)
OVER (PARTITION BY column
ORDER BY column) provides
access to a row at a specified
physical offset after the current
row within the partition.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
11
11. EXPLAIN THE USE OF
FIRST_VALUE()
FUNCTION.
FIRST_VALUE(column) OVER
(PARTITION BY column ORDER BY
column) returns the first value in
an ordered set of values.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
12
12. WHAT IS THE
LAST_VALUE()
FUNCTION USED FOR?
LAST_VALUE(column) OVER
(PARTITION BY column ORDER BY
column) returns the last value in
an ordered set of values.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
13
13. HOW DO YOU
CALCULATE A MOVING
AVERAGE USING
WINDOW FUNCTIONS?
Use AVG(column) OVER
(PARTITION BY column ORDER BY
column ROWS BETWEEN n
PRECEDING AND CURRENT ROW) to
calculate a moving average.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
14
14. DESCRIBE THE
PERCENT_RANK()
FUNCTION.
PERCENT_RANK() OVER (PARTITION
BY column ORDER BY column)
calculates the relative rank of a
row within the partition as a
percentage.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
15
15. WHAT IS THE
CUME_DIST()
FUNCTION?
CUME_DIST() OVER (PARTITION BY
column ORDER BY column)
calculates the cumulative
distribution of a value within the
partition.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
16
16. EXPLAIN
PERCENTILE_CONT()
FUNCTION.
PERCENTILE_CONT(n) WITHIN
GROUP (ORDER BY column) OVER
(PARTITION BY column) computes
a percentile based on continuous
distribution.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
17
17. HOW DOES
PERCENTILE_DISC()
DIFFER FROM
PERCENTILE_CONT()?
PERCENTILE_DISC(n) WITHIN
GROUP (ORDER BY column) OVER
(PARTITION BY column) computes
a percentile based on discrete
distribution.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
18
18. WHAT IS A FRAME
IN THE CONTEXT OF
WINDOW FUNCTIONS?
A frame defines a subset of rows
within the partition to apply the
window function, specified using
ROWS or RANGE clauses.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
19
19. HOW DO YOU USE
THE ROWS BETWEEN
CLAUSE?
ROWS BETWEEN n PRECEDING AND
m FOLLOWING specifies a frame
of rows relative to the current row
for the window function.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
20
20. WHAT IS THE
RANGE CLAUSE USED
FOR IN WINDOW
FUNCTIONS?
The RANGE clause defines a
frame of rows based on the value
range of the rows, not their
physical positions.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
21
21. HOW CAN YOU
EMULATE IGNORE
NULLS IN SQL SERVER?
Use subqueries or conditional
aggregation to ignore nulls in
window functions since SQL
Server does not directly support
IGNORE NULLS.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
22
22. EXPLAIN THE
CONCEPT OF
WINDOWING IN SQL.
Windowing allows calculations
across a set of table rows related
to the current row using window
functions.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
23
23. WHAT ARE THE
BENEFITS OF USING
WINDOW FUNCTIONS?
Window functions simplify
complex queries, improve
readability, and often provide
better performance for analytical
queries.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
24
24. CAN WINDOW
FUNCTIONS BE USED IN
THE WHERE CLAUSE?
No, window functions cannot be
used in the WHERE clause directly.
They are used in the SELECT or
ORDER BY clauses.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
25
25. HOW DO WINDOW
FUNCTIONS IMPROVE
PERFORMANCE?
Window functions can perform
calculations without requiring
additional joins or subqueries,
reducing the complexity and
execution time.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
26
26. WHAT IS THE
DIFFERENCE BETWEEN
PARTITION BY AND
ORDER BY IN WINDOW
FUNCTIONS?
PARTITION BY divides the result set
into partitions, and ORDER BY
specifies the order of rows within
each partition.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
27
27. HOW DO YOU
CALCULATE RUNNING
TOTALS USING
WINDOW FUNCTIONS?
Use SUM(column) OVER
(PARTITION BY column ORDER BY
column ROWS UNBOUNDED
PRECEDING) for running totals.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
28
28. DESCRIBE THE USE
OF COUNT() AS A
WINDOW FUNCTION.
COUNT(column) OVER (PARTITION
BY column ORDER BY column)
counts the number of rows within
the window frame.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
29
29. HOW CAN YOU
USE WINDOW
FUNCTIONS TO
IDENTIFY GAPS AND
ISLANDS IN DATA?
Use LAG() and LEAD() functions to
compare current and
previous/next row values to
identify gaps and islands.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
30
30. WHAT ARE
HYPOTHETICAL SET
FUNCTIONS?
Hypothetical set functions, like
PERCENTILE_CONT and
PERCENTILE_DISC, compute
percentiles and other statistical
measures based on hypothetical
distributions.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
31
31. EXPLAIN THE
STRING_AGG()
FUNCTION.
STRING_AGG(column, delimiter)
WITHIN GROUP (ORDER BY
column) OVER (PARTITION BY
column) concatenates values
into a single string within each
partition.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
32
32. HOW DOES GROUP
BY DIFFER FROM
PARTITION BY IN
WINDOW FUNCTIONS?
GROUP BY aggregates rows into
groups and reduces the result
set, while PARTITION BY defines
partitions for window functions
without reducing the result set.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
33
33. WHAT IS THE
SIGNIFICANCE OF
ROWS UNBOUNDED
PRECEDING?
ROWS UNBOUNDED PRECEDING
specifies the frame from the first
row in the partition to the current
row.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
34
34. HOW DO YOU
REMOVE DUPLICATES
USING WINDOW
FUNCTIONS?
Use ROW_NUMBER() OVER
(PARTITION BY column ORDER BY
column) to assign unique
numbers and then filter by the
row number to remove
duplicates.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
35
35. CAN WINDOW
FUNCTIONS BE
NESTED?
No, window functions cannot be
nested within each other in the
same query.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
36
36. HOW DO YOU
CALCULATE A MOVING
SUM USING WINDOW
FUNCTIONS?
Use SUM(column) OVER
(PARTITION BY column ORDER BY
column ROWS BETWEEN n
PRECEDING AND CURRENT ROW) to
calculate a moving sum.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
37
37. EXPLAIN THE
CONCEPT OF FRAMING
IN WINDOW
FUNCTIONS.
Framing specifies the subset of
rows within a partition for the
window function, defined by
ROWS or RANGE clauses.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
38
38. HOW DO YOU USE
THE OVER CLAUSE
WITH PARTITION BY
AND ORDER BY?
OVER (PARTITION BY column
ORDER BY column) defines the
window frame by partitioning and
ordering the rows.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
39
39. WHAT IS THE
PURPOSE OF NTILE() IN
WINDOW FUNCTIONS?
NTILE() divides the result set into a
specified number of
approximately equal groups.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
40
40. HOW DO WINDOW
FUNCTIONS HANDLE
TIES IN RANKING?
Functions like RANK() leave gaps
for ties, while DENSE_RANK() does
not leave gaps.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
41
41. DESCRIBE HOW TO
CALCULATE THE
MEDIAN USING
WINDOW FUNCTIONS.
Use PERCENTILE_CONT(0.5)
WITHIN GROUP (ORDER BY
column) OVER (PARTITION BY
column) to calculate the median.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
42
42. WHAT ARE THE
LIMITATIONS OF
WINDOW FUNCTIONS
IN SQL SERVER?
Limitations include the inability to
use window functions in WHERE,
HAVING, and GROUP BY clauses,
and lack of support for some
standard features like IGNORE
NULLS.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
43
43. HOW DO YOU
HANDLE NULL VALUES
IN WINDOW
FUNCTIONS?
Use conditional expressions or
subqueries to handle NULL values,
as window functions do not
directly support IGNORE NULLS.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
44
44. WHAT IS THE
WINDOW CLAUSE
USED FOR?
The WINDOW clause allows you to
reuse window definitions within
the same query, improving
readability and maintainability.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
45
45. HOW DO YOU
IMPLEMENT PAGING
USING WINDOW
FUNCTIONS?
Use ROW_NUMBER() OVER (ORDER
BY column) to assign row
numbers and then filter by the
desired page range.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
46
46. WHAT ARE
STATISTICAL WINDOW
FUNCTIONS?
Statistical window functions
include PERCENTILE_CONT,
PERCENTILE_DISC, PERCENT_RANK,
and CUME_DIST, used for
statistical calculations.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
47
47. EXPLAIN THE
CONCEPT OF ROW
PATTERN
RECOGNITION IN SQL.
Row pattern recognition allows
the detection of patterns within
sequences of rows, a feature not
yet fully supported in SQL Server.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
48
48. HOW CAN
WINDOW FUNCTIONS
BE OPTIMIZED IN SQL
SERVER?
Optimization techniques include
indexing, parallelism
improvements, and batch-mode
processing for better
performance.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
49
49. DESCRIBE THE USE
OF FIRST_VALUE AND
LAST_VALUE IN
WINDOW FUNCTIONS.
FIRST_VALUE and LAST_VALUE
return the first and last values in
the window frame, respectively,
based on the specified ordering.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
50
50. HOW DO YOU
PERFORM
CONDITIONAL
AGGREGATION USING
WINDOW FUNCTIONS?
Use CASE expressions within
aggregate functions to perform
conditional aggregation, e.g.,
SUM(CASE WHEN condition THEN
column ELSE 0 END) OVER
(PARTITION BY column).
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
51
51. HOW DO YOU CALCULATE
A CUMULATIVE SUM USING
WINDOW FUNCTIONS?
Example:
SELECT orderid, val, SUM(val)
OVER (ORDER BY orderid) AS
cumulative_sum
FROM Sales.OrderValues;
This query calculates the
cumulative sum of the val
column ordered by orderid.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
52
52. EXPLAIN THE
ROW_NUMBER() FUNCTION
WITH AN EXAMPLE.
Example:
SELECT orderid, val, ROW_NUMBER()
OVER (ORDER BY val DESC) AS
row_num
FROM Sales.OrderValues;
This query assigns a unique
sequential integer to rows
ordered by val in descending
order.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
53
53. HOW DO YOU USE LAG()
TO FIND THE PREVIOUS
ROW'S VALUE?
Example:
SELECT orderid, val, LAG(val, 1) OVER
(ORDER BY orderid) AS prev_val
FROM Sales.OrderValues;
This query returns the previous
row's val value for each row.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
54
54. PROVIDE AN EXAMPLE OF
USING LEAD() TO FIND THE
NEXT ROW'S VALUE.
Example:
SELECT orderid, val, LEAD(val, 1) OVER
(ORDER BY orderid) AS next_val
FROM Sales.OrderValues;
This query returns the next row's
val value for each row.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
55
55. HOW DO YOU
CALCULATE A MOVING
AVERAGE USING WINDOW
FUNCTIONS?
Example:
SELECT orderid, val,
AVG(val) OVER (ORDER BY orderid
ROWS BETWEEN 2 PRECEDING AND
CURRENT ROW) AS moving_avg
FROM Sales.OrderValues;
This query calculates a moving
average over the current and two
preceding rows.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
56
56. EXPLAIN HOW TO RANK
ROWS USING RANK().
Example:
SELECT orderid, val,
RANK() OVER (ORDER BY val DESC)
AS rnk
FROM Sales.OrderValues;
This query ranks rows based on
val in descending order.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
57
57. HOW DO YOU USE
DENSE_RANK() TO RANK
ROWS WITHOUT GAPS?
Example:
SELECT orderid, val, DENSE_RANK()
OVER (ORDER BY val DESC) AS
dense_rnk
FROM Sales.OrderValues;
This query ranks rows based on
val in descending order without
gaps in the rank values.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
58
58. DESCRIBE THE NTILE()
FUNCTION WITH AN
EXAMPLE.
Example:
SELECT orderid, val, NTILE(4) OVER
(ORDER BY val) AS quartile
FROM Sales.OrderValues;
This query divides the result set
into four approximately equal
groups.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
59
59. HOW DO YOU USE
FIRST_VALUE() TO GET THE
FIRST VALUE IN A WINDOW?
Example:
SELECT orderid, val, FIRST_VALUE(val)
OVER (ORDER BY orderid) AS first_val
FROM Sales.OrderValues;
This query returns the first val
value based on orderid ordering.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
60
60. PROVIDE AN EXAMPLE
OF USING LAST_VALUE()
TO GET THE LAST VALUE IN
A WINDOW.
Example:
SELECT orderid, val, LAST_VALUE(val)
OVER (ORDER BY orderid ROWS
BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING) AS last_val
FROM Sales.OrderValues;
This query returns the last val
value based on orderid ordering.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
61
61. HOW DO YOU CALCULATE
THE DIFFERENCE BETWEEN
CURRENT AND PREVIOUS
ROW VALUES USING LAG()?
Example:
SELECT orderid, val,
val - LAG(val, 1) OVER (ORDER BY
orderid) AS diff
FROM Sales.OrderValues;
This query calculates the
difference between the current
and previous row's val.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
62
62. EXPLAIN THE USE OF
PERCENT_RANK() WITH AN
EXAMPLE.
Example:
SELECT orderid, val,
PERCENT_RANK() OVER (ORDER BY
val) AS percent_rank
FROM Sales.OrderValues;
This query calculates the relative
rank of each row as a percentage
of the total rows.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
63
63. HOW DO YOU
CALCULATE THE CUMULATIVE
DISTRIBUTION USING
CUME_DIST()?
Example:
SELECT orderid, val,
CUME_DIST() OVER (ORDER BY val) AS
cumulative_dist
FROM Sales.OrderValues;
This query calculates the
cumulative distribution of each
row's val.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
64
64. PROVIDE AN EXAMPLE OF
USING PERCENTILE_CONT()
TO CALCULATE A
PERCENTILE.
Example:
SELECT orderid, val,
PERCENTILE_CONT(0.5) WITHIN GROUP
(ORDER BY val) OVER () AS median
FROM Sales.OrderValues;
This query calculates the median
val.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
65
65. EXPLAIN HOW TO USE
PERCENTILE_DISC() TO
CALCULATE A PERCENTILE.
Example:
SELECT orderid, val,
PERCENTILE_DISC(0.5) WITHIN
GROUP (ORDER BY val) OVER () AS
median
FROM Sales.OrderValues;
This query calculates the median
val based on discrete distribution.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
66
66. HOW DO YOU
CALCULATE A RUNNING
TOTAL USING WINDOW
FUNCTIONS?
Example:
SELECT orderid, val, SUM(val) OVER
(ORDER BY orderid ROWS BETWEEN
UNBOUNDED PRECEDING AND CURRENT
ROW) AS running_total
FROM Sales.OrderValues;
This query calculates a running
total of val.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
67
67. DESCRIBE THE USE OF THE
RANGE CLAUSE WITH AN
EXAMPLE.
Example:
SELECT orderid, val, SUM(val) OVER
(ORDER BY orderid RANGE BETWEEN
INTERVAL '1' DAY PRECEDING AND
CURRENT ROW) AS range_sum
FROM Sales.OrderValues;
This query calculates the sum of
val for rows within the specified
range of days.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
68
68. HOW DO YOU
IMPLEMENT A SLIDING
WINDOW CALCULATION?
Example:
SELECT orderid, val, AVG(val) OVER
(ORDER BY orderid ROWS BETWEEN 2
PRECEDING AND 2 FOLLOWING) AS
sliding_avg
FROM Sales.OrderValues;
This query calculates a sliding
average over a window of five
rows centered on the current row.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
69
69. PROVIDE AN EXAMPLE
OF USING LAG() AND LEAD()
TOGETHER.
Example:
SELECT orderid, val, LAG(val, 1) OVER
(ORDER BY orderid) AS prev_val,
LEAD(val, 1) OVER (ORDER BY orderid)
AS next_val FROM Sales.OrderValues;
This query returns the previous
and next val values for each row.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
70
70. EXPLAIN HOW TO
PARTITION DATA USING
WINDOW FUNCTIONS.
Example:
SELECT orderid, customerid, val,
SUM(val) OVER (PARTITION BY
customerid ORDER BY orderid) AS
customer_total
FROM Sales.OrderValues;
This query calculates the running
total of val for each customerid.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
71
71. HOW DO YOU USE
WINDOW FUNCTIONS TO
CALCULATE THE
PERCENTAGE OF A TOTAL?
Example:
SELECT orderid, val, val * 100.0 /
SUM(val) OVER () AS
percent_of_total
FROM Sales.OrderValues;
This query calculates the
percentage of each val of the
total sum of val.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
72
72. PROVIDE AN EXAMPLE
OF USING NTILE() TO CREATE
DECILES.
Example:
SELECT orderid, val, NTILE(10) OVER
(ORDER BY val) AS decile
FROM Sales.OrderValues;
This query divides the result set
into ten approximately equal
groups (deciles).
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
73
73. HOW DO YOU USE
WINDOW FUNCTIONS TO FIND
THE TOP N ROWS PER GROUP?
Example:
WITH RankedOrders
AS (SELECT orderid, customerid, val,
ROW_NUMBER() OVER (PARTITION BY
customerid ORDER BY val DESC) AS rank
FROM Sales.OrderValues)
SELECT * FROM RankedOrders
WHERE rank <= 3;
This query returns the top 3 orders by
value for each customer.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
74
74. EXPLAIN HOW TO USE
WINDOW FUNCTIONS FOR
DATA DE-DUPLICATION.
Example:
WITH CTE AS
(SELECT orderid, val,
ROW_NUMBER() OVER (PARTITION BY
val ORDER BY orderid) AS rn
FROM Sales.OrderValues)
DELETE FROM CTE WHERE rn > 1;
This query deletes duplicate rows
based on the val column.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
Shwetank Singh
GritSetGrow - GSGLearn.com
75
75. HOW DO YOU PERFORM
CUMULATIVE AGGREGATION
USING WINDOW
FUNCTIONS?
Example:
SELECT orderid, val, SUM(val) OVER
(ORDER BY orderid ROWS BETWEEN
UNBOUNDED PRECEDING AND CURRENT
ROW) AS cumulative_sum
FROM Sales.OrderValues;
This query calculates the
cumulative sum of val for all
preceding rows including the
current row.
DATA ENGINEERING - SQL
WINDOW FUNCTIONS
