Common Date Manipulations on Data Science SQL Interviews
ฝัง
- เผยแพร่เมื่อ 4 ก.ค. 2024
- Date and datetime manipulations are really common and necessary to do data science work. The skill and concepts are often tested on data science interviews. Extracting components from the date field like year, day, month is super common in analytics. Often times you’re given a date field and asked to calculate or count some metric over years or months. Let's go over these concepts in this video.
Link to the question: platform.stratascratch.com/co...
______________________________________________________________________
👉 Subscribe to my channel: bit.ly/2GsFxmA
👉 Playlist for more data science interview questions and answers: bit.ly/3jifw81
👉 Playlist for data science interview tips: bit.ly/2G5hNoJ
👉 Practice more real data science interview questions: platform.stratascratch.com/co...
______________________________________________________________________
Timestamps:
Intro: (0:00)
Question for date manipulation: (2:08)
Framework to solve the question: (3:05)
Coding the solution: (6:55)
Manipulating the date: (9:00)
More tips on manipulating dates: (12:24)
Conclusion: (13:51)
______________________________________________________________________
About The Platform:
I'm using StrataScratch, a platform that allows you to practice real data science interview questions. There are over 1000+ interview questions that cover coding (SQL and python), statistics, probability, product sense, and business cases.
So, if you want more interview practice with real data science interview questions, visit platform.stratascratch.com/co.... All questions are free and you can even execute SQL and python code in the IDE, but if you want to check out the solutions from me or from other users, you can use ss15 for a 15% discount on the premium plans.
______________________________________________________________________
Contact:
If you have any questions, comments, or feedback, please leave them here!
Feel free to also email me at nathan@stratascratch.com
______________________________________________________________________
![Advanced Data Science SQL Interview Question [Amazon] (window functions & aliasing)](http://i.ytimg.com/vi/QenwDm5oWdU/mqdefault.jpg)
One of the best SQL channels out there. Please keep the content coming! Thank you!
Am glad i subcribed. You are amazing Nate. I had SQL experience and haven't used it for long time. It is great to have a refresher. Real one
Really love this channel!!! Its so important for DA to pull the data out!!
Thanks for watching! Really appreciate it! :)
I get confidence when I listed to your explanations. Thank you..
Nate, I've been enjoying your content brother👌
Keep doing these contents mate! You are the best
Thank you! More vids to come!
Great Video. Clearly explained.
Very useful! Thank you! Your channel helped me a lot!
I'm glad! Thank you for watching!
Wow, you handled that sample question effortlessly. I'm learning SQL foundations right now and have added the CAST function to my notes in case I have to do it in a SQL dialect that doesn't have the '::' function
That's great! Only postgres has :: for casting but can also use cast(). Most other sql engines use cast() so it's a great function to know.
@@stratascratch hi,
Can you please make a tutorials on the specific topics individually so that we can build the concept intially and then apply it whenever required.Waiting for your reply.
You speak slow and clear
I love you
Oh, thank you for noticing. Yeah, I consciously try to speak slowly and clearly in my videos.
This is really great stuff, this is going to help me out massively. Really like the presentation and clear yet simple to understand explainations
Thanks for watching! I got many more coding videos coming out so I hope you watch them too. If there are any topics you want me to cover, feel free to call them out too.
man you rock ! thank you
step by step explanation , great framework on how to approach any SQL Query !
Thanks! Next week, we'll jump into some advance topics.
@@stratascratch would be great help if you share anyonline blog where i can practice sql interview questions for free. Solutions mostly are not given on many platforms
@@prernakalra5281 StrataScratch (platform.stratascratch.com) has 50 free SQL questions you can use to practice and LeetCode has a few free SQL questions as well. LeetCode sql questions focus mainly on syntax while SS focuses on interview questions (syntax + implementing edge cases/real life scenarios). HackerRank would be the other option which has some free sql questions. If you add all the free questions on those 3 platforms, you should have about 100-150!
@@stratascratch Awesomee I am gonna practice all, I have to give sql online test from some company as part of inrerview process. Thank you so much :)
Great work!
Thanks for watching! Glad you liked it.
Thank you
Thank you very much. I love your content :) Can extract be used to filter by YY-MMMM? Or do you have any other recommendations?
Thank you! Unfortunately, extract can't do YY-MM because it's trying to grab just one component of the date field. Try to_char(). But just remember that YY-MM will be a string. It won't be able to recognize that field as a date.
@@stratascratch Thanks for the tip!
I appreciate your content a lot! I've watched quite a few of your videos, and I'm grateful for your willingness to help others learn. Now, curious as to why you cover mainly SQL and not Python(not complaining though, as a BI, I use SQL, not Python)
Also, would like to see more on APIs and data streams on your channel(how data is collected via APIs, and anything you consider relevant to this topic). Still new to this, but I'm using it at work and would like to get a better understanding( I have a background in math, so SQL wasn't complicated to learn, as I had already covered set theory); the whole API thing seems a bit challenging to understand.
Thanks for the kind words. I mainly cover SQL because I originally started out my channel with a focus on SQL. Over the past year I explored other languages like python and built data science projects just to see if people found them interesting. They did, which is great, so in 2022, I'm hoping to expand to other data science topics like python, probability, statistics, modeling, and projects. My team will be helping to create these videos so you'll see a lot more production in addition to expanded topics!
Very helpful!!!
Hope you enjoy the series! There's dozens of videos
Many thanks brother! I appreciate your help!
Can we use datepart and count together? Something like
SELECT COUNT(DATEPART(YEAR,inspection_date AS DATE))
FROM sf_restaurant_health_violation
WHERE EXTRACT (YEAR FROM CAST(inspection_date AS DATE) = 2015
Your channel is AMAZING() over ( PARTITION by videos)
Glad you like them!
Excellent work
Thanks for watching!
thanks！
thks a lot
Excellent. Immediately subbed. Any plans in the future for CTE walkthroughs and best practices?
Thanks for watching my videos! Yes, there have been some requests for CTE walkthroughs. I've added that topic to my queue of videos and hoping to film that video sometime in late-March and April. I have a few videos coming out in the next few weeks that are going to focus on technical topics that have appeared on data science interviews in 2021. It's a bit time sensitive so I want to make sure I cover those topics first. After that, we'll dive into CTEs!
@@stratascratch Thanks! Of course there are tons of CTE resources but I like your particular style with practical and applicable angle.
@@IrakliChitishvili Thanks so much! Will try to explain CTEs from a practical angle. There's probably a lot to say about CTEs vs subqueries vs temp tables and when to use them
Thanks for the explanation! What if I need to compare dates of year month like "YYYY-MM" and how can I do that after extracting that component?
You wouldn't be able to compare dates if the dates are in the format 'YYYY-MM' since it has a char data type. My advice would be to keep the full date 'YYYY-MM-DD' and do the comparison then. Then once you are ready to aggregate and group, change the dates to 'YYYY-MM'. Hope that helps!
@@stratascratch Thanks Nate, that helps!
Thanks Sir
Thanks for always watching my videos!
👍👍👌
Thanks for watching! We'll get into some advanced topics next week!
So before going through your solution I tried the question and I did not have to cast the column to date datatype and I just used extract and it gave me output. And even when I tried to filter for 2015 year with extract it still worked. But overall great tip. I have always found it difficult to deal with dates in python or even in SQL. No less than a nightmare to be honest..
Thanks for letting me know. Sometimes it works, sometimes it doesn't. It's not always obvious if something is a date dtype but there are ways to check it before you cast. I just cast automatically sometimes.
@@stratascratch Got it!! 😃😃
Could you do some videos on the coding questions in your site? Would love the same explanation method in a different env. Thanks!
Hi sorry what do you mean? The questions on this video are from my site. Do you want me to do questions on another site or platform?
@@stratascratch He might be asking that, along with SQL, can you prepare content for cracking coding interviews as well? Actually that'd be great.. your videos are awesome and I upgraded to the paid subscription..
@@pvgirish7801 Thanks! Many of my SQL videos are of the point of view of cracking the coding interview. Unless there's another type of question or programming language you'd like for me to use?
@@stratascratch yeah.. something like data structures and algorithms or competitive programming for interviews. When We go to Data scientist interviews .. this is the one of the round that we are facing after sql and data science related rounds (at least in India)
@@pvgirish7801 OK got it. Will do that! I'm planning on adding some algo questions to the platform later in the year so I'll create some videos on that once I am able to integrate them on the StrataScratch platform.
👍🏻👍🏻👍🏻
Thanks for watching! Let me know if you have other topics you'd like for me to cover.
Do you have video on Date Manipulations in Excel ?
Unfortunately, I do not. I would have thought there are a lot of resources for Excel?
Awesome! Thank you for being the best teacher in the world..:) Sir, once you're free , can you please make a video on CTE &Temp table. For performance tuning which one is good? Can you create index on both ? explain why for yes and no?
And difference between Temp table & Hash table? which one is more applicable?
Yes, I can definitely do this. I have a queue of other videos right now but this topic will be on the list. I'm hoping I can talk about this in April (sorry long queue of videos lined up =)).
@@stratascratch Great ! thank you sir :)
what if the date format is dd-mm-yyyy
‘extract’ is not a recognized built-in function name. ??? MSSQL
I used datepart(year, ‘datefield’) instead.
Instructions unclear and I manipulated my date and I am in jail now
Just kidding, great video as always
hah! Thanks for watching. You've been a long time watcher so thanks for keeping up with my vids!
I am confused, how Alias was available in group by???
It's postgres...so maybe it's a postgres thing? I've always been able to use alias for group bys
Here's my solution:
select inspection_year, count(*)
from
(
select EXTRACT(YEAR FROM inspection_date) as inspection_year
from sf_restaurant_health_violations
where business_name ilike '%Roxanne Cafe%' and
violation_id is not null
)a
group by inspection_year
order by inspection_year
That's great! Feel free to check on the platform to see if it validates.
Hey Nate, Why no just use 'WHERE year = 2015'? Why can you use 'GROUP BY(year)' but not 'WHERE year = 2015'?
The GROUP BY is necessary because I have a variable 'year' with an aggregate count(violation_id). It's required for me to have a GROUP BY. Great question tho.
@@stratascratch Hi Nate, I think I did not make my question clear. What I meant is why not use 'year' in the WHERE clause since you already defined 'year', as it has been used in GROUP BY(year)
@@kurtji8170 I see what you mean now. I don't think that's possible to use `year` in the WHERE clause due to how SQL is processed. GROUP BY and ORDER BY are processed last so it knows that `year` exists. However, WHERE is processed before GROUP BY and ORDER BY so it doesn't know `year`. I think this is why I still had to extract the year from the date field in the WHERE clause.
@@stratascratch That explains the confusion, thanks!
how do i select all records that have an event datetime witin 1 hour before, for example mysql select all that time_diff less than 1 hour from datetime? its for a reminder email i want to make on a cronjob can you help with this sql query?