Solving Medium SQL Interview Questions on Analyst Builder

Questions Page on Analyst Builder: www.analystbuilder.com/questions
Tech Layoffs: www.analystbuilder.com/questi...
Separation: www.analystbuilder.com/questi...
Let's look at how we can solve a medium 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

[@fishregulator]

I've been doing data analytics for almost 3 years now and I still learned a few things watching this. Appreciate all you do Alex 🙏

[@raphaelugwoke8283]

Happy New year Alex, came across your channel December last year and it's been a hell of a ride, keep up the good work mate

[@chiragmaniyath8136]

I started solving SQL daily because of this series. Thanks a lot and please do continue

[@wellingtonporto2682]

Amazing lesson, Alex! Now, I feel confident to delve deeper into SQL knowledge through the AnalystBuilder website, and more importantly, to excel in SQL interview questions. Thanks a million!

[@rebekahmenn2118]

This is great! I'm taking the free Harvard SQL course online, and seeing that these queries are medium-difficulty is encouraging to me... everything made sense and is very straightforward. I really look forward to learning more SQL and getting into this industry!

[@kre45]

Just started learning sql on Udemy this week and I am doing pretty good so far with answering your interview questions

[@Siejec]

Alex never fails to show something fresh and interesting! Props!

[@nicjohnson3812]

Great content. Your presentation setup and communication skills are superb. One minor suggestion is to be mindful of formatting best practices e.g. each column name on the next line preceeded by its separator comma. I just unraveled a spaghetti noodle factory of self-taught SQL and the poor formatting added a lot of time. Reading code that is consistently formatted speeds up an analyst's work and following formatting best practices is a sign of your professionalism in an interview.

[@adhamazzam433]

Thank you very much, Alex! I just finished watching all your boot camp videos. They are really very helpful for beginners. And thank you for recommending this Analystsbuilder, which is really useful for practicing ❤❤❤really appreciate your help and hard work 😊

[@SkedlerReports]

Very good video! Good luck to all who read this and are looking for a new job!

[@kavalisreedhar8288]

Your videos are helping me a lot🙏

[@princesrivastava4475]

Best thing i learn to write down what we need to do in query 🎉🎉🎉

[@malcorub]

Some of the easy questions start turning into medium questions when you consider the nested functions like with the COSTCO ROTISERIE CHICKEN scenario. SELECT ROUND(CAST(SUM(lost_revenue_millions) AS DECIMAL), 0) AS Total_Loss FROM sales; I have an easier time with temp tables, stored procs, creating and inserting into tables than I do nested functions. But still, great learning tool Alex!!!!!

[@samscholar28]

If things went well, I will share my story and main thanks would be to you and your channel , thank you so much for all of your efforts 🙏

[@AAT977]

Loving these practice videos

[@AlexTheAnalyst]

Glad to hear it! I like them too - super fun :)

[@stingrgaming]

I think these should be classified as Easy not Medium

[@JaviLomba-fm2rf]

Agreed, medium shouldn't be something you can solve within 5 minutes

[@szidzse]

i wish a medium challange on hackerrank would look like this.

[@venkatsadineni1304]

Excellent Info and useful for interview preparation.

[@mohamedtarek2056]

This is really a great platform

[@reliablevariable1615]

As these interview questions get more difficult, it also gets more important to validate the data. In the second example, the data set is small enough to visually confirm that the error is uniform. If that dataset was 10,000+ rows long, it will not be practical nor would it be possible to validate the data visually. I’d be asking what types of assumptions do I have with the text and how do I write queries that would check against those assumptions. (E.g., do the names have any trailing or leading space? Is the naming convention for the error always a concatenation of the ID at the beginning or can the error show as First Name and then ID?) Depending on how big your testing dataset is and who you are speaking to, the thought process can be scaled up or down accordingly.

[@Earthlighte]

Thank you for this...❤

[@kehindekehinde9719]

Thanks for sharing always

[@newenglandnomad9405]

You the man.

[@BeccaNiederkrom1]

