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

No video

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

Рет қаралды 47,048

Күн бұрын

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...
🟢Get all my free data science interview resources
www.emmading.c...
🟡 Product Case Interview Cheatsheet www.emmading.c...
🟠 Statistics Interview Cheatsheet www.emmading.c...
🟣 Behavioral Interview Cheatsheet www.emmading.c...
🔵 Data Science Resume Checklist www.emmading.c...
✅ We work with Experienced Data Scientists to help them land their next dream jobs. Apply now: www.emmading.c...
// 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

Пікірлер: 54

@emma_ding 2 жыл бұрын

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 2 жыл бұрын

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 2 жыл бұрын

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

@siddharthpathak2180 2 күн бұрын

I just finished a couple SQL crash courses and this is by far the BEST and most comprehensive refresher video for window functions I watched. Kudos!

@QuantumImperfections 2 жыл бұрын

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 2 жыл бұрын

Thanks! Glad you find it helpful :)

@raquelc.4073 Жыл бұрын

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

@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. 💛

@austinbai9641 2 жыл бұрын

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

@xiaomengwu3399 2 жыл бұрын

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

@emma_ding 2 жыл бұрын

Wow, thanks!

@alienfunbug 2 ай бұрын

This video is so condensed and packed full concise and well explained materials. Thank you for the fantastic content.

@osoriomatucurane9511 7 ай бұрын

This is awesome, crystal clear!

@danielkoo8000 2 жыл бұрын

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!!! 👍

@FelipeCampelo0 2 ай бұрын

I have been storing so many CTEs using the WITH statement lol. I think these window functions could make my queries less verbose. Great content!

@TechWithAbee 2 ай бұрын

Thank you very much! 🔥

@swastikchowdhury9655 Жыл бұрын

This was very helpful. Beautiful content

@sw2144 2 жыл бұрын

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

@maweryoutube9852 10 ай бұрын

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. 😊

@sofiayz7472 2 жыл бұрын

Great content as always!

@kirusha_kras 2 жыл бұрын

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 2 жыл бұрын

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 2 жыл бұрын

Thank you! 😃

@Cplanet-uo1sf 4 ай бұрын

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.

@nishantjain1469 2 жыл бұрын

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

@rajmaharjan466 Жыл бұрын

Amazing explanation!

@emma_ding Жыл бұрын

Glad you found it helpful, Raj! 😊

@stonewatertx 2 жыл бұрын

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 2 жыл бұрын

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 2 жыл бұрын

" 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

@user-ie5dm8zc8p 11 ай бұрын

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

@djjiang3718 2 жыл бұрын

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 2 жыл бұрын

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

@emma_ding 2 жыл бұрын

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 2 жыл бұрын

@@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!

@stonewatertx 2 жыл бұрын

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?