8 Huntington Bank SQL Interview Questions (Updated 2024)
Huntington Bank employees write SQL queries often for analyzing banking transaction data for fraud detection, and managing customer data for personalized marketing strategies. For this reason Huntington Bank often tests SQL questions during interviews for Data Science and Data Engineering positions.
Thus, to help you ace the Huntington Bank SQL interview, we've collected 8 Huntington Bancshares SQL interview questions in this article.
8 Huntington Bancshares SQL Interview Questions
SQL Question 1: Analyzing Loan Payments
As a data analyst at Huntington Bank, you are tasked with analyzing the loan payment behavior of customers. In particular, the bank wants to know the total amount paid by each customer each month and the ranking of customers monthly, based on the total amount paid. A ranking of 1 means the customer paid the most compared to other customers in a particular month.
We have the following data in the table:
Example Input:
| payment_id | customer_id | payment_date | amount_paid |
|---|---|---|---|
| 1 | 001 | 2022-07-01 | 500.00 |
| 2 | 002 | 2022-07-05 | 450.00 |
| 3 | 001 | 2022-07-15 | 500.00 |
| 4 | 003 | 2022-07-10 | 700.00 |
| 5 | 002 | 2022-08-05 | 450.00 |
| 6 | 003 | 2022-08-10 | 700.00 |
| 7 | 001 | 2022-08-01 | 500.00 |
| 8 | 001 | 2022-08-15 | 500.00 |
Question:
Write a SQL query that provides the total amount paid by each customer each month and their ranking based the total amount paid. The output should be sorted by the year and month in ascending order and the ranking in descending order.
Example Output:
| Year | Month | Customer ID | Total Amount Paid | Ranking |
|---|---|---|---|---|
| 2022 | 7 | 003 | 700.00 | 1 |
| 2022 | 7 | 001 | 1000.00 | 2 |
| 2022 | 7 | 002 | 450.00 | 3 |
| 2022 | 8 | 001 | 1000.00 | 1 |
| 2022 | 8 | 003 | 700.00 | 2 |
| 2022 | 8 | 002 | 450.00 | 3 |
Answer:
In the SQL query above, we used an aggregate function SUM() to calculate the total amount paid by each customer each month. We also used the window function RANK() to rank the customers based on the total amount paid. The PARTITION BY clause in the window function allows us to calculate the ranking separately for each month. Finally, we grouped the results by year, month, and customer_id and ordered the output by year, month, and ranking in descending order.
For more window function practice, try this Uber SQL Interview Question within DataLemur's online SQL code editor:
SQL Question 2: Employees Earning More Than Their Boss
Given a table of Huntington Bank employee salary information, write a SQL query to find all employees who make more than their direct boss.
Huntington Bank 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.
Test your SQL query for this question 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 confusing, you can find a step-by-step solution here: Employees Earning More Than Managers.
SQL Question 3: What are the ACID properties in a DBMS?
A transaction is a one or more SQL commands which are executed as a singular unit if the transaction -commits- (or no execution hapens if the transaction -aborts-).
For transactions, a DBMS is supposed to enforce the follwing ACID properties: Atomicity, Consistency, Isolation, & Durability.
Here's what each one means:
- Atomicity: the transaction is completed in an all-or-nothing way (no partial commits)
- Consistency: the transaction is valid and follows all constraints and restrictions
- Isolation: the transaction doesn't affect another transaction
- Durability: the committed transactions is stored permanently in the DB (it doesn't dissapear!)
As you can see, it's pretty important for the multiple databases where Huntington Bank store's it's data to be ACID-compliant!
SQL Question 4: Average Account Balance Query for Huntington Bank
As a data scientist for Huntington Bank, they might want you to calculate the average account balance for each account type over the span of a month. The bank offers three main types of accounts: Checking, Savings, and Certificate of Deposit (CD).
Example Input:
| account_id | user_id | account_type | opening_date | closing_date |
|---|---|---|---|---|
| 101 | 123 | Checking | 01/13/2022 | - |
| 102 | 265 | Savings | 01/15/2022 | - |
| 103 | 362 | Checking | 01/20/2022 | - |
| 104 | 192 | CD | 02/02/2022 | - |
| 105 | 981 | Savings | 02/05/2022 | - |
Example Input:
| account_id | date | balance |
|---|---|---|
| 101 | 01/30/2022 | 1200.00 |
| 101 | 02/28/2022 | 1500.20 |
| 102 | 01/30/2022 | 5500.00 |
| 102 | 02/28/2022 | 5600.30 |
| 103 | 01/30/2022 | 2200.00 |
| 103 | 02/28/2022 | 2350.40 |
| 104 | 01/30/2022 | - |
| 104 | 02/28/2022 | 5500.00 |
| 105 | 01/30/2022 | - |
| 105 | 02/28/2022 | 10400.20 |
Your task is to write an SQL query which estimates the average balance for each type of account for the months of January and February.
Answer:
Example Output:
| account_type | month | avg_balance |
|---|---|---|
| Checking | 01/01/2022 | 1700.00 |
| Checking | 02/01/2022 | 1925.30 |
| Savings | 01/01/2022 | 5500.00 |
| Savings | 02/01/2022 | 8000.25 |
| CD | 01/01/2022 | - |
| CD | 02/01/2022 | 5500.00 |
This query first joins the table and table on . It then averages the column, grouping by both and , after truncating the column to the precision of month. The average calculation excludes any NULL balances, so it properly handles accounts that were not opened in the month of January.
To practice a very similar question try this interactive Amazon Average Review Ratings Question which is similar for calculating averages for categories or this JPMorgan Chase Card Launch Success Question which is similar for aggregating data over a month.
Check out the Huntington Bank career page and see what type of role is the best fit for your experience.
SQL Question 5: Why would it make sense to denormalize a database?
Denormalization is the practice of altering a database schema in a way that breaks the normalization rules (1st, 2nd, 3rd normal forms).
For example, in a database that stores Huntington Bank sales analytics data, you might have separate tables for "customers," "orders," and "products," with foreign key constraints linking the tables together. This helps to ensure the integrity of the data and reduces redundancy, but it can also make queries that involve multiple tables more complex and slower to execute.
By de-normalizing the database, you can combine some of the data from separate tables into a single table, which can reduce the number of joins that are required to retrieve the data you need. This can speed up queries and improve the performance of your database.
However, it's important to carefully consider the trade-offs of de-normalization before making any changes to your database. De-normalization can make it more difficult to maintain the integrity and reliability of your data, and can also increase the risk of data redundancy. It's generally best to use de-normalization as a performance optimization technique only when necessary, and to carefully evaluate the benefits and drawbacks in the context of your specific database and workload.
SQL Question 6: Calculate the Sum of Loan Amounts by Loan Type
Assume we have a table where each row represents a loan granted by Huntington Bank. The loan type could be "Personal", "Mortgage", "Car", etc. Write a SQL query to find the sum of loan amounts by loan type.
Example Input:
| loan_id | customer_id | loan_type | loan_amount | loan_date |
|---|---|---|---|---|
| 1001 | 501 | "Personal" | 10000 | 01/01/2022 |
| 1002 | 502 | "Mortgage" | 200000 | 01/02/2022 |
| 1003 | 503 | "Car" | 15000 | 01/03/2022 |
| 1004 | 504 | "Personal" | 15000 | 01/04/2022 |
| 1005 | 505 | "Mortgage" | 250000 | 01/05/2022 |
Example Output:
| loan_type | total_loan_amount |
|---|---|
| "Personal" | 25000 |
| "Mortgage" | 450000 |
| "Car" | 15000 |
Answer:
This SQL query uses the clause to group the loans by their type. Then, for each group, it calculates the sum of the loan amounts using the function. The resulting table contains each loan type and the corresponding total loan amount given out by Huntington Bank for that type.
SQL Question 7: Can you explain what a cross-join is and the purpose of using them?
A cross-join, also known as a cartesian join, is a type of JOIN operation in SQL that creates a new table by combining each row from the first table with every row from the second table. This results in a table with a row for every possible combination of rows from the two input tables.
An example of when this might be useful is if you wanted to first make a dataset containing all possible pairs of customers and products data, in order to later build a Machine Learning model to predict the probability of a customer purchasing a particular product.
However, it is important to note that cross-joins can create very large tables, especially if the input tables have a large number of rows. For example, if you had 10,000 customers and 5,000 different product SKUs, the resulting cross-join would have 50 million rows.
SQL Question 8: Calculate Average Balance with Math Functions
Huntington Bank has a table that records the balance of customers for the past year. Given this data, the bank wants to find the average absolute monthly balance per customer, rounded to the nearest dollar.
The monthly balance should be aggregated by the absolute difference between the maximum and minimum balance within a month.
The contains the following columns:
- : The unique ID of the bank customer
- : The date of the available balance
- : The balance amount on a given day
Example Input:
| customer_id | balance_date | balance |
|---|---|---|
| 1001 | 03/01/2022 | 5000 |
| 1001 | 03/15/2022 | -5000 |
| 1002 | 03/10/2022 | 7000 |
| 1002 | 03/20/2022 | 10000 |
| 1002 | 03/31/2022 | 8000 |
| 1001 | 04/02/2022 | 4000 |
| 1001 | 04/19/2022 | 3500 |
| 1002 | 04/05/2022 | 9000 |
| 1002 | 04/28/2022 | 7000 |
Example Output:
| customer_id | month | avg_abs_balance |
|---|---|---|
| 1001 | 3 | 5000 |
| 1001 | 4 | 4000 |
| 1002 | 3 | 3000 |
| 1002 | 4 | 2000 |
Answer:
This query first calculates the maximum and minimum balance for each customer per month in the subquery. The difference between these max and min balances are then averaged and rounded to the nearest dollar in the main query.
The two most similar questions to the one given are "Cards Issued Difference" and "Compressed Mean". They both involve similar SQL operations - the calculation of differences between maximum and minimum values and the calculation of averages.
Here is the markdown:
To practice a very similar question try this interactive JPMorgan Chase Cards Issued Difference Question which is similar for calculating the difference between maximum and minimum balances or this Alibaba Compressed Mean Question which is similar for calculating average balances.
Huntington Bank SQL Interview Tips
The key to acing a Huntington Bank SQL interview is to practice, practice, and then practice some more!
Besides solving the above Huntington Bank SQL interview questions, you should also solve the 200+ DataLemur interview questions which come from companies like Facebook, Google and unicorn tech startups.
Each problem on DataLemur has hints to guide you, detailed solutions and best of all, there's an interactive coding environment so you can instantly run your query and have it executed.
To prep for the Huntington Bank SQL interview you can also be useful to solve interview questions from other banking & finanacial services companies like:
But if your SQL coding skills are weak, don't worry about diving straight into solving questions – improve your SQL foundations with this free SQL for Data Analytics course.
This tutorial covers topics including joining a table to itself and functions like SUM()/COUNT()/AVG() – both of these pop up routinely in Huntington Bank SQL interviews.
Huntington Bancshares Data Science Interview Tips
What Do Huntington Bank Data Science Interviews Cover?
Beyond writing SQL queries, the other question categories tested in the Huntington Bank Data Science Interview include:
- Statistics & AB Testing Questions
- Python or R Programming Questions
- Product Metrics Interview Questions
- ML Modelling Questions
- Behavioral Based Interview Questions
How To Prepare for Huntington Bank Data Science Interviews?
To prepare for Huntington Bank Data Science interviews read the book Ace the Data Science Interview because it's got:
- 201 interview questions sourced from Facebook, Google & startups
- a refresher on Python, SQL & ML
- over 1000+ reviews on Amazon & 4.5-star rating