I've shared your channel with so many Alex, so good! I am an "old" lady now lol but thinking of documenting my journey of learning SQL and seeing where it takes me. =) Maybe inspire some Gen Xers.

[@AlexTheAnalyst]

Haha you don't look old at all! I really appreciate it! :D

[@BATMAN-sh8mm]

Honestly these questions are easy level

[@ivanko-nebo]

Hi Alex. There is an issue in "Senior Citizen Discount" moderate SQL question. The task says "If a customer is 55 or above they qualify" - this should be meaning ">=" condition. But it is not accepted as correct by the system. I get "your solution is correct" only if I change to ">", which contradicts the task.

[@bahorvan7990]

He, Alex! Thanks for the videos) looking forward to python tasks) anyway, have you checked the links to udemy courses on different geo? I'm from Kazakhstan and I can't go to the courses you recommend. I've just recently been thinking about taking a statistics course for ds and here you are😊

[@tranceformeeer]

Maybe using VPN helps. Did you try it?

[@davita6379]

hey,alex for Cake vs Pie Question(Hard) does using Pivot function make more sense?because its advance and its corresponds to level of challenge. this is how i solved it SELECT date_sold,abs(Cake-Pie),case when Cake > Pie then 'Cake' Else 'Pie' end as type FROM ( SELECT date_sold,product, coalesce(amount_sold,0)as amount_sold FROM desserts ) AS SourceTable PIVOT ( SUM(amount_sold) FOR product IN ([Cake], [Pie]) ) AS PivotTable;

[@isaiahsmith6946]

Thanks for the website Alex. One thing I would say is after doing medium SQL questions on websites such as stratascratch the ones on analyst builder are much easier. I've only been doing SQL a couple months and can get a majority of the medium questions correct on your website but fail to get most of them on other websites. If I'm applying to entry level DA and DS roles should I be ok if I can answer the medium questions on analyst builder?

[@AlexTheAnalyst]

I've found these to be very accurate to real world interviews

[@TheSupersayan6]

i'm a little confused about the Senior Citizen Discount question, i've seen the solution it says to use the timestampdiff but is there any alternate way to solfe this question in mysql? #SELECT customer_id #FROM customers #where 2023 - year(birth_date) >= 55 #where date_sub('2023-01-01',birth_date) >= 55 #order by customer_id asc; i've tried a few different ways but not getting the desired output thanks.

[@ItisthatisKarthik]

Hey Hi Alex, I am facing issue with downloading the excel dataset for the PowerBI final project from github so could you please resolve that issue if possible I have even commented on that and shared my mailID

[@rickball3147]

Been working through your exercise prompts. I have a quick question. In MSSQL why is it required to CAST int into decimals to conduct equations between columns? It is not required in MYSQL.

[@AlexTheAnalyst]

Different version of SQL require different things. MySQL automatically handles that conversion while MSSQL needs it to be explicitly done.

[@nicjohnson3812]

Although both are tSQL, the vendors implemented their version differently. A common observation between different tSql versions from between vendors are features from historical providers are completely absent in other versions. E.g. I solved many date problems in MSSQL but the needed functions to check a weekday were missing from another tSql version. Getting proficient in using official reference guides / api docs is recommended. Help forums may send you down a rabbit hole if the solution isn't relevant to your specific version and release. Finally, making the switch to plSql from tSql is not easy at first. Oracle is plSql, msSql is tSql. It's like you moved the light switch in the bathroom and you keep trying to turn it on in the old spot, then finally you find it at 2am on the first try.

[@MichelleLe-om3fv]

I guess everyone has a different definition of easy, medium, and hard because datalemurs easy questions are hard for me 😅. Id say this is easy - medium, too

[@DANNYEL20122]

Hi please can we get the dataset to work on our own 🙂

[@malcorub]

All the Analyst Builder free question datasets appear to be small enough to copy from the site and paste into Excel.

[@idwtv534]

Is it just me or does anyone else also feels these are beginner level problems?