SQL Window Functions: The Key to Succeeding in Data Science Interviews

Window functions are a part of SQL that is useful on a daily basis for data scientists. This video will give you an overview of SQL window functions by explaining:
- When are window functions
- Syntax of window function
- How to define a window
- How to select a function
👉 Window function problems in Leetcode: / sql-window-functions
✔️ 3 types of SQL questions to ace interviews: • Ace Your Data Science ...
✔️ SQL Window Function with Examples • Master SQL Window Func...
✔️ T-SQL documentation by Microsoft docs.microsoft.com/en-us/sql/...
🟢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 What is a Window Function
02:28 Syntax of Window Functions
03:23 Define a Window Frame using Over Clause
04:00 PARTITION BY
04:50 ORDER BY
05:23 ROWS/RANGE
08:35 Aggregate Functions
09:11 Ranking Functions
11:16 Analytic Functions

[@emma_ding]

Thanks DJ jiang for pointing out that negative offset of LEAD or LAG works in PostgreSQL and Snowflake. I was referring to T-SQL that negative offset is not allowed and I should have clarified it in the video. Details here docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver15.

[@tahirmuhammad9646]

Hey Emma, these are great! Can you put this in a playlist called ACE The SQL interview and put future videos here about the topic? :) TYVM!

[@emma_ding]

@@tahirmuhammad9646 Great suggestion, Tahir! Here you go kzbin.info/aero/PLY1Fi4XflWStrBVJc2o9SbSQp9dRt3l9W! :)

[@TechWithAbee]

Thank you very much! 🔥

[@raquelc.4073]

This is the best video about window functions that I have seen so far! Wow! Congrats and thanks!

[@QuantumImperfections]

This..... WAS FANTASTIC! I've got 4+ years of experience in SQL and I needed a quick refresher for window functions and this was exactly what I needed. Thank You!

[@emma_ding]

Thanks! Glad you find it helpful :)

[@danielkoo8000]

Thanks Emma for sharing. What you shared helped me comprehend window functions. Most importantly, I applied those window functions to my day to day work, which boosted efficiency. Appreciated :)

[@LorenzoAnderson-ih5ke]

Super clear and orginised, thank you!!! 👍

[@austinbai9641]

This is the best vedio talking about window fundction on KZbin, thanks Emma! Please keep up the great work!

[@sofiayz7472]

Great content as always!

[@osoriomatucurane9511]

This is awesome, crystal clear!

[@xiaomengwu3399]

Your channel is the best on all DS interviews! well organized, clear and short!

[@emma_ding]

Wow, thanks!

[@swastikchowdhury9655]

This was very helpful. Beautiful content

[@sw2144]

Emma luv u. Your explanations are very elaborative and succinct at the same time.

[@krishnamvats2238]

stopped in between of the video, the best part about the video is that it explains the difference between group by and window function. This is something not many youtuber do. I was struggling to visualize how it happens. Thanks a lot

[@emma_ding]

Glad to hear it was helpful for you, Krishnam! Thanks for watching. 💛

[@maweryoutube9852]

Very clear, detailed, and amazing explanation SUBSCRIBED from more great videos adventure Thank you

[@AVChian01]

Great lecture!!! Exactly what I was looking to solve my problem at work. I am definitely subscribing to your channel.

[@emma_ding]

So happy to hear you found my video helpful! Thanks for watching. 😊

[@rajmaharjan466]

Amazing explanation!

[@emma_ding]

Glad you found it helpful, Raj! 😊

[@arunabhsingh5448]

Keep going!

[@user-yb7tr3hy6j]

awesome

[@djjiang3718]

Hello Emma, your videos are superb and great and very useful! Just one thing, we can use negative value when we use LEAD or LAG, basically lead(date,-1) over (order by date) equivalent of lag(date,1) over(order by date), which I tested in PostgreSQL and Snowflake, and they both works.

[@kirusha_kras]

Yes, but it's syntactic sugar) For example in McSQL it won't work

[@emma_ding]

