Advance sql - window functions patterns and tricks

Advance SQL
Windows Functions
Eyal Trabelsi

What is a window function
https://blog.matters.tech/sql-window-functions-basics-e9a9fa17ce7e

• NULLS values are treated as their own group over
the partitioned columns.
NULLS !!!!!

• They sorted and ranked according to the NULLS
FIRST or NULLS LAST option.
NULLS !!!!!

• They sorted and ranked according to the NULLS
FIRST or NULLS LAST option.
• By default, NULL values are sorted and ranked last
in ASC ordering, and sorted and ranked first in
DESC ordering.
NULLS !!!!!

Supported window functions
• Window function are vendor ‘ dependent!!!!

• Most vendors support the all the basic window functions
like avg, min, max, count, sum, lead, lag, rank etc.

• Most vendors support the all the basic window functions
like avg, min, max, count, sum, lead, lag, rank etc.
• These are the vendor specific supported window
functions:

Our Business use case
Lets say we have the following events table

Event_id time user_id type click_num
1 18:00 1 a 2
2 18:20 1 a 3
3 18:59 1 b 4
4 18:00 2 b 1

Calculating Cumulative sum via sum

Event_id time user_id type click_num cumsum
1 18:0
0
1 a 2 2
2 18:2
0
1 a 3 5
3 18:5
9
1 b 4 9
4 18:0
0
2 b 1 1

SELECT event_id,
time,
user_id,
type,
sum(click_num) over (PARTITION BY user_id ORDER BY time
ROWS UNBOUNDED PRECEDING)
FROM t

DEFAULTS!!!

• Calculate cumulative sum of lead per month
• Calculate cumulative sum of orders’ amount per store

Calculate Growth
Event_id time user_id type click_num growth
1 18:0
0
1 a 2 NULL
2 18:2
0
1 b 3 1/2
3 18:5
9
1 a 4 1/3
4 18:0
0
2 b 1 NULL

Calculate Growth
SELECT event_id,
time,
user_id,
type,
lag(num_click) over (PARTITION BY user_id ORDER BY time) AS
last_num_click,
(num_click - last_num_click) / last_num_click
FROM t

Calculate Growth
SELECT event_id,
time,
user_id,
type,
lag(num_click) over (PARTITION BY user_id ORDER BY time) AS
last_num_click,
(num_click - last_num_click) / last_num_click
FROM t
*

Calculate Growth
• Calculate growth of accounts month after month
• Calculate growth of orders amount per store month after month
• Calculate growth of orders amount per store and opportunity

Calculate Growth
• Calculate growth of accounts month after month
• Calculate growth of orders amount per store month after month
• Calculate growth of orders amount per store and opportunity HARD :P

Indicate the first event for each user

Event_id time user_id type first
1 18:00 1 a 1
2 18:20 1 b 0
3 18:59 1 a 0
4 18:00 2 b 1

SELECT event_id,
time,
user_id,
type
floor(1/ row_number() over (PARTITION BY user_id ORDER BY time))
FROM t

Indicate the first row template

Indicate the first row template
SELECT c1,
c2,
floor(1/ row_number() over (PARTITION BY c1 ORDER BY c2))
FROM t

Indicate the first template use-cases

• Our analysis need to indicate rank without changing.
granularity (filtering for example or summation of sub
granularity).

granularity).
• When we want to consolidate tables which indicate
changes and the source doesn’t support it. For example
table which indicate transitions to plans might miss the
first plans.

granularity).
• When we want to consolidate tables which indicate
changes and the source doesn’t support it. For example
table which indicate transitions to plans might miss the
first plans.
• Our query goal is not clear whether changing granularity
is needed.

Indicate the first template questions
• Show indicator on the first segment touch point on the store
• Show indicator for the first event of product change for store

Now for more
“complicated”
patterns

Creating session for user within 30 min

Event_id time user_id type session
1 18:00 1 a 1
2 18:20 1 b 1
3 18:59 1 a 2
4 18:00 2 b 1

https://blog.modeanalytics.com/finding-user-sessions-sql/

