10 FTI Consulting SQL Interview Questions (Updated 2024)
FTI Consulting employees write SQL queries for analyzing large structured datasets to identify patterns in financial transactions and detect potential fraud, as well as managing data migration to ensure compliance with data protection laws and industry standards for secure data handling. Because of this, FTI Consulting uses SQL problems during interviews for Data Science, Data Engineering and Data Analytics jobs.
Thus, to help you prep, here's 10 FTI Consulting SQL interview questions – able to solve them?
10 FTI Consulting SQL Interview Questions
SQL Question 1: Identify the Most Frequent Consulting Service Users
FTI Consulting provides multiple types of consulting services. In the context of FTI Consulting, a "power" user, "VIP" user, or "whale" user can be considered as a corporate client who avails a lot of consultations over a certain time period. Given the client and consultations use records, write a SQL query to find these high-value clients.
We have two tables: The clients table and the consultations table. The consultations table records every consultation service availed by a client.
Example Input:
| client_id | company_name |
|---|---|
| 1 | Company A |
| 2 | Company B |
| 3 | Company C |
| 4 | Company D |
| 5 | Company E |
Example Input:
| consultation_id | client_id | adviser_id | consultation_date | service_type |
|---|---|---|---|---|
| 100 | 1 | 300 | 01/20/2022 | Economic |
| 101 | 2 | 301 | 01/21/2022 | Forensic |
| 102 | 3 | 302 | 01/22/2022 | Corporate |
| 103 | 1 | 303 | 01/23/2022 | Economic |
| 104 | 1 | 304 | 01/24/2022 | Economic |
Answer:
Here is the PostgreSQL query:
This query joins the consultations and clients table on the client_id. It then groups the results by client_id and company_name, and counts the number of consultations availed by each client. Then, it orders these results in decreasing order of consultation_count and returns the top 5 clients with the highest count of availances of the consultation service.
The output will give a list of the top 5 clients that frequently avail consulting services with the company FTI Consulting.
To practice a similar customer analytics SQL question where you can solve it right in the browser and have your SQL query instantly executed, try this Walmart SQL Interview Question:
SQL Question 2: Well Paid Employees
Given a table of FTI Consulting employee salary information, write a SQL query to find all employees who make more than their direct manager.
FTI Consulting 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 problem 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 tough, you can find a detailed solution here: Employee Salaries Higher Than Their Manager.
SQL Question 3: What's the purpose of a primary key?
A primary key is a column or group of columns that uniquely identifies a row in a table. For example, say you had a database of FTI Consulting marketing campaigns data:
In this FTI Consulting example, the CampaignID column is the primary key of the MarketingCampaigns table. The constraint ensures that no two rows have the same CampaignID. This helps to maintain the integrity of the data in the table by preventing duplicate rows.
SQL Question 4: Analyze Employee Performance Over Time
As a data analyst at FTI Consulting, you're asked to analyze the performance of consultants over time with regard to the number of projects they've handled. The dataset at your disposal contains information about projects and the respective consultants who handled them. Information of interest includes the project ID, the consultant ID, the start date, and the end date of the project.
Your task is to write a SQL query that generates a report showing the cumulative number of projects each consultant has handled over each month.
Example Input:
| project_id | consultant_id | start_date | end_date |
|---|---|---|---|
| 101 | 1 | 2022-02-01 | 2022-02-28 |
| 102 | 1 | 2022-03-01 | 2022-03-31 |
| 103 | 2 | 2022-03-01 | 2022-03-31 |
| 104 | 3 | 2022-04-01 | 2022-04-30 |
| 105 | 1 | 2022-04-01 | 2022-04-30 |
Example Output:
| month | consultant_id | cumulative_projects |
|---|---|---|
| 2 | 1 | 1 |
| 3 | 1 | 2 |
| 3 | 2 | 1 |
| 4 | 1 | 3 |
| 4 | 2 | 1 |
| 4 | 3 | 1 |
Answer:
Here is a PostgreSQL query that can perform the analysis:
This query partitions the data by , and for each partition, it orders the data by . It then applies the COUNT() window function to count the number of rows from the start of the partition up to the current row. The result is the cumulative count of projects for each consultant over each month.
To practice a related window function SQL problem on DataLemur's free online SQL code editor, try this Google SQL Interview Question:
SQL Question 5: How is the constraint used in a database?
A is a field in a table that references the of another table. It creates a link between the two tables and ensures that the data in the field is valid.
Say for example you had sales analytics data from FTI Consulting's CRM (customer-relationship management) tool.
In this example, the table has a foreign key field called that references the "account_id" field in the table (the primary key). This helps to link the data about each opportunity to the corresponding account information in the accounts table.
This makes sure the insertion of rows in the table that do not have corresponding entries in the table. It also helps to enforce the relationship between the two tables and ensures that data is not deleted from the table if there are still references to it in the table.
SQL Question 6: Analysis of Consulting Projects based on Revenue and Duration
As a data analyst at FTI Consulting, your task is to analyze the data of consulting projects undertaken by the firm. The aim is to find out the average revenue per day for each consulting service type offered by the firm. The Projects table contains the following columns: project_id (unique identifier for each project), start_date (project commencement date), end_date (project completion date), service_type (type of consulting service), and total_revenue (total revenue generated by the project).
Example Input:
| project_id | start_date | end_date | service_type | total_revenue |
|---|---|---|---|---|
| 1001 | 01/01/2020 | 31/03/2020 | Financial | 90000 |
| 1002 | 01/02/2020 | 31/05/2020 | Economic | 120000 |
| 1003 | 01/03/2020 | 31/03/2020 | Corporate | 50000 |
| 1004 | 01/04/2020 | 30/06/2020 | Forensic & Litigation | 200000 |
| 1005 | 01/05/2020 | 31/07/2020 | Technology | 150000 |
For your analysis, you have to consider the duration of the project in days and calculate the average revenue per day for each service type. The output should have columns and .
Example Output:
| service_type | avg_revenue_per_day |
|---|---|
| Financial | 1500 |
| Economic | 1333.33 |
| Corporate | 1666.67 |
| Forensic & Litigation | 2222.22 |
| Technology | 1666.67 |
Answer:
The above query first calculates the duration of each project by subtracting the start_date from the end_date inside the function. It then calculates the total_revenue for each service type and divides it by the total duration, yielding the average revenue per day. The results are grouped by service_type. The output list shows service_type and the corresponding average revenue generated per day. Note that the result may vary depending on the specific database and its handling of date operations.
SQL Question 7: Why is normalizing a database helpful?
Normalization can reduce redundancy and improve performance and flexibility in a database.
By dividing larger tables into smaller, more specific ones linked by foreign keys, it is often possible to improve query speed. Note: it is not always the case that query speed will be improved due to costly nature of joins. Still, we think normalization is a good idea because it can also increase flexibility by making it easier to alter individual tables without affecting others, which can be useful for adapting FTI Consulting's database to ever-changing business needs.
SQL Question 8: Filter Customer Records by City
At FTI Consulting, we manage a wide range of clients across multiple cities. Imagine we have a table named that stores our customer data. The task is to use the SQL keyword to filter out all customers who are from cities starting with 'W'.
Example Input:
| customer_id | fullname | signup_date | city | |
|---|---|---|---|---|
| 4345 | Jane Doe | 01/18/2021 | jane.doe@example.com | New York |
| 5546 | John Smith | 07/10/2021 | john.smith@example.com | Washington |
| 4637 | Alice Johnson | 09/05/2021 | alice.johnson@example.com | Chicago |
| 6758 | David Williams | 02/15/2022 | david.williams@example.com | Wilmington |
| 9890 | Emily Brown | 03/16/2022 | emily.brown@example.com | Wichita |
Answer:
You can use the SQL 'LIKE' keyword, in combination with a WHERE clause, to accomplish this task. Here's an example of a PostgreSQL query to solve the problem:
This SQL command filters and returns only the records from the 'customers' table where the 'city' field starts with the letter 'W'. The '%' is a wildcard character that matches any sequence of characters.
So, in this case, the records for John Smith, David Williams and Emily Brown would be returned, as their cities (Washington, Wilmington, Wichita) start with the letter 'W'.
SQL Question 9: Analyze Transactions and Calculate Spending per Customer
In FTI Consulting, there are two key tables in the database. The first is the table that contains information about each customer, including the customer ID, first name, and last name. The second is the table, which records all transactions made by the customers, including the transaction ID, customer ID, transaction amount, and transaction date.
Task: Write a SQL query that joins the table and the table to calculate the total amount spent by each customer.
Example Input:
| customer_id | first_name | last_name |
|---|---|---|
| 103 | John | Smith |
| 245 | Maria | Johnson |
| 657 | Michael | Williams |
| 908 | Emma | Jones |
| 712 | Sophia | Brown |
Example Input:
| transaction_id | customer_id | transaction_amount | transaction_date |
|---|---|---|---|
| 800 | 103 | 200 | 2022-08-10 |
| 801 | 245 | 150 | 2022-08-11 |
| 802 | 657 | 300 | 2022-08-12 |
| 803 | 908 | 250 | 2022-08-13 |
| 804 | 712 | 350 | 2022-08-14 |
Sample Output:
| customer_id | first_name | last_name | total_spent |
|---|---|---|---|
| 103 | John | Smith | 200 |
| 245 | Maria | Johnson | 150 |
| 657 | Michael | Williams | 300 |
| 908 | Emma | Jones | 250 |
| 712 | Sophia | Brown | 350 |
Answer:
This PostgreSQL query does an inner join on the table and the table using the field that is common in both tables. The joined table is grouped by , , and from the table, and the total transaction amount is calculated for each group (i.e., for each customer) using the aggregate function. The result is then sorted in descending order of .
Since join questions come up so often during SQL interviews, practice this Spotify JOIN SQL question:
SQL Question 10: What is database denormalization?
Denormalization is a technique used to improve the read performance of a database, typically at the expense of some write performance.
By adding redundant copies of data or grouping data together in a way that does not follow normalization rules, denormalization improves the performance and scalability of a database by eliminating costly join operations, which is important for OLAP use cases that are read-heavy and have minimal updates/inserts.
How To Prepare for the FTI Consulting SQL Interview
The best way to prepare for a FTI Consulting SQL interview is to practice, practice, practice. Besides solving the above FTI Consulting SQL interview questions, you should also solve the 200+ tricky sql questions on DataLemur which come from companies like Microsoft, Google, and Meta.
Each SQL question has multiple hints, detailed solutions and most importantly, there's an interactive coding environment so you can right in the browser run your query and have it executed.
To prep for the FTI Consulting SQL interview you can also be wise to practice interview questions from other consulting and professional service companies like:
Discover how FTI Consulting is harnessing the potential of AI to drive business growth and transformation!
However, if your SQL skills are weak, forget about diving straight into solving questions – improve your SQL foundations with this free SQL tutorial.
This tutorial covers topics including joins and INTERCEPT/EXCEPT – both of which pop up frequently during FTI Consulting SQL interviews.
FTI Consulting Data Science Interview Tips
What Do FTI Consulting Data Science Interviews Cover?
Beyond writing SQL queries, the other types of problems tested in the FTI Consulting Data Science Interview include:
- Statistics and Probability Questions
- Python or R Programming Questions
- Product-Sense Questions
- ML Modelling Questions
- Behavioral Interview Questions based on FTI Consulting company values
How To Prepare for FTI Consulting Data Science Interviews?
To prepare for FTI Consulting Data Science interviews read the book Ace the Data Science Interview because it's got:
- 201 interview questions taken from Google, Microsoft & tech startups
- a refresher covering Python, SQL & ML
- over 1000+ 5-star reviews on Amazon
Don't ignore the behavioral interview – prepare for that using this guide on acing behavioral interviews.