11 Fifth Third Bank SQL Interview Questions (Updated 2024)
At Fifth Third Bancorp, SQL is used across the company for analyzing transaction patterns to detect fraudulent activities and forecasting future revenue trends from customer data. So, it shouldn't surprise you that Fifth Third Bank LOVES to ask SQL coding questions during interviews for Data Analyst, Data Science, and BI jobs.
So, to help you practice, we've collected 11 Fifth Third Bancorp SQL interview questions – can you answer each one?
11 Fifth Third Bancorp SQL Interview Questions
SQL Question 1: Identify High-Value Customers at Fifth Third Bank
As a data analyst at Fifth Third Bank, one of your tasks is to frequently analyze the customer database to identify power users - customers who carry out transactions very frequently and hold high account balances. This information will help Fifth Third Bank identify and retain their most valuable customers.
You are required to write a SQL query to find the customers who have carried out more than 50 transactions and have an average account balance of more than 10,000 during the last year.
For this question, consider the following example tables:
Example Input:
| transaction_id | customer_id | transaction_date | amount |
|---|---|---|---|
| 101 | 123 | 2022-06-08 | 100 |
| 102 | 265 | 2022-06-10 | 500 |
| 103 | 362 | 2022-06-18 | 200 |
| 104 | 123 | 2022-07-26 | 50 |
| 105 | 981 | 2022-07-05 | 300 |
Example Input:
| account_id | customer_id | balance |
|---|---|---|
| 501 | 123 | 10200 |
| 502 | 265 | 8000 |
| 503 | 362 | 12000 |
| 504 | 123 | 13000 |
| 505 | 981 | 9000 |
Answer:
The SQL query first calculates the number of transactions each customer has carried out in the past year, and the average balance of each customer's account. It then joins these two result sets to find the customers who have more than 50 transactions and an average account balance of more than 10,000. These are the most valuable customers to Fifth Third Bank as they provide a consistent source of income through transaction fees and they trust the bank with their larger amount of money.
To practice a related customer analytics question on DataLemur's free interactive SQL code editor, try this Microsoft Teams Power User SQL Interview Question:
SQL Question 2: Department Salaries
You're given a table of Fifth Third Bank employee and department salary information. Write a SQL query to find departments where the departments average salary is higher than the company's average salary.
Try this question interactively on DataLemur:
The answer is LONG – 30+ lines of SQL. You can find a step-by-step solution here: Department vs. Company Salary.
SQL Question 3: What are the differences between an inner and a full outer join?
A full outer join returns all rows from both tables, including any unmatched rows, whereas an inner join only returns rows that match the join condition between the two tables.
For a tangible example, suppose you had a table of Fifth Third Bank orders and Fifth Third Bank customers.
Here's a SQL inner join using the orders and customers tables:
This query will return rows from the orders and customers tables that have matching values. Only rows with matching values will be included in the results.
Here is an example of a using the orders and customers tables:
This query will return all rows from both the orders and customers tables, including any rows that do not have matching values. Any rows with null values for either table will be included in the results.
Check out the Fifth Third Bank career page and see how their data scientists use SQL in their day-to-day operations.
SQL Question 4: Calculate Monthly Average Transactions
As a data analyst for Fifth Third Bank, you are given the task to analyze the transactional data. You are required to write a SQL query to calculate and present the average transactions per account for each month over a year. Assume that transactions happen at different times of a month.
Use the following sample data:
Example Input
| transaction_id | account_id | transaction_date | amount |
|---|---|---|---|
| 8912 | A100 | 02/03/2022 | 200.00 |
| 1530 | A101 | 02/26/2022 | 500.00 |
| 6710 | A100 | 02/18/2022 | 350.00 |
| 4245 | A102 | 06/08/2022 | 700.00 |
| 9823 | A101 | 06/15/2022 | 800.00 |
| 6745 | A100 | 06/29/2022 | 150.00 |
| 3423 | A102 | 12/05/2022 | 900.00 |
| 9823 | A101 | 12/15/2022 | 940.00 |
Expected Output:
Example Output
| month | account_id | avg_transaction_amt |
|---|---|---|
| 2 | A100 | 275.00 |
| 2 | A101 | 500.00 |
| 6 | A100 | 150.00 |
| 6 | A101 | 800.00 |
| 6 | A102 | 700.00 |
| 12 | A100 | 0 |
| 12 | A101 | 940.00 |
| 12 | A102 | 900.00 |
Answer:
You can use a SQL window function to solve this problem. Use the function as the window function, partitioned by and ordered by :
This query first uses to get the month from the field. Then it calculates the average amount of transactions for each account in each month with . Finally, it sorts the results by and .
p.s. Window functions show up super frequently during SQL interviews, so practice the 27+ window function questions on DataLemur
SQL Question 5: Can you explain the distinction between a correlated and a non-correlated sub-query?
A non-correlated sub-query is not linked to the outer query and can be run on its own. It does not reference any columns from the outer query and is used to retrieve additional data for the outer query. On the other hand, a correlated sub-query is one that relies on the outer query and cannot be run independently. It uses the outer query to filter or transform data by referencing a column from it, while the outer query uses the results of the inner query.
Here is an example of a non-correlated sub-query:
The sub-query in this case is non-correlated, as it does not reference any columns from the outer query.
Here is an example of a correlated sub-query:
This query selects the and total sales of all Fifth Third Bank customers in the sales table whose are greater than the average of their own customer group. The sub-query in this case is correlated with the outer query, as it references the column from the outer query ().
Non-correlated sub-queries are typically faster, as they only have to be executed once, wheareas correlated sub-queries are slower since they have to be re-evaluated for each row of the outer query.
SQL Question 6: Bank Account and Transaction Management
The Fifth Third Bank requires a system to manage its customer's bank accounts and their transactions. Design a relational database schema to hold information regarding the accounts and their transactions which includes Account Number, Account Type (Checking, Savings, etc.), CustomerID, TransactionID, Transaction Type (Deposit, Withdrawal, Transfer, etc.), Transaction Amount, Transaction Time.
Consider the following sample data:
Example Input:
| account_number | account_type | customer_id |
|---|---|---|
| 1001 | Checking | 201 |
| 1002 | Savings | 205 |
| 1003 | Checking | 207 |
| 1004 | Savings | 210 |
| 1005 | Checking | 215 |
Example Input:
| transaction_id | account_number | transaction_type | transaction_amount | transaction_time |
|---|---|---|---|---|
| 1 | 1001 | Deposit | 5000 | 06/08/2022 00:00:00 |
| 2 | 1001 | Withdrawal | 200 | 06/10/2022 00:00:00 |
| 3 | 1002 | Deposit | 3000 | 06/18/2022 00:00:00 |
| 4 | 1003 | Transfer | 1000 | 07/26/2022 00:00:00 |
| 5 | 1004 | Deposit | 2500 | 07/05/2022 00:00:00 |
The aim is to write a SQL query that shows the current balance of all bank accounts.
Answer:
This Query will return the current balance of all the bank accounts. It subtracts the amounts of 'Withdrawals' and 'Transfers' from 'Deposits' for each account separately.
SQL Question 7: Can you explain the purpose of the constraint?
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 Fifth Third Bank'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 8: Filter Fifth Third Bank Customer Records
As a data analyst for Fifth Third Bank, you have been given access to the customer records database. You need to develop breakdowns of customers for the marketing team. They are interested in customers who have opened accounts in the last year, but haven't used the bank's online banking portal. They're also interested in customers who have a checking account balance of more than $5,000, but have not deposited in the last month. Generate a list of such customers by creating a SQL query.
Please filter:
- New customers, who opened accounts in the last year and haven't used the online banking portal.
- High balance customers, those holding more than $5000 in their checking account, but having not deposited in the last month
Example Input:
| customer_id | name | opened_account_date | online_portal_usage | checking_balance |
|---|---|---|---|---|
| 101 | John Doe | 2021-07-01 | True | 10000.00 |
| 102 | Jane Doe | 2021-05-01 | False | 5300.00 |
| 103 | Paul Newman | 2022-03-01 | False | 1000.00 |
| 104 | Oprah Winfrey | 2021-12-01 | False | 10000.00 |
| 105 | Taylor Swift | 2020-01-01 | True | 15000.00 |
Example Input:
| transaction_id | customer_id | transaction_date | transaction_type | transaction_amount |
|---|---|---|---|---|
| 1 | 101 | 2022-02-01 | Deposit | 1000.00 |
| 2 | 102 | 2022-02-10 | Deposit | 300.00 |
| 3 | 103 | 2022-02-20 | Withdrawal | 100.00 |
| 4 | 104 | 2021-01-21 | Deposit | 1000.00 |
| 5 | 105 | 2022-03-31 | Deposit | 5000.00 |
Answer:
This query uses the WHERE clause with AND and OR logical operators to filter the customer records. For new customers, the query retrieves the records where account opening date is within the last year and the online portal usage is False. For high balance customers, the query retrieves the records where the checking account balance is greater than $5000 and there are no deposits made in the last month. The results are ordered by the customer id.
SQL Question 9: Calculate Click-Through-Rates For Displayed Ads
Fifth Third Bank has been displaying digital ads for different types of banking services such as mortgage loans, savings accounts, and credit cards. This information is logged in a table named . When a visitor clicks on one ad and forwards to the banking service page related to the ad, this action is logged in another table named .
Your task is to calculate the click-through rate (CTR) for each type of banking service based on the ad clicks and the total ads displayed. The CTR is calculated as .
Assume you are provided with the following tables:
Example Input:
| ad_id | service_type | display_date |
|---|---|---|
| 1001 | 'Mortgage Loan' | '06/08/2022' |
| 1002 | 'Savings Account' | '06/10/2022' |
| 1003 | 'Credit Card' | '06/18/2022' |
| 1004 | 'Mortgage Loan' | '06/08/2022' |
| 1005 | 'Credit Card' | '06/10/2022' |
Example Input:
| visit_id | ad_id | visit_date |
|---|---|---|
| 101 | 1001 | '06/08/2022' |
| 102 | 1002 | '06/10/2022' |
| 103 | 1004 | '06/08/2022' |
| 104 | 1003 | '06/18/2022' |
| 105 | 1002 | '06/10/2022' |
Answer:
This query first performs a LEFT JOIN between and with the as the common key. It then groups the results by .
The total ads displayed and total click-throughs for each service type are calculated using the COUNT() function. The CTR is then calculated by dividing the total click-throughs by total ads displayed, multiplied by 100 to get the percentage. ROUND() is used to limit the result to two decimal points.
To solve another question about calculating rates, try this TikTok SQL Interview Question on DataLemur's online SQL coding environment:
SQL Question 10: What are the different kinds of joins in SQL?
In SQL, a join retrieves rows from multiple tables and combines them into a single result set.
Four JOIN types can be found in SQL. For an example of each one, suppose you had a table of Fifth Third Bank orders and Fifth Third Bank customers.
-
INNER JOIN: Rows from both tables are retrieved when there is a match in the shared key or keys. An between the Orders and Customers tables would return only rows where the in the Orders table matches the in the Customers table.
-
LEFT JOIN: A retrieves all rows from the left table (in this case, the Orders table) and any matching rows from the right table (the Customers table). If there is no match in the right table, NULL values will be returned for the right table's columns.
-
RIGHT JOIN: A combines all rows from the right table (in this case, the Customers table) and any matching rows from the left table (the Orders table). If there is no match in the left table, NULL values will be displayed for the left table's columns.
-
FULL OUTER JOIN: A combines all rows from both tables, regardless of whether there is a match in the shared key or keys. If there is no match, NULL values will be displayed for the columns of the non-matching table.
SQL Question 11: Find The Clients with 'Third' in their Names
You are working as a Data Analyst at Fifth Third Bank and you have been asked to identify all clients in the customer database who have 'Third' in their names. Filter down the dataset using the SQL keyword LIKE to find the records that match this specific pattern.
Example Input:
| client_id | first_name | last_name | signup_date | account_type |
|---|---|---|---|---|
| 2541 | John | Thirdly | 05/20/2018 00:00:00 | Savings |
| 3725 | Samantha | Forthright | 02/14/2020 00:00:00 | Checking |
| 1734 | Paul | Thirdson | 07/15/2019 00:00:00 | Business |
| 4652 | Rachel | Anderson | 03/10/2021 00:00:00 | Savings |
| 5917 | Michael | Thirston | 11/05/2016 00:00:00 | Credit Card |
Answer:
In the query above, the keyword is used with the wildcard character to filter down records where either 'first_name' or 'last_name' contains 'Third'. A record will be returned if 'Third' appears anywhere in the respective columns of 'first_name' and 'last_name'.
Example Output:
| client_id | first_name | last_name | signup_date | account_type |
|---|---|---|---|---|
| 2541 | John | Thirdly | 05/20/2018 00:00:00 | Savings |
| 1734 | Paul | Thirdson | 07/15/2019 00:00:00 | Business |
Fifth Third Bank SQL Interview Tips
The best way to prepare for a SQL interview, besides making sure you have strong SQL fundamentals, is to practice a ton of real SQL questions that were asked in recent job interviews.
Beyond just solving the earlier Fifth Third Bank SQL interview questions, you should also solve the 200+ FAANG SQL Questions on DataLemur which come from companies like Facebook, Google and unicorn tech startups.
Each SQL question has hints to guide you, fully explained answers along with a discussion board to see how others solved it and best of all, there's an interactive SQL code editor so you can easily right in the browser your SQL query answer and have it executed.
To prep for the Fifth Third Bank SQL interview it is also useful to practice interview questions from other banking & finanacial services companies like:
But if your SQL foundations are weak, forget about going right into solving questions – improve your SQL foundations with this interactive SQL tutorial.
This tutorial covers topics including creating pairs via SELF-JOINs and ordering data – both of which come up often in SQL job interviews at Fifth Third Bank.
Fifth Third Bancorp Data Science Interview Tips
What Do Fifth Third Bank Data Science Interviews Cover?
Besides SQL interview questions, the other types of problems tested in the Fifth Third Bank Data Science Interview include:
- Experimental Design & AB Testing Questions
- Python or R Programming Questions
- Product Analytics Questions
- ML Interview Questions
- Behavioral & Resume-Based Questions
How To Prepare for Fifth Third Bank Data Science Interviews?
The best way to prepare for Fifth Third Bank Data Science interviews is by reading Ace the Data Science Interview. The book's got:
- 201 Interview Questions from FAANG tech companies
- A Refresher covering Product Analytics, SQL & ML
- Amazing Reviews (1000+ 5-star reviews on Amazon)