McDonald's employees write SQL queries to analyze customer behavior patterns, helping them understand what menu items are popular at different times of the day and how promotions impact sales. It also allows them to optimize supply chain management by analyzing historical sales data to ensure they have the right ingredients in stock when demand peaks, this is why McDonald's asks SQL questions during interviews for Data Analyst, Data Science, and BI jobs.
Thus, to help you study, we've curated 11 McDonald's SQL interview questions – able to answer them all?
You are given a table named in which each row represents one order details at a McDonald's outlet. The columns include the , , (the date the item was sold), and (price of the ordered item).
Write a SQL query to find the monthly total sales of each item and rank them within each month based on the total sales. You should use window functions in your query.
order_id | item_id | sales_date | price |
---|---|---|---|
101 | 1 | 2022-06-03 | 5.99 |
102 | 2 | 2022-06-03 | 4.99 |
103 | 1 | 2022-06-04 | 5.99 |
104 | 3 | 2022-06-04 | 3.99 |
105 | 2 | 2022-07-01 | 4.99 |
106 | 3 | 2022-07-02 | 3.99 |
107 | 1 | 2022-07-02 | 5.99 |
mth | item_id | total_sales | rank |
---|---|---|---|
6 | 1 | 11.98 | 1 |
6 | 2 | 4.99 | 2 |
6 | 3 | 3.99 | 3 |
7 | 1 | 5.99 | 1 |
7 | 2 | 4.99 | 2 |
7 | 3 | 3.99 | 3 |
In this query, is applied to divide the sales table into sub-tables for each item's monthly sales.
is then applied to calculate the total sales for each item each month, and function is used to assign a ranking to each item within each month based on their total sales.
To practice another window function question on DataLemur's free online SQL code editor, solve this Amazon SQL question asked in a BI Engineer interview:
Check out McDonald's press releases to learn about their ongoing efforts to innovate and adapt in the fast-food industry! This information is relevant for understanding how McDonald's is evolving to meet changing consumer preferences and expectations.
Imagine there was a table of McDonald's employee salary data. Write a SQL query to find the employees who earn more than their direct manager.
employee_id | name | salary | department_id | manager_id |
---|---|---|---|---|
1 | Emma Thompson | 3800 | 1 | |
2 | Daniel Rodriguez | 2230 | 1 | 10 |
3 | Olivia Smith | 8000 | 1 | 8 |
4 | Noah Johnson | 6800 | 2 | 8 |
5 | Sophia Martinez | 1750 | 1 | 10 |
8 | William Davis | 7000 | 2 | NULL |
10 | James Anderson | 4000 | 1 | NULL |
employee_id | employee_name |
---|---|
3 | Olivia Smith |
This is the output because Olivia Smith earns $8,000, surpassing her manager, William Davis who earns 7,800.
Check your SQL query for this question and run your code right in DataLemur's online SQL environment:
First, we perform a SELF-JOIN where we treat the first table () as the managers' table and the second table () as the employees' table. Then we use a clause to filter the results, ensuring we only get employees whose salaries are higher than their manager's salary.
If the solution above is hard to understand, you can find a step-by-step solution here: Employee Salaries Higher Than Their Manager.
Normalization is the process of organizing fields and tables of a database to minimize redundancy and dependency. While there are technically 5 levels (normal forms), the 3 most important normal forms you need to know about for SQL interviews at McDonald's are:
The McDonald's Corporation wants to analyze their sales data to gain insights that could potentially lead to strategic business decisions. They want to know which burger type sold the most in each city along with the total revenue earned from its sales. They also want to know which city had the highest sales.
Assume that the McDonald's Corporation has its business data spread across three separate tables: , and . The table contains details about the various burger types that the corporation offers. The table contains details about the sales made at each branch. The table contains details about the various branches of the corporation.
burger_id | burger_type | price |
---|---|---|
1 | Big Mac | 3.99 |
2 | Quarter Pounder | 3.79 |
3 | Cheeseburger | 1.69 |
sale_id | burger_id | branch_id | quantity |
---|---|---|---|
1 | 1 | 1 | 100 |
2 | 1 | 2 | 150 |
3 | 2 | 1 | 80 |
branch_id | city |
---|---|
1 | New York |
2 | Chicago |
This query joins the , and tables on their common fields ( and ). It then groups the result by and . Finally, it calculates the total revenue for each in each and orders the result in descending order of . This way, for every city, we get the burger type that has the most sales along with the total revenue generated.
To get the city with highest sales, we could simply group by city and sum the total revenues, ordering the result in descending order of the total revenues.
A cross-join, also known as a cartesian join, is a type of JOIN operation in SQL that creates a new table by combining each row from the first table with every row from the second table. This results in a table with a row for every possible combination of rows from the two input tables.
An example of when this might be useful is if you wanted to first make a dataset containing all possible pairs of customers and products data, in order to later build a Machine Learning model to predict the probability of a customer purchasing a particular product.
However, it is important to note that cross-joins can create very large tables, especially if the input tables have a large number of rows. For example, if you had 10,000 customers and 5,000 different product SKUs, the resulting cross-join would have 50 million rows.
As an analyst in McDonald's, we want to tailor our menu and offers to cater to our loyal customers. To do so, we need to filter customers who ordered meals at least twice in the last month and prefer 'non-vegetarian' meals.
The and tables have the following schema:
order_id | customer_id | meal_id | order_date |
---|---|---|---|
1001 | 201 | 301 | 2022-02-10 |
1002 | 202 | 302 | 2022-02-15 |
1003 | 201 | 301 | 2022-02-18 |
1004 | 203 | 302 | 2022-03-01 |
1005 | 204 | 303 | 2022-03-02 |
1006 | 201 | 303 | 2022-03-05 |
1007 | 204 | 303 | 2022-03-10 |
customer_id | preference |
---|---|
201 | Non-vegetarian |
202 | Vegetarian |
203 | Non-vegetarian |
204 | Non-vegetarian |
Note: In the table, each row represents an order placed by a customer. In the table, the column indicates whether the customer prefers vegetarian or non-vegetarian meals.
With this query, we first join the and tables using the column. We then filter the records to only include orders placed within February 2022 and those who prefer non-vegetarian meals. Finally, we group by and and restrict the results to only include customers who made 2 or more orders within the specified period, as per the requirements.
The keyword added to a statement can be used to get records without duplicates.
For example, say you had a table of McDonald's customers:
name | city |
---|---|
Akash | SF |
Brittany | NYC |
Carlos | NYC |
Diego | Seattle |
Eva | SF |
Faye | Seattle |
Suppose you wanted to figure out which cities the customers lived in, but didn't want duplicate results, you could write a query like this:
Your result would be:
city |
---|
SF |
NYC |
Seattle |
As a data analyst for McDonald's, we want to understand which are our most favourite items. Write a SQL query to find out the average daily sales for each item on the McDonald's menu.
date | item_id | quantity |
---|---|---|
2022-08-01 | 1 | 300 |
2022-08-01 | 2 | 150 |
2022-08-02 | 1 | 400 |
2022-08-02 | 2 | 200 |
2022-08-02 | 3 | 100 |
2022-08-03 | 1 | 350 |
2022-08-03 | 3 | 120 |
item_id | item_name |
---|---|
1 | Big Mac |
2 | Quarter Pounder |
3 | Chicken McNuggets |
item_name | avg_daily_sales |
---|---|
Big Mac | 350 |
Quarter Pounder | 175 |
Chicken McNuggets | 110 |
This query joins the and tables on the column. Then, it groups data by the and calculates the average quantity (which represents daily sales) for each group (menu item). The result is the average daily sales for each item on McDonald's menu.
To practice a very similar question try this interactive Amazon Highest-Grossing Items Question which is similar for identifying most popular products or this Wayfair Y-on-Y Growth Rate Question which is similar for analyzing daily sales data.
McDonald's is executing digital promotional campaigns for their new burger. These campaigns are showing ads to customers through various platforms. Each ad comes with a click-through link. When the customer clicks on this link, they are directed to McDonald's app where they have an option to add the new burger to the cart.
Write a SQL query that calculates the Click-Through-Rate (CTR) which is defined as the number of users who clicked the ad link (clicks) divided by the number of users the ad was shown to (impressions). Also, calculate the Conversion Rate (CR) defined as the number of users who added the burger to the cart (conversions) divided by the total number of ad-clicks.
We have two tables:
ad_id | impressions |
---|---|
1 | 5000 |
2 | 4500 |
3 | 6000 |
click_id | user_id | ad_id |
---|---|---|
1 | 123 | 1 |
2 | 265 | 1 |
3 | 362 | 2 |
4 | 192 | 3 |
5 | 981 | 3 |
cart_id | user_id |
---|---|
1 | 123 |
2 | 362 |
3 | 192 |
The output should be:
ad_id | CTR | CR |
---|---|---|
1 | 0.0004 | 0.5 |
2 | 0.00022 | 1 |
3 | 0.00033 | 0.5 |
The SQL query for this problem would be:
This query first joins the , , and tables using LEFT JOINs. Then, for each , it calculates the CTR as the total number of clicks divided by the number of impressions, and the CR as the total number of carts divided by the number of clicks. The is used to cast the integer counts to floating point values, ensuring a decimal result.
To solve a similar SQL problem on DataLemur's free interactive SQL code editor, solve this Meta SQL interview question:
For a tangible example of in PostgreSQL, suppose you were doing an HR Analytics project for McDonald's, and had access to McDonald's's contractors and employees data. Assume that some employees were previously contractors, and vice versa, and thus would show up in both tables.
You could use operator to find all contractors who never were a employee using this query:
Note that is available in PostgreSQL and SQL Server, while is the equivalent operator which is available in MySQL and Oracle (but don't worry about knowing which RDBMS supports which exact commands since McDonald's interviewers aren't trying to trip you up on memorizing SQL syntax).
As a data analyst at McDonald's, your role is to study order patterns to measure the popularity of different menu items. Write a SQL query that finds all orders which include a menu item that contains the word 'Burger'.
Below are two tables, and .
order_id | customer_id | order_date | menu_id |
---|---|---|---|
101 | 123 | 06/08/2022 | 1 |
102 | 265 | 06/10/2022 | 2 |
103 | 362 | 06/18/2022 | 3 |
104 | 192 | 07/26/2022 | 4 |
105 | 981 | 07/05/2022 | 5 |
menu_id | item_name |
---|---|
1 | Big Mac Burger |
2 | Chicken McNuggets |
3 | Fish-O-Fillet |
4 | Quarter Pounder Burger |
5 | McFlurry |
Your output should include the , , and .
This query joins the and tables on , then selects relevant columns. It only includes rows where the in the table contains 'Burger'.
The best way to prepare for a McDonald's SQL interview is to practice, practice, practice. Besides solving the earlier McDonald's SQL interview questions, you should also solve the 200+ SQL Interview Questions on DataLemur which come from companies like Microsoft, Google, Amazon, and tech startups.
Each exercise has multiple hints, fully explained answers along with a discussion board to see how others solved it and most importantly, there's an online SQL code editor so you can easily right in the browser your query and have it graded.
To prep for the McDonald's SQL interview it is also helpful to practice interview questions from other hospitality and restaurant companies like:
However, if your SQL coding skills are weak, forget about diving straight into solving questions – strengthen your SQL foundations with this DataLemur SQL tutorial.
This tutorial covers SQL concepts such as CASE/WHEN/ELSE statements and different types of joins – both of which show up often in SQL interviews at McDonald's.
In addition to SQL interview questions, the other types of questions tested in the McDonald's Data Science Interview are:
I believe the best way to study for McDonald's Data Science interviews is to read the book Ace the Data Science Interview.
It has 201 data interview questions taken from FAANG & startups. The book's also got a crash course on SQL, Product-Sense & ML. And finally it's helped thousands of people land their dream job in data, which is how it's gotten to be an Amazon best-seller with over 1000+ 5-star reviews.
While the book is more technical in nature, it's also crucial to prepare for the McDonald's behavioral interview. Start by reading the company's unique cultural values.