Window Functions

Question 1

Visualize a rolling average 3 days

Answer 1

06/01/2022: 2 = 2.0
06/02/2022: 2 + 1 = 3 / 2 days = 1.5
06/03/2022: 2 + 1 + 3 = 6 / 3 days = 2.0
06/04/2022: 2 + 1 + 3 + 4 = 10 / 4 days = 2.5
06/05/2022: 2 + 1 + 3 + 4 + 5 = 15 / 5 days = 3.0

Question 2

What does ROW_NUMBER() do?

Answer 2

Assigns a unique sequential number to each row within a partition

Question 3

What does RANK() do?

Answer 3

Assigns same rank to ties but skips numbers after ties

Question 4

What does DENSE_RANK() do?

Answer 4

Assigns same rank to ties with no gaps/skips

Question 5

What does LAG() do?

Answer 5

Returns previous row value

Question 6

What does LEAD() do?

Answer 6

Returns next row value

Question 7

What does SUM() OVER() do?

Answer 7

Calculates running total without collasping the rows like GROUP BY

Question 8

What does AVG() OVER() do?

Answer 8

Calculates moving average across rows

Question 9

What does PARTITION BY do?

Answer 9

Splits data into groups without collapsing rows

Question 10

What does ORDER BY do in window functions?

Answer 10

Defines row order for calculations

Question 11

What does ROWS BETWEEN mean?

Answer 11

Defines which rows are included in the window

Question 12

What does UNBOUNDED PRECEDING mean?

Answer 12

Start from first row of partition

Question 13

What does CURRENT ROW mean?

Answer 13

Includes the current row as boundary

Question 14

What does UNBOUNDED FOLLOWING mean?

Answer 14

Includes all rows after current row

Question 15

What does ROWS BETWEEN 2 PRECEDING AND CURRENT ROW mean?

Answer 15

Includes current row + 2 previous rows

Question 16

What does ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING mean?

Answer 16

Includes previous current and next row

Question 17

What does ROWS mean?

Answer 17

Counts physical rows

Question 18

What does RANGE mean?

Answer 18

Uses value-based range not row count

Question 19

How to get latest row per user?

Answer 19

Use ROW_NUMBER with PARTITION and filter rn=1

Question 20

How to calculate MoM change?

Answer 20

Use LAG to compare current vs previous row

Question 21

How to get top N including ties?

Answer 21

Use DENSE_RANK and filter rank <= N

Question 22

How to compute running total?

Answer 22

Use SUM OVER with ORDER BY

Question 23

How to compute moving average?

Answer 23

Use AVG OVER with ROWS BETWEEN

Question 24

Visualize ROW_NUMBER() ?

Answer 24

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_date DESC)

Question 25

Visualize RANK() ?

Answer 25

RANK() OVER (ORDER BY revenue DESC)

Question 26

Visualize DENSE_RANK() ?

Answer 26

DENSE_RANK() OVER (ORDER BY revenue DESC)

Question 27

Visualize LAG() ?

Answer 27

LAG(revenue) OVER (ORDER BY month)

Question 28

Visualize LEAD() ?

Answer 28

LEAD(payment_date) OVER (PARTITION BY user_id ORDER BY payment_date)

Question 29

Visualize SUM() OVER() ?

Answer 29

SUM(amount) OVER (ORDER BY payment_date)

Question 30

Visualize AVG() OVER() ?

Answer 30

AVG(mrr) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

Question 31

Visualize PARTITION BY ?

Answer 31

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_date DESC)

Question 32

Visualize ORDER BY in window functions?

Answer 32

LAG(revenue) OVER (ORDER BY month)

Question 33

Visualize ROWS BETWEEN mean?

Answer 33

AVG(mrr) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

Question 34

Visualize UNBOUNDED PRECEDING mean?

Answer 34

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Question 35

Visualize CURRENT ROW mean?

Answer 35

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Question 36

Visualize UNBOUNDED FOLLOWING mean?

Answer 36

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

Question 37

Visualize ROWS BETWEEN 2 PRECEDING AND CURRENT ROW mean?

Answer 37

AVG(mrr) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

Question 38

Visualize ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING mean?

Answer 38

AVG(value) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

Question 39

Visualize ROWS mean?

Answer 39

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

Question 40

Visualize RANGE mean?

Answer 40

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Question 41

Visualize How to get latest row per user?

Answer 41

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_date DESC) rn FROM events) t WHERE rn=1

Question 42

Visualize How to calculate MoM change?

Answer 42

SELECT month, revenue, revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change FROM revenue_table

Question 43

Visualize How to get top N including ties?

Answer 43

SELECT * FROM (SELECT user_id, revenue, DENSE_RANK() OVER (ORDER BY revenue DESC) rnk FROM users) t WHERE rnk <= 3

Question 44

Visualize How to compute running total?

Answer 44

SELECT date, SUM(revenue) OVER (ORDER BY date) AS cumulative_revenue FROM sales

Question 45

Visualize How to compute moving average?

Answer 45

SELECT date, AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales

Question 46

Formula for determining a window frame ROWS BETWEEN

Answer 46

N-row window frame = ROWS BETWEEN N-1 PRECEDING AND CURRENT ROW 3-row → 2 PRECEDING 5-row → 4 PRECEDING 10-row → 9 PRECEDING

Question 47

Functions that do not use window frames and only care about PARTITION BY and ORDER BY

Answer 47

ROW_NUMBER() RANK() DENSE_RANK() LAG() LEAD()

Question 48

Functions that do you use WINDOW FRAMES

Answer 48

SUM() OVER AVG() OVER MIN() OVER MAX() OVER