9 Deloitte SQL Interview Questions (Updated 2024)

Updated on

August 11, 2024

Data Analysts & Data Scientists at Deloitte use SQL to analyze financial datasets, such as income statements and balance sheets, to identify trends and forecast revenue growth. It is also used to manage client databases to track customer interactions and preferences for strategic consulting projects, which is why Deloitte includes SQL interview questions in its hiring process to assess candidates' skills.

So, to help you study for the Deloitte SQL interview, here's 9 Deloitte SQL interview questions in this article.

Deloitte SQL Interview Questions

9 Deloitte SQL Interview Questions

SQL Question 1: Identify VIP Users For Deloitte

Deloitte makes use of a log system to track the completion of various critical tasks by its employees. This system logs which task was completed, the completion time, and the employee who completed the task. Deloitte needs to identify 'VIP' users for its system - these are employees who complete more than 20 critical tasks per week consistently over the period of a month.

Create a SQL query that identifies the VIP users for each month using an table. Mark employees who complete the above-mentioned frequency as 1, else 0.

The table has the following columns:

  • unique identifier for each log
  • the id of the user who performed the activity
  • the type of activity performed ('critical' or 'non-critical')
  • the time when the activity was performed
Example Input:
log_iduser_idactivity_typetimestamp
120132critical06/02/2022 09:00:00
130245non-critical06/03/2022 10:30:00
140332critical06/04/2022 14:44:00
150467critical06/05/2022 15:22:00
160532critical06/06/2022 10:54:00
170632non-critical06/07/2022 11:21:00

Your output table should be:

MonthYearuserIDisVIP

Answer:


This query works by grouping data by user_id, month, and year. For each group, it checks if the count of 'critical' activities is more than 80 (which is 20 times 4 weeks). If it's more than 80, it marks the user as a VIP (1), otherwise, it marks them as not a VIP (0).

To solve a super-customer analysis question on DataLemur's free online SQL code editor, try this Microsoft SQL Interview problem:

Microsoft SQL Interview Question: Super Cloud Customer

SQL Question 2: Well Paid Employees

Suppose there was a table of Deloitte employee salary data. Write a SQL query to find the employees who earn more than their direct manager.

Deloitte Example Input:

employee_idnamesalarydepartment_idmanager_id
1Emma Thompson38001
2Daniel Rodriguez2230110
3Olivia Smith800018
4Noah Johnson680028
5Sophia Martinez1750110
8William Davis70002NULL
10James Anderson40001NULL

Example Output:

employee_idemployee_name
3Olivia Smith

This is the output because Olivia Smith earns $8,000, surpassing her manager, William Davis who earns 7,800.

Write a SQL query for this question directly within the browser on DataLemur:

Employees Earning More Than Their Manager

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 code above is hard to understand, you can find a step-by-step solution with hints here: Employees Earning More Than Managers.

SQL Question 3: What is database denormalization, and when is it a good idea to consider it?

Denormalization is the practice of altering a database schema in a way that breaks the normalization rules (1st, 2nd, 3rd normal forms).

Here's 3 reasons to de-normalize a database at Deloitte:

  1. Improved performance: Denormalization can often improve performance by reducing the number of expensive join operations required to retrieve data. This is particularly useful when the database is being used for online analytical processing (OLAP) as frequent joins across multiple tables can be slow and costly.

  2. Scalability: Denormalization can increase the scalability of a database by requiring less data to be read and processed when executing a query. This can be beneficial when the database is expected to handle a high volume of queries.

  3. Simplification: One way to simplify the design of a database is by using denormalization to reduce the number of tables and relationships that need to be managed. This can make the database easier to understand and maintain.

Deloitte SQL Interview Questions

SQL Question 4: Finding Average Monthly Product Ratings

Given a database of product reviews, can you write a SQL query in PostgreSQL to calculate the average monthly stars (ratings) given to products? For example, if product "50001" received ratings of "4" and "3" in June, its average rating for that month would be "3.5". You should structure the output in a way that allows a user to easily see the average product ratings for each month.

Example Input:
review_iduser_idsubmit_dateproduct_idstars
617112306/08/2022 00:00:00500014
780226506/10/2022 00:00:00698524
529336206/18/2022 00:00:00500013
635219207/26/2022 00:00:00698523
451798107/05/2022 00:00:00698522
Example Output:
monthproduct_idaverage_stars
6500013.50
6698524.00
7698522.50

Answer:

Taking into account timezones could affect the calculation, in this example we are considering PostgreSQL's function to truncate the date at the month level without considering timezones.


This query groups the product reviews by month and product_id, then calculates the average stars (ratings) for each group. The results are then ordered first by month and then product_id for ease of reading.

To practice another window function question on DataLemur's free interactive coding environment, solve this Amazon BI Engineer interview question:

Amazon SQL Interview Question

SQL Question 5: What's the SQL command do, and can you give an example?

When using , only rows that are identical in both sets will be returned.

For a concrete example, say you were a Data Analyst supporting the Sales Analytics team at Deloitte, and data on potential sales leads lived in both Salesforce and Hubspot CRMs. To write a query to analyze leads created before 2023 started, that show up in BOTH CRMs, you would use the command:


SQL Question 6: Filter Customers Based on Order Buying and Location Data

As an analyst at Deloitte, you are given a database that consists of customer records and the recorded transactions of these customers. Your task is to write a SQL query that allows you to filter the customers who are based in New York and who have made at least one purchase over $1000 in the category of "Electronics" since 2020.

This information is housed in two tables, and . The structure of these tables is as follows:

