9 Kearney SQL Interview Questions (Updated 2024)

Kearney employees write SQL queries to extract and process different types of data from client databases, including customer transactions and market research information. They use this data to create predictive models for strategic consulting projects, helping to identify patterns and forecast future trends, which is why Kearney includes SQL questions in interviews for Data Science, Data Engineering, and Data Analytics roles.

Thus, to help prep you for the Kearney SQL interview, here’s 9 A.T. Kearney SQL interview questions – scroll down to start solving them!

9 A.T. Kearney SQL Interview Questions

SQL Question 1: Identify the top purchasing customers for Kearney

Kearney is an e-commerce business which sells a variety of products. The business values customers who frequently purchase products, so the management wants to identify the top 10 customers who have made the most purchases in the past month. Write a SQL query to identify these customers. You are given two tables - Customers and Purchases.

The table stores information about the customers such as their customer_id and name. The table stores information about each purchase made, such as the customer_id of the customer who made the purchase, the purchase_id, and purchase_date. Each row in the Purchase table indicates a unique purchase made by a customer.

Example Input:

Example Input:

Answer:

This query first joins the table with the table on the column. It then filters for purchases made in the month of August 2022 using the WHERE clause.

The GROUP BY clause groups the results by customer name, and for each group, the number of purchases is counted. The results are then ordered in descending order of the number of purchases to get the customers with the most purchases at the top. Finally, the LIMIT clause restricts the output to the top 10 customers only.

To work on a similar customer analytics SQL question where you can code right in the browser and have your SQL solution instantly executed, try this Walmart SQL Interview Question:

SQL Question 2: Employee Salaries Higher Than Their Manager

Given a table of Kearney employee salaries, write a SQL query to find employees who make more than their own boss.

Kearney Example Input:

Example Output:

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

Try this interview question interactively on DataLemur:

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 detailed solution here: Employees Earning More Than Managers.

SQL Question 3: What is the difference between cross join and natural join?

Cross joins and natural joins are two types of JOIN operations in SQL that are used to combine data from multiple tables. A cross join creates a new table by combining each row from the first table with every row from the second table, and is also known as a cartesian join. On the other hand, a natural join combines rows from two or more tables based on their common columns, forming a new table. One key difference between these types of JOINs is that cross joins do not require common columns between the tables being joined, while natural joins do.

Here's an example of a cross join:

If you have 20 products and 10 colors, that's 200 rows right there!

Here's a natural join example using two tables, Kearney employees and Kearney managers:

This natural join returns all rows from Kearney employees where there is no matching row in managers based on the column.

SQL Question 4: Analysis of Client Revenue Using Window Functions

Assume you are consulting for Kearney and they have given you a dataset of all their client transactions. Your task is to write a SQL query that will analyze the rolling 12-month revenue with each client.

Specifically, for each month, you should calculate the sum of the revenue from that month and the previous 11 months for every client and identify any clients who have generated over $1 million in revenue over the past 12 months.

Here's the client transactions dataset we'll be using:

Example Input:

Note: The dates are in dd/mm/yyyy format and the amount is in US dollars.

Example Output:

Answer:

Below is a PostgreSQL query to solve the problem:

This query will first create a CTE () that calculates the rolling 12-month revenue for each client. In this CTE, we use a window function to calculate the sum of the revenue over a window of 12 months (from the current row and the 11 preceding rows), for each client.

Then, from the resulting table, we select the rows where the 12-month revenue is greater than $1 million.

For more window function practice, solve this Uber SQL Interview Question within DataLemur's interactive SQL code editor:

SQL Question 5: What's the purpose of a foreign key?

A foreign key is a field in a database table that serves as a reference to the primary key of another table, allowing for the creation of a relationship between the two tables.

For a concrete example, let's inspect employee data from Kearney's HR database:

:

In this table, is the primary key, and is used to uniquely identify each row.

could be a foreign key. It references the of the manager of each employee in the table, establishing a relationship between the employees and their managers. This foreign key allows you to easily query the table to find out who a specific employee's manager is, or to find out which employees report to a specific manager.

It is possible for a table to have multiple foreign keys that reference primary keys in different tables. For instance, the table could have additional foreign keys for the of the department where an employee works and the of the employee's location.

SQL Question 6: Estimate Profit Per Client

Kearney is a global consulting firm that needs to track the profitability of their engagements with different clients. They maintain a time tracking system where consultants log the hours they spend on various projects. Each project is linked with the respective client.

The company takes every hour worked by a consultant and multiplies it by that consultant's hourly billable rate to calculate the cost of each project. Here is a database table design that reflects this scenario:

Table:

Table:

The question is: Write a SQL query to find the total profit generated from every client. The profit here is calculated as the total billable amount per client subtracted from the total cost.

Answer:

This query first joins the table and the table on the field. It then groups by from the table. For each group, it calculates the sum of the product of hours and billable rate (which gives the total revenue per client).

Please be aware that this solution assumes that the is already stored as a numerical value in the database, despite the dollar sign indicating it's a string. If it's stored as a string, it must be converted to a numerical type within the SQL query. The actual conversion method depends on the specific SQL dialect used. In PostgreSQL, for example, this can be done using some built-in functions such as or .

