WINDOW FUNCTIONS | Advanced SQL
Вставка
- Опубліковано 5 сер 2024
- TRY LATITUDE FOR FREE 👉 bit.ly/3o9iGlX
Exclusive Data Analysis Content ➡ mochen.info/ ⬅ Join My Mailing List
/ mo_chen ⬅ Support me on Patreon
/ discord ⬅ Join our Discord Data Community
/ mo-chen1 ⬅ Daily tips & advice on how to enhance your data analysis skills
/ mo_chen1 ⬅ My IG is personal -- only follow if you're interested in non-data stuff as well
/ mo_chen_1 ⬅ Figuring out what I want to do in this space...
• Data Analysis Portfoli... ⬅ Portfolio Projects Playlist: End-to-end, FREE guided portfolio projects covering Excel, SQL, Tableau, Power BI and Python
┋Professional Certificates┋
DataCamp Certifications ➡ datacamp.pxf.io/c/4216860/205...
Google Data Analytics Professional Certificate ➡ www.coursera.org/google-certi...
Google Advanced Data Analytics Professional Certificate ➡ www.coursera.org/google-certi...
Tableau Business Intelligence Professional Certificate ➡ imp.i384100.net/TableauBIAnalyst
Microsoft Power BI Data Analyst Professional Certificate ➡ www.coursera.org/professional...
IBM Data Science Professional Certificate ➡ imp.i384100.net/IBMdataScience
Google Project Management Professional Certificate ➡ imp.i384100.net/GoogleProject...
┋Timestamps┋
00:00 Intro
01:50 Import csv files
03:06 CREATE TABLE
04:18 psql command line tool
08:02 OVER
11:12 PARTITION BY
14:56 ROW_NUMBER
18:00 RANK and DENSE_RANK
20:38 LAG and LEAD
24:20 Bonus
25:23 Outro
┋The project┋
Github repo for code ➡ github.com/mochen862/window-f...
New York City Airbnb Open Data ➡ www.kaggle.com/datasets/dgomo...
Thanks so much for taking a little time out of your day to watch this!
TRY LATITUDE FOR FREE 👉 bit.ly/3o9iGlX
This is the best explanation for Window Functions, even for courses where you need to pay. Thank you
I'm so glad you think so! Thanks for watching!
I've always been waiting this video since the previous about SQL released. Really appreciate your help !!!
Thanks so much for watching 😄
beautiful stuff. Thank you from Ghana!!
Pretty cool, not a lot of WINDOW and analytical queries out on youtube, definitely a solid niche to be explored. Thanks 👍.
You are Amazing as a Teacher
Omg yes yes! 🎉 been waiting for this since your previous sql video! we really appreciate you, bro!!
Thanks so much for watching Vinnyke 😄
That was awesome. Thank you!
This really cleared things up for me on the topic. Thank Mo!
Thank you for the tips! Window functions are very important to understand especially for data analyst technical interviews.
Thanks so much for watching Baturalp 😃 I'm glad you liked the video!
Thanks as always Mo, your explanation are always helpful. This is perhaps the best tutorial on Window Functions I have encountered so far
Glad it was helpful! Thanks a lot for watching!
Wooh! that’s some sort of content. Got my basics cleared😁.
Glad it was helpful!
one if the best tutorials on window function .. thanks a lot for your effort ..waiting for more :)
Thanks Mo, after a while, your explanation on the OVER(PARTITION BY) query is the one that made me understood it best.
Big thanks.
I’m glad you liked it, thanks a lot for watching 😃
nice comment
Learned a lot in this video, thank you so much. This is much more relatable when I have years of working experience than in college
Hi Mo, thanks for yur video! This is really helpful , the content is very clear and straight forwards.
Keep it up.
That's very kind of you to say! Thanks so much for watching 😃
This is by far the best explanation of Window functions I've ever watched, thank you so much for your efforts 👏👏
Glad it was helpful!
perfectly explained!👌👌👌👌
So far the best explanation of window function! Thank you!
Glad it helped!
this is amazing, i was searching for these content for such a long time, Thank you MO
Glad you found it useful! Thanks for watching 😃
Dude, you're good. Cheers!
This is an super amazing introduction to this topic. BIG THANKS for this! You did absolutely incredible work demonstrating this!
But I have a question, which I cannot find be covered anywhere: Is it somehow possible to use value(s) from the current row and compare them them to any other row in the windows?
Let's say, to stick with your example: I want to find the FIRST_VALUE booking_id over the entire resultset, whose price is 2* the price of the current row and if there is no such one, then just put a NULL in the column of the current row.
Is it possible to do this using Window Functions only?
Thanks in advance and best regards.
BEST OF THE BEST .. Many thx. clear & to the point
Glad it helped!
this is pretty cool, thanks for the video!
Glad you liked it!
Thanks, a lot of good info.
Thanks for watching Caribou Data Science!
Lets go Mo!!!! Thanks for this!!! 👌👍🙏
Great to see you again on the channel! Thanks for watching Alvin 😃
@Data With Mo no problem bro! Always here for the rich content! I started learning SQL last week since I have a advance understanding of python now. Looking to transition and learn advance skills in SQL to land a job at at the market. 👍👌🙏
Mo, Im a huge fan of the channel! You have very good info! Just one feedback on this video. You start running without us knowing how to run 😂. Maybe for a next time a short introduction of the different type of functions and why to use them, would be great. For the rest, thanks... as always! 🤩🤩
I would love to go to Japan and the US one day my friend. 💯Thanks for the content.
Can't argue with that! I've been to California a couple of times, but would love to visit Japan myself too! Thanks a lot for watching 😃
please make a video on which courses we should take to be a data analyst , there's a bunch of courses and videos but we don't know which one is the absolute best and appropriate for the industry .
Hi Alester 👋 Please check out my roadmap here datawithmo.com/. This should help 😃
Thanks Mo
Thanks for watching Prashant 😃
Thanks for the super content...
Thanks for watching Niranga 😃
Hello,
I just wanted to ask at 24min29sec, instead of having all the code in a subquery named a and inside a query to filter the top3, wouldn't be easier to have one sole query and adding at the end a "HAVING top3_flag = "Yes" " ?
Thank you, Wow, your explanation very very Good
Thanks so much for watching Elfrid 😁
Great content. Greetings from Brazil
hey IHAS 👋 Thanks a lot for watching!
Really helpful ❤
Thanks so much for watching Mohit 😄
Thank you so so much for this nice tutorial.
I can only say that it would be a bit clearer if you use ORDER BY when you used PARTITITON BY for the same column/s. Because without it results are scattered and and difficult to follow.
Also. please, could you explain for your last real world example, will the column before the last be the same if you used DENSE_RANK ??
Thank you.
the bonus question can we also do like where rank
can this be used with mysql as well????
I am waiting for this one
Nice one! Hope you liked it 😃
I’m here again for knowledge and your handsomeness - what a great deal 🤤
Thank you so much for sticking around on the channel!
Big Mo 🤝.
Thank you for watching 😃
Am I watching because I want to learn about data analytics or because he’s very handsome? Yes. The answer is yes.
Thanks Anna 😃
Lol 😂 same
you ever had any SQL/Python coding tests during interviews?
Not actual coding tests but walking people through how I would solve the problems using code yes. Thanks a lot for watching!
nice comment
explain the OVER Clause
It is explained throughout the video. Please feel free to check out other learning resources if my explanation didn't get across 😁 Thanks a lot for watching!
How I can learn Data analysis in a month fast? Please give me a roadmap.
Please see a roadmap here datawithmo.com/
Sir do you earn 100000 pound sterlings a year in UK? Plz reply. Thanks a lot.
My organisaton pays me well but I don't want to disclose how much exactly. You can find lots of salary information on Glassdoor. Thanks for watching Gourab!
Great video as always!
I would like to make a suggestion. Tbh, you could remove your face off the screen when it comes to teaching/showing things on the screen. This way, we could have a better focus on the learning and we won't be blocked from any important details we need to see.
Thank you for watching! Noted 😃 In this video, there's nothing happening at the top right, that's why I left my face there.
@@mo-chen Totally understand your motive, but it can be a little distracting for learners 😅
Mine is Bora Bora Mo.
Great to have you here on the channel Simon 😃
I don't think data analyst job is a bright future anymore.
It will be replace by AI so quick, cause AI grow faster than we can imagine.
Like I said, I welcome AI onboard. Being a data analyst is not just about writing code which AI is very good at. I'm more than happy for AI to do all of my boring, repetitive tasks such as data cleaning and transformation. You need to understand the business problems, engage with your stakeholders to come up with solutions, dashboards, processes that suit their needs. AI will certainly change the job tasks of data analysts, in a good way I believe - as rather than spending time doing monotonous tasks, I can spend more time doing value-add tasks.
Hi everyone, just a quick question if anyone has any idea on this issue.
I tried importing the .csv to populate the table using psql and it works all right.
However, I was curious and tried to import it via "right-click on the bookings table, import/exoprt data" and got this error message:
ERROR: invalid input syntax for type integer: "40.76489"
CONTEXT: COPY bookings, line 9, column host_id: "40.76489"
I tried exploring on why this happens and found out that postgre didn't manage to read the .csv file as well as via psql. I already made sure that every data constraints are correct, alrady check the 'header' option, and made sure the delimiter is comma (,)
I'm trying to explore any possibilities to troubleshoot things myself, but ended up stuck hahaha
nice comment