Example Input:
customer_idfirst_namelast_namecitystate
1JohnDoeNew YorkNY
2JaneSmithLos AngelesCA
3AliceJohnsonNew YorkNY
4BobWilliamsChicagoIL
5CharlieBrownNew YorkNY
Example Input:
order_idcustomer_idorder_datecategoryorder_value
1101/05/2020Electronics1500
2202/10/2021Furniture800
3103/15/2022Electronics700
4304/20/2020Electronics2000
5505/25/2022Furniture1500

Answer:


This query will return the customer_id, first_name and last_name from the data joined with data where the customers' state is New York, the orders' category is Electronics, the order value is more than $1000 and the order date is after or on 1st January, 2020. Finally, it filters down the customers who have had at least one such order (as indicated by ).

SQL Question 7: What's the difference between and ?

The clause is used to filter the groups created by the clause. It's similar to the clause, but it is used to specify conditions on the groups created by the clause, rather than on the individual rows of the table.

For example, say you were analyzing salaries for analytics employees at Deloitte:


This query retrieves the total salary for each Analytics department at Deloitte and groups the rows by the specific department (i.e. "Marketing Analytics", "Business Analytics", "Sales Analytics" teams).

The clause then filters the groups to include only Deloitte departments where the total salary is greater than $1 million.

SQL Question 8: Calculate the Click-through Conversion Rate

Given a mock dataset, calculate the click-through conversion rate for advertisements run by Deloitte. For each advertisement, calculate the percentage of times a viewer clicked on the advertisement and then went on to add a product to their cart.

Assume we are given two tables, and . The table contains information about the advertisements and their IDs. The table tracks every action taken by users -- from ad clicks to product add to cart.

Example Input:
ad_idcampaign_idproduct_id
1A100
2A101
3B102
4B103
Example Input:
user_action_iduser_idactionad_idproduct_idaction_date
1123"Clicked ad"1100"01/01/2022 00:00:00"
2123"Added product to cart"null100"01/01/2022 00:05:00"
3456"Clicked ad"2101"01/02/2022 00:00:00"
4456"Added product to cart"null101"01/03/2022 00:00:00"
5789"Clicked ad"3102"01/04/2022 00:00:00"

Answer:


Above PostgreSQL query first separates out the click ad actions and add to cart actions and counts them according to ad_id and product_id respectively using Common Table Expressions (CTE). Then, a JOIN operation is performed on click_actions, add_to_cart_actions, and ads on common ids and final conversion rate is calculated using the formula (total_add_to_cart / total_clicks) * 100. The result will be the ad_id, campaign_id and the calculated conversion rate for each ad.

To practice a similar problem on DataLemur's free interactive SQL code editor, try this Facebook SQL Interview question:

SQL interview question asked by Facebook

SQL Question 9: Calculate Weighted Average Rating by Product

Deloitte is analyzing the performance of various products based on customer ratings. Each product has multiple sizes, each with its own rating. The larger the size, the heavier the impact on the final rating. We need to calculate the weighted average rating for each product.

Here are the tables for product ratings and product sizes:

Example Input:
product_idsize_idrating
114
125
133
215
222
234
Example Input:
size_idweight_factor
10.1
20.3
30.6
Example Output:
product_idweighted_avg_rating
13.7
23.6

Answer:


In this query, we first join the table and table on . Then, for each product (), we calculate the weighted sum of ratings where the weights are the in the table. We then divide that sum by the total of the weights () to yield the weighted average. The function is used to round the average to one decimal point. Finally, the clause ensures this calculation is done separately for each product.

To practice a very similar question try this interactive Amazon Average Review Ratings Question which is similar for its focus on evaluating product ratings or this Amazon Highest-Grossing Items Question which is similar for its process of analyzing product data.

How To Prepare for the Deloitte SQL Interview

Assuming that you've already got basic SQL skills, the next best tip we have to prepare for the Deloitte SQL interview is to solve as many practice SQL interview questions as you can! In addition to solving the earlier Deloitte SQL interview questions, you should also solve the 200+ tricky sql questions on DataLemur which come from companies like Google, Uber, and Microsoft.

DataLemur Question Bank

Each SQL question has hints to guide you, 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 right in the browser run your SQL query and have it graded.

To prep for the Deloitte SQL interview it is also a great idea to solve interview questions from other consulting and professional service companies like:

Unlock the power of Artificial Intelligence and data with Deloitte's expert consulting services!

In case your SQL foundations are weak, don't worry about jumping right into solving questions – strengthen your SQL foundations with this SQL tutorial for Data Scientists & Analysts.

DataLemur SQL Tutorial for Data Science

This tutorial covers SQL topics like grouping by multiple columns and using wildcards with LIKE – both of these pop up routinely in Deloitte SQL interviews.

Deloitte Data Science Interview Tips

What Do Deloitte Data Science Interviews Cover?

In addition to SQL interview questions, the other topics to practice for the Deloitte Data Science Interview include:

Deloitte Data Scientist

How To Prepare for Deloitte Data Science Interviews?

To prepare for Deloitte Data Science interviews read the book Ace the Data Science Interview because it's got:

  • 201 interview questions taken from tech companies like Netflix, Google, & Airbnb
  • a crash course on Python, SQL & ML
  • over 1000+ 5-star reviews on Amazon

Ace the Data Science Interview by Nick Singh Kevin Huo

Don't ignore the behavioral interview – prep for it using this list of behavioral interview questions for Data Scientists.

© 2024 DataLemur, Inc

Career Resources

Free 9-Day Data Interview Crash CourseFree SQL Tutorial for Data AnalyticsUltimate SQL Interview GuideAce the Data Job Hunt Video CourseAce the Data Science InterviewBest Books for Data Analysts