Common Date Manipulations on Data Science SQL Interviews

[@alphar85]

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]

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

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

[@BJTangerine]

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]

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]

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

[@bandhammanikanta]

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

[@alanlinaaa]

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

[@stratascratch]

Thanks for watching! Really appreciate it! :)

[@luckychitundu1070]

Nate, I've been enjoying your content brother👌

[@prernakalra5281]

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

[@stratascratch]

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

[@prernakalra5281]

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

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

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

[@rozario1309]

Great Video. Clearly explained.

[@owenbird1075]

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]

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.

[@mahesh26sai]

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

[@stratascratch]

Glad you like them!

[@Firatayrilik]

Keep doing these contents mate! You are the best

[@stratascratch]

Thank you! More vids to come!

[@siranxie844]

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

[@stratascratch]

I'm glad! Thank you for watching!

[@yuthpatirathi2719]

Thank you

[@glstnlev]

man you rock ! thank you

[@IrakliChitishvili]

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

[@stratascratch]

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]

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

[@stratascratch]

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

[@de_da_oo]

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]

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!

[@hudatolah]

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

[@anandvyavahare2031]

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]

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]

@@stratascratch Got it!! 😃😃

[@ghinwamoujaes9059]

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]

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]

@@stratascratch Thanks for the tip!

[@lizard_sinno]

Great work!

[@stratascratch]

Thanks for watching! Glad you liked it.

[@huanchenli4137]

Very helpful!!!

[@stratascratch]

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

[@mysteriousbd3743]

Excellent work

[@stratascratch]

Thanks for watching!

[@NotFound-iu8wx]

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

[@stratascratch]

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

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

[@kurtji8170]

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

[@stratascratch]

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]

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

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

@@stratascratch That explains the confusion, thanks!

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

[@veemukta]

Do you have video on Date Manipulations in Excel ?

[@stratascratch]

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

[@ryandavis280]

thanks！

[@priyankasarkar6600]

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]

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]

@@stratascratch Great ! thank you sir :)

[@yousfoss4367]

thks a lot

[@judyhe686]

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]

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]

@@stratascratch Thanks Nate, that helps!

[@Soulfulreader786]

what if the date format is dd-mm-yyyy

[@weiyang2116]

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

[@stratascratch]

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]

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

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

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

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

[@shalupatil]

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

[@stratascratch]

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

[@mdabulkalamazad6775]

Thanks Sir

[@stratascratch]

Thanks for always watching my videos!

[@shobhamourya8396]

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]

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

[@anuragsingh4766]

👍👍👌

[@stratascratch]

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

[@459B]

👍🏻👍🏻👍🏻

[@stratascratch]

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