logo

11 Fifth Third Bank SQL Interview Questions (Updated 2024)

Updated on

June 30, 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?

Fifth Third Bank SQL Interview Questions

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_idcustomer_idtransaction_dateamount
1011232022-06-08100
1022652022-06-10500
1033622022-06-18200
1041232022-07-2650
1059812022-07-05300
Example Input:
account_idcustomer_idbalance
50112310200
5022658000
50336212000
50412313000
5059819000

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: Microsoft SQL Interview Question: Teams Super User

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:

Department vs. Company Salary

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.

Fifth Third Bancorp SQL Interview Questions

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_idaccount_idtransaction_dateamount
8912A10002/03/2022200.00
1530A10102/26/2022500.00
6710A10002/18/2022350.00
4245A10206/08/2022700.00
9823A10106/15/2022800.00
6745A10006/29/2022150.00
3423A10212/05/2022900.00
9823A10112/15/2022940.00

Expected Output:

Example Output
monthaccount_idavg_transaction_amt
2A100275.00
2A101500.00
6A100150.00
6A101800.00
6A102700.00
12A1000
12A101940.00
12A102900.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

DataLemur SQL Questions

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_numberaccount_typecustomer_id
1001Checking201
1002Savings205
1003Checking207
1004Savings210
1005Checking215
Example Input:
transaction_idaccount_numbertransaction_typetransaction_amounttransaction_time
11001Deposit500006/08/2022 00:00:00
21001Withdrawal20006/10/2022 00:00:00
31002Deposit300006/18/2022 00:00:00
41003Transfer100007/26/2022 00:00:00
51004Deposit250007/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:

  1. New customers, who opened accounts in the last year and haven't used the online banking portal.
  2. High balance customers, those holding more than $5000 in their checking account, but having not deposited in the last month
Example Input:
customer_idnameopened_account_dateonline_portal_usagechecking_balance
101John Doe2021-07-01True10000.00
102Jane Doe2021-05-01False5300.00
103Paul Newman2022-03-01False1000.00
104Oprah Winfrey2021-12-01False10000.00
105Taylor Swift2020-01-01True15000.00
Example Input:
transaction_idcustomer_idtransaction_datetransaction_typetransaction_amount
11012022-02-01Deposit1000.00
21022022-02-10Deposit300.00
31032022-02-20Withdrawal100.00
41042021-01-21Deposit1000.00
51052022-03-31Deposit5000.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_idservice_typedisplay_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_idad_idvisit_date
1011001'06/08/2022'
1021002'06/10/2022'
1031004'06/08/2022'
1041003'06/18/2022'
1051002'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 interview question from TikTok

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.

  1. 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.

  2. 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.

  3. 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.

  4. 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_idfirst_namelast_namesignup_dateaccount_type
2541JohnThirdly05/20/2018 00:00:00Savings
3725SamanthaForthright02/14/2020 00:00:00Checking
1734PaulThirdson07/15/2019 00:00:00Business
4652RachelAnderson03/10/2021 00:00:00Savings
5917MichaelThirston11/05/2016 00:00:00Credit 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_idfirst_namelast_namesignup_dateaccount_type
2541JohnThirdly05/20/2018 00:00:00Savings
1734PaulThirdson07/15/2019 00:00:00Business

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. DataLemur Questions

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.

Free 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:

Fifth Third Bank Data Scientist

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)

Acing Data Science Interview