Thanks DJ for pointing it out! I was focusing on T-SQL docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver15 and should have mentioned it in the video. Thanks again!

[@djjiang3718]

@@emma_ding No worries, you are great! Your videos are very detailed and clear and well organized and very high level! Thank you and Keep up your hard works!

[@ashleyblanchard8993]

Hi Emma, what app do you use to record yourself and the screen?

[@kirusha_kras]

Emma, your content is great. Your English is beautiful and clear. My respect For subscribers: I would like to use the comments to find ways to improve my English. If you are starting your journey in data analytics and you are a native speaker of English, we can be useful to each other) For example, weekly calls or something similar Best regards, Senior Data Analyst from Russia

[@roczhang2009]

Hi there, as a non-native speaker who's working in an English-speaking country, what I found most helpful is not to have English conversations, but to pay closer attention to all the sentences and expressions that come from the native speakers. Think about how you would have phrased your words vs. how they did. Find the difference and work on it. You could use everything as the source for that purpose: movies, youtube videos etc. But this is not to say that having conversations in English is not helpful. It is, but just maybe not as important as many people think. Cuz I know many non-native speakers, who have worked in English every day for many years, but still speak bad English.

[@emma_ding]

Thank you! 😃

[@nishantjain1469]

Shouldn't the SUM() at 9:09 do a cumulative sum since order function is mentioned ?

[@stonewatertx]

Sorry for another dumb question: I tried something like "select rank(a) over(order by a) from t" in PostgreSQL. It'll show error telling me to add WITHIN GROUP. But if I write "select rank() over(order by a) from t" then it's fine. Seems like I can't pass anything in the () after rank. Could anyone clarify?

[@user-ie5dm8zc8p]

Emma: How are you? I am from Brazil. Do you have a video about partitions and frames?

[@stonewatertx]

Great video! One quick question: At 9:10, I thought SUM(value) OVER(ORDER BY value) will return cumulative sum; but SUM(value) OVER() ORDER BY value will return the SUM shown in the video. or is it a PostgreSQL thing? I'm a little confused now.

[@yankeephan87]

There must be an error in her table bc it should be a rolling sum bc the sum(value) cannot be the same if it is ordered by the value for all rows

[@fkarcher7149]

" I thought SUM(value) OVER(ORDER BY value) will return cumulative sum; but SUM(value) OVER() ORDER BY value will return the SUM shown in the video". This is the correct statement. There must have been an error in the video.

[@apamwamba]

You are 100%. Reason: (1.) When ORDER BY is specified and FRAMING is allowed, the DEFAULT FRAME is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW i.e all values up to the current value in the partition ( 2.) When ORDER BY is not specified and FRAMING is allowed, the DEFAULT FRAME is ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING i.e The entire partition

[@Cplanet-uo1sf]

Can you explain how this query works ? SELECT COUNT(p) OVER(order by p ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as p_count, p, c,d FROM `d.t` SELECT COUNT(p) OVER(order by p ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as p_count, p FROM `d.t` Why is the window affected when I specify c and d columns? I would expect to count 1 before, current one and next one. So for first row it would be 2 for next ones except last one 3 and for last one to be 3, because the window is made of 3 items so it will count 3 most of the time. But no.. it returns different counts that does not make any sense to me. I read the bigquery documentation and I really don't get it.

[@minjeongkim986]

would you mind sharing some videos for python coding interview as well?

[@emma_ding]

Hi Minjeong, this one might be helpful to you. kzbin.info/www/bejne/nnLUmGWaobOYi8U

[@nishantjain1469]

The order on the playlist is not correct

[@billj2229]

It's sad that you can't use variable to specify rows in window functions.

[@IKostman]

Nice job young lady 🙂

[@MrPushupp]

but why does the rank function skip 4 and 6 though??

[@apamwamba]

Its by design. Think Olympics when there is a tie.

[@ashleyblanchard8993]

i don't get rank and dense rank

[@andyw810]

The table at ~9:00 is wrong. should go with running total, running average, etc.