WINDOW FUNCTIONS | Advanced SQL
HTML-код
- Опубликовано: 30 май 2024
- TRY LATITUDE FOR FREE 👉 bit.ly/3o9iGlX
┋Resources to take your data analytics skills to the next level┋
Ultimate Data Roadmap: Learn applicable data skills that you can actually use in your workplace in 30 days ➡ mochen.info/ultimate-data-roa...
Ultimate Data Portfolio: Learn how to create an outstanding data portfolio in 3 hours ➡ mochen.info/ultimate-data-por...
1:1 Mentorship: Learn how to advance your career ➡ mochen.info/1-1-mentorship-lp/
Analyst Fundamentals Test: Find out your score in 10 minutes ➡ mochen.info
CourseCareers: Start the FREE Data Analytics Introduction Course Now ➡ coursecareers.com/a/mochen?co...
Portfolio Projects Playlist: End-to-end, FREE guided portfolio projects covering Excel, SQL, Tableau and Python ➡ • Portfolio Projects
┋Community & Socials┋
/ discord ⬅ Join our Discord Data Community
/ mo_chen1 ⬅ My IG is personal -- only follow if you're interested in non-data stuff as well
/ mo-chen1 ⬅ Daily tips & advice on how to enhance your data analysis skills
/ mo_chen_1 ⬅ Figuring out what I want to do in this space...
┋Professional Certificates┋
CourseCareers ➡ coursecareers.com/a/mochen
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
I've always been waiting this video since the previous about SQL released. Really appreciate your help !!!
Thanks so much for watching 😄
Omg yes yes! 🎉 been waiting for this since your previous sql video! we really appreciate you, bro!!
Thanks so much for watching Vinnyke 😄
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 👍.
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 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 😃
one if the best tutorials on window function .. thanks a lot for your effort ..waiting for more :)
So far the best explanation of window function! Thank you!
Glad it helped!
Dude, you're good. Cheers!
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 😃
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!👌👌👌👌
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. 👍👌🙏
BEST OF THE BEST .. Many thx. clear & to the point
Glad it helped!
Thanks, a lot of good info.
Thanks for watching Caribou Data Science!
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.
this is pretty cool, thanks for the video!
Glad you liked it!
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 😃
Thanks for the super content...
Thanks for watching Niranga 😃
Really helpful ❤
Thanks so much for watching Mohit 😄
Great content. Greetings from Brazil
hey IHAS 👋 Thanks a lot for watching!
Thank you, Wow, your explanation very very Good
Thanks so much for watching Elfrid 😁
Thanks Mo
Thanks for watching Prashant 😃
I am waiting for this one
Nice one! Hope you liked it 😃
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" " ?
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! 🤩🤩
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 😃
Big Mo 🤝.
Thank you for watching 😃
can this be used with mysql as well????
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
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
I’m here again for knowledge and your handsomeness - what a great deal 🤤
Thank you so much for sticking around on the channel!
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
Mine is Bora Bora Mo.
Great to have you here on the channel Simon 😃
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!
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!
How I can learn Data analysis in a month fast? Please give me a roadmap.
Please see a roadmap here datawithmo.com/
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 😅
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