11 McDonald's SQL Interview Questions (Updated 2024)
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?
11 McDonald's SQL Interview Questions
SQL Question 1: Analyze Sales Performance of Different Menu Items.
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.
Example Input:
| 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 |
Example Output:
| 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 |
Answer:
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.
SQL Question 2: Employee Salaries Higher Than Their Manager
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.
McDonald's Example Input:
| 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 |
Example Output:
| 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:
Answer:
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.
SQL Question 3: What are the various forms of normalization?
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:
- First Normal Form (1NF): This should fix remove a table's duplicate columns. Also, each column should contain only a single value (no lists or containers of data), and finally each row of table should have a unique identifier as well.
- Second Normal Form (2NF): A table is in its second normal form if it meets all requirements of the first normal form and places the subsets of columns in separate tables. The relationships between tables are created using primary/foreign keys.
- Third Normal Form (3NF): The table should be in the second normal form. There should be no dependency on another non-key attribute (meaning a primary key should be the only thing required to identify the row).
SQL Question 4: Analyze the Sales of McDonald's
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.
Example Input:
| burger_id | burger_type | price |
|---|---|---|
| 1 | Big Mac | 3.99 |
| 2 | Quarter Pounder | 3.79 |
| 3 | Cheeseburger | 1.69 |
Example Input:
| sale_id | burger_id | branch_id | quantity |
|---|---|---|---|
| 1 | 1 | 1 | 100 |
| 2 | 1 | 2 | 150 |
| 3 | 2 | 1 | 80 |
Example Input:
| branch_id | city |
|---|---|
| 1 | New York |
| 2 | Chicago |
Answer:
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.
SQL Question 5: Can you explain the concept of a cross-join, and their purpose?
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.
SQL Question 6: Filtering McDonald's Customers by Preferences and Histories
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:
Example Input:
| 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 |
Example Input:
| 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.
Answer:
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.
SQL Question 7: How can you select records without duplicates from a table?
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 |
SQL Question 8: Average sales per McDonald's menu item
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.
Example Input:
| 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 |
Example Input:
| item_id | item_name |
|---|---|
| 1 | Big Mac |
| 2 | Quarter Pounder |
| 3 | Chicken McNuggets |
Example Output:
| item_name | avg_daily_sales |
|---|---|
| Big Mac | 350 |
| Quarter Pounder | 175 |
| Chicken McNuggets | 110 |
Answer:
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.
SQL Question 9: Calculate the Click-Through-Rate for McDonald's Digital Ads
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:
Example Input:
| ad_id | impressions |
|---|---|
| 1 | 5000 |
| 2 | 4500 |
| 3 | 6000 |
Example Input:
| click_id | user_id | ad_id |
|---|---|---|
| 1 | 123 | 1 |
| 2 | 265 | 1 |
| 3 | 362 | 2 |
| 4 | 192 | 3 |
| 5 | 981 | 3 |
Example Input:
| cart_id | user_id |
|---|---|
| 1 | 123 |
| 2 | 362 |
| 3 | 192 |
The output should be:
Example Output:
| ad_id | CTR | CR |
|---|---|---|
| 1 | 0.0004 | 0.5 |
| 2 | 0.00022 | 1 |
| 3 | 0.00033 | 0.5 |
Answer:
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:
SQL Question 10: What do the SQL commands / do?
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).
SQL Question 11: Finding Orders Containing Specific Menu Items
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 .
Example Input:
| 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 |
Example Input:
| 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 .
Answer:
This query joins the and tables on , then selects relevant columns. It only includes rows where the in the table contains 'Burger'.
McDonald's SQL Interview Tips
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:
- Starbucks SQL questions
- Bloomin' Brands SQL questions
- Domino's SQL questions
- Las Vegas Sands SQL questions
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.
McDonald's Data Science Interview Tips
What Do McDonald's Data Science Interviews Cover?
In addition to SQL interview questions, the other types of questions tested in the McDonald's Data Science Interview are:
- Probability & Stats Questions
- Coding Questions in Python or R
- Product-Sense Questions
- ML Modelling Questions
- Behavioral Based Interview Questions
How To Prepare for McDonald's Data Science Interviews?
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.