Creating session template
SELECT *,
SUM(new_session) over (PARTITION BY user_id ORDER BY time) AS new_session
FROM ( SELECT *
CASE WHEN (time - LEAD(time) over (PARTITION BY user_id ORDER BY time)) >
<INTERVAL>
THEN 1
ELSE 0
END AS new_session
FROM t
)

Creating session with condition

Lets say we don’t want to sessionize events with type b

Event_id time user_id type session
1 18:00 1 a 1
2 18:20 1 b
3 18:59 1 a 2
4 18:00 2 a 1
Lets say we don’t want to sessionize events with type b

SELECT *,
SUM(new_session) over (PARTITION BY user_id ,condition ORDER BY time) AS new_session
FROM ( SELECT *
CASE WHEN (time - LEAD(time) over (PARTITION BY user_id,condition ORDER BY time)) >
<INTERVAL>
THEN 1
ELSE 0
END AS new_session
FROM t
)

template use-cases
granularity (filtering for example).

Finding Series length
https://www.youtube.com/watch?v=mgipNdAgQ3o&t
=1070s%3Fstart=20:08&end=24:34

Finding Series length use-cases

Finding Series length use-cases
• Our analysis need to check sequence of improvement:

Finding Series length questions
• find the largest sequence of month with increase in lead creation
• find the largest sequence of month with decrease in ticket for product x
• find the difference in sequence of month with decrease in ticket for product
x after bug was fixed

Time decay template use-cases
Our analysis has time related importance calculation
• Calculated recommended movie , if I saw a movie a week
ago it will probably be more relevant to my interest than
movie I saw 5 years ago .

Event_id time user_id package
1 1/1/18 1 a
2 1/2/18 1 b
3 1/6/18 1 a
4 1/7/18 1 c
Lets say we have the following package change table

Join on time interval
Lets say we have the following package change
table

Event_id From_time To_time user_id package
1 1/1/18 1/2/18 1 a
2 1/2/18 1/6/18 1 b
3 1/6/18 1/7/18 1 a
4 1/7/18 NULL 1 c
table

Event_id From_time To_time user_id package
1 1/1/18 1/2/18 1 a
2 1/2/18 1/6/18 1 b
3 1/6/18 1/7/18 1 a
4 1/7/18 1/1/99 1 c
table
*

SELECT event_id,
type,
user_id,
package,
time AS from_time,
ISNULL(LEAD(time) over (PARTITION BY user_id ORDER BY time)),’1/1/99’)
AS to_time
FROM t

Join on time interval template

Join on time interval template
SELECT c1,
c2 AS from_time,
ISNULL(LEAD(c2) over (PARTITION BY c1 ORDER BY c2)),’2999-01-01’)
AS to_time
FROM t

Join on time interval template use-cases

• We need to join between two events which didn’t occur in
the exact same time

the exact same time
• When we want to create “contracts” when we have
events only.

the exact same time
• When we want to create “contracts” when we have
events only.
• When we want to consolidate two “contracts” from
different sources.

Join on time interval template questions
• Connecting orders to the browsing session which resulted in the
purchase
• For each month understand which package the customer is.

1 18:00 1 a 2
2 18:20 1 a 3
3 18:59 1 b 4
3 18:59 1 b 4

Deduplication
1 18:00 1 a 2
2 18:20 1 a 3
3 18:59 1 b 4

Deduplication
SELECT event_id,
time,
user_id,
type,
click_num
FROM (SELECT *
row_number() over (PARTITION BY event_id ORDER BY time)) AS
rnk
FROM t)
WHERE rnk=1

Deduplication template
SELECT *
FROM (SELECT c1,
c2,
row_number() over (PARTITION BY pk ORDER BY c2)) AS rnk
FROM t)
WHERE rnk=1

Deduplication template questions
• Return all won opportunities except ones which occur
in the same month (keep only last)

Advance sql - window functions patterns and tricks

More Related Content

What's hot

Similar to Advance sql - window functions patterns and tricks

More from Eyal Trabelsi

Recently uploaded

Advance sql - window functions patterns and tricks

Editor's Notes