Solving HARD SQL Interview Questions on Analyst Builder

Questions Page on Analyst Builder: www.analystbuilder.com/questions
Kelly's Third Purchase: www.analystbuilder.com/questi...
Temperature Fluctuations: www.analystbuilder.com/questi...
Let's look at how we can solve a Hard SQL technical interview question. In this series we work our way through easy, medium, hard, and very hard SQL interview questions that you may see in a technical interview.
____________________________________________
RESOURCES:
💻Analyst Builder - www.analystbuilder.com/
📖Take my Full MySQL Course Here: bit.ly/3tqOipr
📖Take my Full Python Course Here: bit.ly/48O581R
📖Practice Technical Interview Questions: bit.ly/46pDqqL
Coursera Courses:
Google Data Analyst Certification: coursera.pxf.io/5bBd62
Data Analysis with Python - coursera.pxf.io/BXY3Wy
IBM Data Analysis Specialization - coursera.pxf.io/AoYOdR
Tableau Data Visualization - coursera.pxf.io/MXYqaN
Udemy Courses:
Python for Data Science - bit.ly/3Z4A5K6
Statistics for Data Science - bit.ly/37jqDbq
SQL for Data Analysts (SSMS) - bit.ly/3fkqEij
Tableau A-Z - bit.ly/385lYvN
Please note I may earn a small commission for any purchase through these links - Thanks for supporting the channel!
____________________________________________
BECOME A MEMBER -
Want to support the channel? Consider becoming a member! I do Monthly Livestreams and you get some awesome Emoji's to use in chat and comments!
/ @alextheanalyst
____________________________________________
Websites:
💻Website: AlexTheAnalyst.com
💾GitHub: github.com/AlexTheAnalyst
📱Instagram: @Alex_The_Analyst
____________________________________________
All opinions or statements in this video are my own and do not reflect the opinion of the company I work for or have ever worked for

[@MuhamadRidzuan-ei4qn]

Hi Alex! From Malaysia here, i just want to tell you i have just landed an interview for Data Analyst job although i am in Civil Engineering background.I have been following you since last June and follow learned all your bootcamp. I just want to tell you thank you so much!

[@4b1dd3n]

Hey, I'm from Malaysia too, and also have a completely different background. Do you mind if I ask you whether you applied directly or via linkedin/jobstreet etc? And specifically what role did you apply for considering zero work experience in data analytics? I'm planning to start applying after completing Alex's analyst builder course. Any tips to land an interview would be greatly appreciated 😊

[@MuhamadRidzuan-ei4qn]

@@4b1dd3n I just follow Alex resume format and create some portfolio website for my work. And yeah i apply through linked

[@lovelyintheisland]

Thank you Alex. I listen to your videos in the car on my to work then I watch it in the evenings to practice. Very helpful and inspiring to learn and achieve more in my career.

[@brendansully12]

This channel looks like a WEALTH of information! Thank you for making it all

[@yeerehneh]

Found ur channel a few days ago as an aspiring data analyst and lovelovelove everything!!!! Pls keep up the good work thanku

[@user-in3br6dw1r]

Alex ! This Channel Just a Tree of Best Learning Knowledge ! Thx !

[@Bobthetomado]

I've ran into similar problems of StrataScratch, but the Analyst Builder question should be a bit more clear on if the discount only applies to the 3rd transaction ever made, or every 3 transactions.

[@Nyrus62]

Alex! Your content has been so huge in my journey to become a data analyst. I’m working on doing some projects and putting together my portfolio, but man it’s so hard to put all of the skills together and know what to do and when to use certain tools. I’m starting to get some of those valley of despair feelings. Do you, or anyone else for that matter, have any advice or tips on getting through knowing the technical skills but not knowing how to implement them?

[@therealmarshal]

thx Alex, you are doin well

[@StandbyxP]

Great video as always Alex! Just one question since I am still learning: Does the "DATEDIFF(t1.date, t2.date) = 1" always mean 1 day difference? Because I thought it is mandatory to put an intervall before putting (start_date,end_date) inside the parenthesis of DATEDIFF(), to determine if you want year, month, day etc. as difference.

[@user-bv2qd5wt1q]

Also, as far as I now, datediff substracts olddate from newdate so the result is minus something?

[@andiglazkov4915]

😊 thanks

[@malcorub]

I solved it like this in SSMS. But I don't think temp tables are allowed in Analyst Builder. SELECT [date] ,[temperature] ,[temperature]-LAG([temperature]) OVER(ORDER BY DATE) AS 'LAG' INTO #TEMPY FROM [temperatures] SELECT [DATE] FROM #TEMPY WHERE [LAG]>0 DROP TABLE #TEMPY

[@carlosoviedo5685]

I now this is no the place to ask this but please; can make a video of import and export data from managment sql server and cover the thing with the wizard? plase you are really good explaining things

[@carlosoviedo5685]

never mind I found where you talked about it

[@dianadennis7225]

re: Q2 shouldn't filter in the ON clause; the ON clause should only include join criteria. Poor practice and readability matter at this level

[@PRIYANKAREDDY-ln3hu]

Let's assume you have the following three tables: Students table: Columns: StudentID (Primary Key), FirstName, LastName Courses table: Columns: CourseID (Primary Key), CourseNameEnrollments table: Columns: EnrollmentID (Primary Key), StudentID (Foreign Key referencing Students table), CourseID (Foreign Key referencing Courses table), Grade Write a SQL query that retrieves the average grade for each course. The result should display the CourseName and the average Grade for each course. If a course has no grades recorded, it should still be included in the result with an average

[@someshkumar6796]

select C.CourseName, avg(Grade) from Courses C left join Enrollments E on C.CourseID = E.CourseID groupby C.CourseName (Left join will include all the courses even if there're no grades to be found)

[@vladton84]

What about 6th, 9th, 12 th transactions?

[@Helmofthedominator25]

Kelly's 3rd Purchase. That code is working only if the customers don`t have the 6th purchase, to solve that problem you need to write the correction in code like when the row_number is = 3 then it must restart and begin again from 1 to 3, and on and on and on and on. Sample code : WITH CTE AS ( SELECT *, (ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY transaction_id )-1) % 3 + 1 AS third_transaction FROM purchases ) SELECT customer_id, transaction_id, amount, (amount - amount *0.33) AS discounted_amount FROM CTE WHERE third_transaction = 3

[@JoshuaNichollsMusic]

8:30 I’m yelling at the screen “Use QUALIFY!!” I use window functions all the time and QUALIFY is the simplest solution for these situations where you need to use the output of a window function, in my experience. Maybe it’s only a Snowflake SQL thing 😅

[@ak6877]

So far I have used qualify on snowflake and teradata only. SQL server doesn’t have it and that’s what most people would use also Qualify isn’t ANSI

[@AlexTheAnalyst]

Haha yes, Qualify would be great in Snowflake, but isn't available in MySQL.

[@AltF4Esc]

I love this, but you should show every third purchase. The 1st 3rd purchase wont get you very far. Where mod(row_num, 3) = 0 Mod returns the remainder of long division

[@pastamaster51]

Agreed. How can we code it in way that fits in case someone has 7, 10, or more purchases?

[@Mister_Garibaldi]

@@pastamaster51 WHERE row_num / 3 = CAST((row_num / 3) AS INT) However, the problem is only for the third purchase, not every third purchase

[@AltF4Esc]

@@pastamaster51 where mod(row_num, 3) = 0 Mod gives the remainder of the division

[@Bobthetomado]

I agree. There isn't a problem of the guy in the video. Its an issue of wording in the question. But it would have been good for him to say "In an interview you should clarify"