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.

🟢Get all my free data science interview resources

// Comment
Got any questions? Something to add?
Write a comment below to chat.

// Let's connect on LinkedIn:

====================
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

Рекомендации по теме

Комментарии

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

arimaauto

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

Heather-bz

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

danielkoo

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

Albertelmstreet

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

eegernades

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?

Art.of.the.Warriors

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

arimaauto

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

isabellaxu

Master SQL Window Functions for Data Science Interviews in 2023

Master SQL Window Functions for Data Science Interviews in 2023

SQL Window Functions in 10 Minutes

SQL Window Functions | Clearly Explained | PARTITION BY, ORDER BY, ROW_NUMBER, RANK, DENSE_RANK

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

Master SQL Window Functions: RANK & DENSE RANK

SQL Interview Questions: Master SQL Window Functions Like a Pro

SQL Window Functions: Explained (with examples)

Master SQL Window Functions in 60 Seconds!

Master Microsoft Fabric in 60 Minutes with This Step-by-Step Tutorial

SQL Tricks | You Must Try This ! Windows Functions

How to use Window Functions in SQL | Short Videos | Master SQL with Bite-Sized Video Tutorials

Mastering SQL Windows Functions | SQL Book for Every Data Enthusiast

Secret To Optimizing SQL Queries - Understand The SQL Execution Order

Ace Your Data Science Interview: Top SQL Questions to Master

Learn how to write SQL Queries(Practice Complex SQL Queries)

Introduction to Window Functions (Analytic Functions) | Mastering SQL the Right Way

Simple Roadmap to master SQL #Shorts

Data Analyst SQL Interview Question | Window Functions | Moving Average #sqlinterview #dataanalysis

SQL For Data Analysts And Data Scientists - Episode 12 - Window functions

SQL Window Function with Example- RANK, DENSE RANK, ROW NUMBER, LEAD/LAG | SQL Tutorial in Hindi 14

first_value and last_value SQL window functions MASTER CLASS | Advance SQL

Master the SQL SELECT statement part 08: Window Functions

Advanced SQL Tutorial | Subqueries

SQL Window Functions | RANK, DENSE_RANK, ROWNUMBER simplified (with examples)+ BIG NEWS