Common Date Manipulations on Data Science SQL Interviews

Рет қаралды 24,631

Күн бұрын

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
______________________________________________________________________

Пікірлер: 82

@oagengtembo226 Жыл бұрын

You speak slow and clear I love you

@stratascratch Жыл бұрын

Oh, thank you for noticing. Yeah, I consciously try to speak slowly and clearly in my videos.

@alphar85 2 жыл бұрын

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

@brianle8899 2 жыл бұрын

One of the best SQL channels out there. Please keep the content coming! Thank you!

@bandhammanikanta6302 2 жыл бұрын

I get confidence when I listed to your explanations. Thank you..

@alanlinaaa 2 жыл бұрын

Really love this channel!!! Its so important for DA to pull the data out!!

@stratascratch 2 жыл бұрын

Thanks for watching! Really appreciate it! :)

@luckychitundu1070 2 жыл бұрын

Nate, I've been enjoying your content brother👌

@rozario1309 2 жыл бұрын

Great Video. Clearly explained.

@siranxie844 2 жыл бұрын

Very useful! Thank you! Your channel helped me a lot!

@stratascratch 2 жыл бұрын

I'm glad! Thank you for watching!

@Firatayrilik 2 жыл бұрын

Keep doing these contents mate! You are the best

@stratascratch 2 жыл бұрын

Thank you! More vids to come!

@BJTangerine 2 жыл бұрын

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

@stratascratch 2 жыл бұрын

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.

@rohith98 2 жыл бұрын

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

@owenbird1075 3 жыл бұрын

This is really great stuff, this is going to help me out massively. Really like the presentation and clear yet simple to understand explainations

@stratascratch 3 жыл бұрын

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.

@prernakalra5281 3 жыл бұрын

step by step explanation , great framework on how to approach any SQL Query !

@stratascratch 3 жыл бұрын

Thanks! Next week, we'll jump into some advance topics.

@prernakalra5281 3 жыл бұрын

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

@stratascratch 3 жыл бұрын

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

@prernakalra5281 3 жыл бұрын

@@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 :)

@yuthpatirathi2719 2 жыл бұрын

Thank you

@glstnlev 2 жыл бұрын

man you rock ! thank you

@lizard_sinno 3 жыл бұрын

Great work!

@stratascratch 3 жыл бұрын

Thanks for watching! Glad you liked it.

@mysteriousbd3743 3 жыл бұрын

Excellent work

@stratascratch 3 жыл бұрын

Thanks for watching!

@mahesh26sai Жыл бұрын

Your channel is AMAZING() over ( PARTITION by videos)

@stratascratch Жыл бұрын

Glad you like them!

@dianadennis7225 2 жыл бұрын

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.

@stratascratch 2 жыл бұрын

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!

@huanchenli4137 3 жыл бұрын

Very helpful!!!

@stratascratch 3 жыл бұрын

Hope you enjoy the series! There's dozens of videos

@ghinwamoujaes9059 3 жыл бұрын

Thank you very much. I love your content :) Can extract be used to filter by YY-MMMM? Or do you have any other recommendations?

@stratascratch 3 жыл бұрын

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.

@ghinwamoujaes9059 3 жыл бұрын

@@stratascratch Thanks for the tip!

@ItsWithinYou Жыл бұрын

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

@ryandavis280 2 жыл бұрын

thanks！

@mdabulkalamazad6775 3 жыл бұрын

Thanks Sir

@stratascratch 3 жыл бұрын

Thanks for always watching my videos!

@IrakliChitishvili 3 жыл бұрын

Excellent. Immediately subbed. Any plans in the future for CTE walkthroughs and best practices?

@stratascratch 3 жыл бұрын

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!

@IrakliChitishvili 3 жыл бұрын

@@stratascratch Thanks! Of course there are tons of CTE resources but I like your particular style with practical and applicable angle.

@stratascratch 3 жыл бұрын

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

@yousfoss4367 2 жыл бұрын

thks a lot

@judyhe686 3 жыл бұрын

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?

@stratascratch 3 жыл бұрын

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!

@judyhe686 3 жыл бұрын

@@stratascratch Thanks Nate, that helps!

@anandvyavahare2031 2 жыл бұрын

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

@stratascratch 2 жыл бұрын

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.

@anandvyavahare2031 2 жыл бұрын

@@stratascratch Got it!! 😃😃

@hudatolah 2 жыл бұрын

‘extract’ is not a recognized built-in function name. ??? MSSQL I used datepart(year, ‘datefield’) instead.

@weiyang2116 3 жыл бұрын

Could you do some videos on the coding questions in your site? Would love the same explanation method in a different env. Thanks!

@stratascratch 3 жыл бұрын

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?

@pvgirish7801 3 жыл бұрын

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

@stratascratch 3 жыл бұрын

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

@pvgirish7801 3 жыл бұрын

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

@stratascratch 3 жыл бұрын

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

@Soulfulreader786 Жыл бұрын

what if the date format is dd-mm-yyyy

@anasakt_ 2 жыл бұрын

Do you have video on Date Manipulations in Excel ?

@stratascratch 2 жыл бұрын

Unfortunately, I do not. I would have thought there are a lot of resources for Excel?

@anuragsingh4766 3 жыл бұрын

👍👍👌

@stratascratch 3 жыл бұрын

Thanks for watching! We'll get into some advanced topics next week!

@459B 3 жыл бұрын

👍🏻👍🏻👍🏻

@stratascratch 3 жыл бұрын

Thanks for watching! Let me know if you have other topics you'd like for me to cover.

@shalupatil 2 жыл бұрын

I am confused, how Alias was available in group by???

@stratascratch 2 жыл бұрын

It's postgres...so maybe it's a postgres thing? I've always been able to use alias for group bys

@NotFound-iu8wx 3 жыл бұрын

Instructions unclear and I manipulated my date and I am in jail now Just kidding, great video as always

@stratascratch 3 жыл бұрын

hah! Thanks for watching. You've been a long time watcher so thanks for keeping up with my vids!

@priyankasarkar6600 3 жыл бұрын

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?

@stratascratch 3 жыл бұрын

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

@priyankasarkar6600 3 жыл бұрын

@@stratascratch Great ! thank you sir :)

@kurtji8170 2 жыл бұрын

Hey Nate, Why no just use 'WHERE year = 2015'? Why can you use 'GROUP BY(year)' but not 'WHERE year = 2015'?

@stratascratch 2 жыл бұрын

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.

@kurtji8170 2 жыл бұрын

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

@stratascratch 2 жыл бұрын

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

@kurtji8170 2 жыл бұрын

@@stratascratch That explains the confusion, thanks!

@shobhamourya8396 2 жыл бұрын

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

@stratascratch 2 жыл бұрын

That's great! Feel free to check on the platform to see if it validates.

@JNET_Reloaded Жыл бұрын

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?