SQL Question 7: Can you explain the distinction between a left and right join?

A join in SQL allows you to retrieve data from multiple tables and combine it into a single set of results.

To demonstrate the difference between left vs. right join, imagine you had two database tables, an advertising campaigns table which had information on Google Ads keywords and how much was bid for each keyword, and a sales table, which has data on how many products were sold and which Google Ads keyword drove that sale.

: A LEFT JOIN retrieves all rows from the left table (in this case, the table) and any matching rows from the right table (the Sales table). If there is no match in the right table, values will be returned for the right table's columns.

: A RIGHT JOIN retrieves all rows from the right table (in this case, the Sales table) and any matching rows from the left table (the table). If there is no match in the left table, values will be returned for the left table's columns.

SQL Question 8: Calculate the Average Duration of Projects

At Kearney, a global consulting company, it's crucial to track the efficiency and timeframe of handling various consulting projects. Suppose you are given a table with the project details including the project start and end dates. Can you write a SQL query to calculate the average duration of all projects?

Example Input:

|project_id|client_id|consultant_id|start_date|end_date|

|:----|:----|:----|:----|:----| |1|12|51|01/01/2022 00:00:00|31/01/2022 00:00:00| |2|15|42|02/01/2022 00:00:00|03/02/2022 00:00:00| |3|18|37|04/01/2022 00:00:00|05/02/2022 00:00:00| |4|11|52|06/01/2022 00:00:00|07/02/2022 00:00:00| |5|16|44|08/01/2022 00:00:00|09/02/2022 00:00:00|

Example Output:

Answer:

The EXTRACT function in PostgreSQL is used widely for extracting parts of a date or time. 'epoch' keyword is used to retrieve the number of seconds since '1970-01-01 00:00:00 UTC' which is further divided by 86400 (the number of seconds in a day) to convert the duration to days which is then averaged over all the projects. The AVG function calculates the average of the output. Note that the duration doesn't consider the exact time of day a project started/ended.

To practice a very similar question try this interactive Facebook Average Post Hiatus (Part 1) Question which is similar for calculating time between two dates or this Alibaba Compressed Mean Question which is similar for calculating average of a numerical column.

SQL Question 9: Calculating Click-Through-Rate for Digital Ads

Question:

Kearney is a global consulting firm that runs various digital campaigns to attract potential customers. They use 2 main types of digital ads - email and search ads. You have been given two tables and . The table has a record of all the clicks made on their ads while the table has a record of all times their ads were served (whether or not they were clicked). Write a SQL query to calculate the click-through-rate (CTR) for each type of ad on a monthly basis. The click-through-rate is calculated as the total number of clicks divided by the total number of impressions, expressed as a percentage.

Example Input:

Example Input:

Answer:

In this SQL query, we first join the table with the table. Here, we use INNER JOIN which will return the records where there is a match in both tables. We group the results by ad_type and month because we want to get the results for each ad type on a monthly basis. Finally, we calculate the CTR using the formula provided in the question.

To solve a similar problem about calculating rates, try this TikTok SQL Interview Question on DataLemur's online SQL code editor:

Kearney SQL Interview Tips

The key to acing a Kearney SQL interview is to practice, practice, and then practice some more! Besides solving the earlier Kearney SQL interview questions, you should also solve the 200+ DataLemur interview questions which come from companies like Amazon, JP Morgan, and management consulting companies like Kearney.

Each problem on DataLemur has multiple hints, full answers and crucially, there is an online SQL coding environment so you can right in the browser run your query and have it checked.

To prep for the Kearney SQL interview it is also helpful to solve SQL questions from other management consulting companies like:

• McKinsey SQL questions
• BCG SQL questions
• Bain & Company SQL questions
• Oliver Wyman SQL questions

Find out how Kearney's Digital Analytics and AI capabilities can drive business success!

But if your SQL foundations are weak, forget about diving straight into solving questions – strengthen your SQL foundations with this interactive SQL tutorial.

This tutorial covers things like filtering on multiple conditions using AND/OR/NOT and handling timestamps – both of these show up routinely during Kearney SQL interviews.

A.T. Kearney Data Science Interview Tips

What Do Kearney Data Science Interviews Cover?

In addition to SQL query questions, the other topics tested in the Kearney Data Science Interview include:

• Statistics and Probability Questions
• Coding Questions in Python or R
• Analytics and Product-Metrics Questions
• ML Interview Questions
• Behavioral Based Interview Questions

How To Prepare for Kearney Data Science Interviews?

I think the optimal way to prepare for Kearney Data Science interviews is to read the book Ace the Data Science Interview.

It has 201 interview questions sourced from FAANG tech companies. The book's also got a refresher on Stats, ML, & Data Case Studies. And finally it's helped thousands of people land their dream job in data, which is why it's got over 1000+ 5-star reviews on Amazon.

While the book is more technical in nature, it's also important to prepare for the Kearney behavioral interview. Start by reading the company's cultural values.