SQL Mock Interview (Data Analyst): Departments with the Highest Revenues

In this video, we dive into advanced SQL topics by analyzing and solving 5 real-world business problems in a SQL mock interview. We'll cover joins, grouping, aggregations, CTEs, sub-queries, and window functions. The problems include finding department revenue, tracking user orders, analyzing customer orders, selecting the second-highest order amount, and discovering the highest month-over-month increase in order amount.

Chapters (Powered by ChapterMe) -
00:00 - Introduction to SQL for Analyzing Orders and Revenue
02:23 - Basics of Data Retrieval and Simple Filtering in SQL
03:22 - Advanced SQL Techniques: Joining Tables and Filtering Data
06:02 - Detailed Analysis: Order Breakdown by Department for 2020
12:34 - Utilizing Inner Joins to Enhance Data Accuracy
14:45 - Applying Time Constraints to Queries for Precise Data Analysis
18:48 - SQL Query Testing for Accuracy and Efficiency
23:17 - Advanced Data Grouping and Aggregation Techniques
27:51 - Identifying Key Insights: Ranking and Sorting Data
33:25 - Analyzing Monthly Trends and Departmental Performance
45:09 - Final Thoughts on SQL Query Optimization and Interview Strategies
50:44 - Conclusion and Reflections on SQL for Data Analysis

Want more SQL content?

ABOUT US:
Did you enjoy this interview question and answer? Want to land your dream career? Exponent is an online community, course, and coaching platform to help you ace your upcoming interview. Exponent has helped people land their dream careers at companies like Google, Microsoft, Amazon, and high-growth startups. Exponent is currently licensed by Stanford, Yale, UW, and others.

Our courses include interview lessons, questions, and complete answers with video walkthroughs. Access hours of real interview videos, where we analyze what went right or wrong, and our 1000+ community of expert coaches and industry professionals, to help you get your dream job and more!

#sql #sqlinterview #datascience #businessanalyst

[prateeksenapati]

For the question on "second highest order amount in the fashion department", should we be using DENSE_RANK() instead of RANK() - since if 2 orders have the highest amount then the third order (which is the second highest amount order) will get a rank of 3 using RANK() and a rank of 2 using DENSE_RANK()?

[kaboyodaniel]

Thanks for all you do. Always a joy to follow along. Wish there was more videos like this.

[funtik_]

where o.order_date in (2019 etc) should not give expected result. Need to use year(o.order_date) in(2019, 2020 etc

[dickyalamsyah]

You can’t group by within rank function actually

[mohammadnasser]

at 27:27
do we really need to group by c.first_name and c.last_name?
I mean we already grouping by c.customer_id which has a kind of one-to-one relation with the first and last name

[aliaksandrmatrunich]

order_date>= YEAR(CURRENT_DATE - INTERVAL 5 YEAR);

[mildlydisastrous]

Almost every written query had very serious issues and either won't run at all or would give wrong answers. I'm not talking about typos, I'm talking about using an aggregate function when defining an order inside a partition clause, for example, which is not possible. Or using a window function AND a group by in a same query, which won't give the result he was hoping for. Or, as was done in his last query, filtering the data to only include one month and then using the lag function. Filtering with where is executed before the lag, so by the time lag is executed, the dataset has only records for december, meaning he will get nulls for every department. This is not a comprehensive list of issues, mind you, there are more.

And that's just serious issues. In addition to that most of his filtering conditions were non-sargable, and he never thought about edge cases. For example, in the second problem it is possible that there were no users who bought something from one of the departments, and in that case the inner join he used would have lost that department, it wouldn't show in the result at all. Is it a plausible situation when working with real data? No. But it is definitely possible, and it should have been at least mentioned.

Overall I am very disappointed with both the interviewee and the interviewer, who missed all of the mentioned mistakes.

[Alpha]

Thanks a million for this work, highly appreciated as i'll be applying for data positions at the end of the year. Quick observation; The cte in the third query was not used, cte = orders_per_year . And the rank was not also used either

[HoaTa-en]

For the second to last question, can we do ORDER BY o.order_amount DESC, LIMIT 1, OFF SET 1, so that we don't have to use rank() ??

[AshishPatil-uu]

w7.w3 wrong to group by customer and year, .. it should be only group by customer.

[nilsaha]

I did not understand the code of the last question. He had to calculate the increase or decrease in month-over-month growth for the year 2022. What was the basis for creating the CTE for November and December (hardcoded)? In the second CTE, he filtered out only the December amount. Does it calculate and compare all the data back to January?

[MrKhulaid]

You also used orders_ranked=1 in your main query but your cte is orders_ranking. I don't think that'll work

[ДаниилПетровский-цб]

hello! i would like get to know such information like: this interview is for what level, for instance, junior or middle, maybe senior?

[MrTeslaX]

I feel like interviewer's focus on the parts that aren't really important i.e 18:20. Focus should be on testing the logic and the ability to get to the solution.

[anshikarastogi]

How would the Lag function pick up month 11 records when there is a where filter on month = 12?

[asceticadarsh]

27:51 Mistake
CTE order_rankings have mention but in query orders_ranked

[joaofernando]

He could have used the extract() to get the year.

[sem]

thanks for the video, it's really useful

[TalhaFurqan-ln]

Is this mock interview for an entry-level data analyst position or a senior level?

[ajaycb]

The queries for sure have a lot of errors. Cant group by in rank