Master SQL Window Functions for Data Science Interviews in 2023

In this video, I take an even closer look at window functions by going over three examples using three functions: aggregate functions, ranking functions and analytic functions.
👉 Window function problems in Leetcode: / sql-window-functions
✔️ 3 types of SQL questions to ace interviews: • Ace Your Data Science ...
✔️ Overview of SQL Window Functions • SQL Window Functions: ...
🟢Get all my free data science interview resources
www.emmading.com/resources
🟡 Product Case Interview Cheatsheet www.emmading.com/product-case...
🟠 Statistics Interview Cheatsheet www.emmading.com/statistics-i...
🟣 Behavioral Interview Cheatsheet www.emmading.com/behavioral-i...
🔵 Data Science Resume Checklist www.emmading.com/data-science...
✅ We work with Experienced Data Scientists to help them land their next dream jobs. Apply now: www.emmading.com/coaching
// Comment
Got any questions? Something to add?
Write a comment below to chat.
// Let's connect on LinkedIn:
/ emmading001
====================
Contents of this video:
====================
00:00 Review
01:01 Aggregate Functions Example
02:19 Ranking Functions Example
05:53 Analytic Functions Example
09:10 Advantage of Window Functions

[@danielkoo8000]

Thanks Emma for coaching. Now I comprehend what window functions are and what kind of problems could be resolved with window functions. Appreciated😀

[@ashinzepaul1113]

谢谢你 Emma. 很棒👍

[@dfkgjdflkg]

love your content

[@arimaauto2258]

For 1454 SQL server. this approach needs distinct login_date
select distinct lg.id, a.name
--,lag4,login_date, DATEDIFF(day, lag4, login_date)
from (
select id,
login_date,
LAG(login_date, 4) over(partition by id order by login_date) as lag4
from (select distinct id, login_date from logins) x
) lg
join accounts a
on lg.id=a.id
where DATEDIFF(day, lag4, login_date)=4
order by id

[@user-pr6rj5us9y]

Very useful. really appreciate your effort in giving the educational session to the public!!!

[@eegernades]

Thanks. Sqlzoo window function section is broken with system errors so this helped a lot. Thank you.

[@Albertelmstreet]

You cover everything... This is awesome!!!

[@emma_ding]

Wow, thank you!

[@Art.of.the.Warriors]

I used lead() for the active users question. I guess instead of looking back, you are looking ahead. If the question asked for a start or end of 5 consecutive day period, then it'll matter right?

[@arimaauto2258]

1454 My SQL solution uses lead
select distinct lg.id, a.name
from (
select id,
login_date,
Lead(login_date, 4) over(partition by id order by login_date) as lag4
from (select distinct id, login_date from logins) x
) lg
join accounts a
on lg.id=a.id
where DATEDIFF(lag4, login_date)=4
order by id

[@arimaauto2258]

not use a window function, but group by having in mysql
SELECT
DISTINCT l1.id,
(SELECT name FROM Accounts WHERE id = l1.id) AS name
FROM Logins l1
JOIN Logins l2
ON l1.id = l2.id AND DATEDIFF(l1.login_date, l2.login_date) BETWEEN 1 AND 4
GROUP BY l1.id, l1.login_date
HAVING COUNT(DISTINCT l2.login_date) = 4

[@isabellaxu5572]

hey Emma, I run your solution for the Active User question on Leetcode, but it returns wrong answer. Can you please check?

[@ektajalan2709]

Lag function will not work here, with duplicate login_date per user id. Since the lag function just calculates over rows, here user id 7 has two rows for the date - '2020-06-02', hence consecutive day calculation is wrong. If you dedup/use distinct on the logins table before applying lag function, this solution will work.