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

Don't leave your SQL interviews to chance. Sign up for Exponent's SQL interview course today: bit.ly/3TYla1W
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?
- Analyze a User's Posts: • Analyze a User's Posts...
- Find employees who earn more than their managers: • SQL Mock Interview: Fi...
- Analyze Marketing Channels: • Data Science SQL Mock ...
- SQL vs. NoSQL Explained (in 4 Minutes): • SQL vs. NoSQL Explaine...
👉 Subscribe to our channel: bit.ly/exponentyt
🕊️ Follow us on Twitter: bit.ly/exptweet
💙 Like us on Facebook for special discounts: bit.ly/exponentfb
📷 Check us out on Instagram: bit.ly/exponentig
📹 Watch us on TikTok: bit.ly/exponenttikttok
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

[@tryexponent]

Don't leave your SQL interviews to chance. Sign up for Exponent's SQL interview course today: bit.ly/3TYla1W

[@kaboyodaniel3630]

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

[@sem1246]

thanks for the video, it's really useful

[@Alpha408]

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

[@prateeksenapati2152]

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()?

[@funtik_777]

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

[@pakalupapito3202]

Landed my data analyst job thanks

[@tryexponent]

Congrats!! 🎉

[@angkhoi6111]

thanks for all

[@mohammadnasser9951]

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

[@user-jq3iy7td9w]

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

[@nilsaha8021]

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?

[@dickyalamsyah790]

You can’t group by within rank function actually

[@aliaksandrmatrunich2056]

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

[@user-qe2tc4mw8q]

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

[@asceticadarsh8284]

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

[@newenglandnomad9405]

Is this query something you'd actually be asked to write on the job?

[@tryexponent]

Hey newenglandnomad9405! The complexity of queries you'll write on the job can vary depending on the role, department, and company. In many cases, you'll have the support of senior colleagues and prior discussions to guide you. Technical interviews typically focus on standard topics and concepts relevant to the job, like SQL for data science or data structures for software engineering. These questions aim to assess the candidates' technical foundations, even if the skills might not be directly used in their day-to-day tasks. So, while you may encounter similar queries in interviews, the actual queries you write on the job may differ based on the specific role and requirements. Hope this helps!

[@ajaycb590]

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

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

[@shubhamdabhade3004]

👍

[@Clazer07]

for the last question...

[@Clazer07]

With nov_dec_sum as ( Select department_name, sum(case when year(order_date) = 2022 and month(order_date) = 11 then order_amount else 0 end as nov_sum, sum(case when year(order_date) = 2022 and month(order_date) = 12 then order_amount else 0 end as dec_sum From orders o join department d on o.orders_id = d.orders_id Group by department ) Select department_name, From nov_dec_sum Order by nov_sum - dec_sum desc Limit 1

[@suchitkakirde1733]

SELECT customer_id, COUNT(order_id) AS total_orders FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '5' YEAR GROUP BY customer_id ORDER BY total_orders DESC LIMIT 1;