8 Bank of America SQL Interview Questions (Updated 2024)
Bank of America employees write SQL queries for analyzing financial transactions and monitoring customer account behaviors for fraud detection. Because of this, Bank of America almost always asks SQL coding questions during interviews for Data Analytics, Data Science, and Data Engineering jobs.
To help you ace the Bank of America SQL interview, here’s 8 Bank of America SQL interview questions – scroll down to start solving them!
8 Bank of America SQL Interview Questions
SQL Question 1: Analyze Customer Behavior in Loan Repayment
As a data analyst at Bank of America, your team needs to understand customer behaviour in loan repayment. Given a table of loan transactions, write a SQL query to retrieve the customer_id, loan_id, paid_amount for each transaction, and the cumulative amount of loan payment each customer has made up to each transaction. Order the result by customer_id and transaction_date.
Please consider the following sample data:
Example Input:
| transaction_id | customer_id | transaction_date | loan_id | paid_amount |
|---|---|---|---|---|
| 1234 | 1111 | 2022-01-01 | 8970 | $500 |
| 1456 | 1111 | 2022-02-01 | 8970 | $300 |
| 1723 | 2222 | 2022-01-15 | 8971 | $1000 |
| 1987 | 2222 | 2022-03-01 | 8971 | $2000 |
| 2290 | 1111 | 2022-03-15 | 8970 | $400 |
Example Output:
| transaction_id | customer_id | transaction_date | loan_id | paid_amount | cumulative_payment |
|---|---|---|---|---|---|
| 1234 | 1111 | 2022-01-01 | 8970 | $500 | $500 |
| 1456 | 1111 | 2022-02-01 | 8970 | $300 | $800 |
| 2290 | 1111 | 2022-03-15 | 8970 | $400 | $1200 |
| 1723 | 2222 | 2022-01-15 | 8971 | $1000 | $1000 |
| 1987 | 2222 | 2022-03-01 | 8971 | $2000 | $3000 |
Answer:
In the above query, the window function is used to calculate the cumulative payment for each customer up to each transaction. The clause inside the clause ensures that the cumulative sum is calculated in the correct order of transactions. The final at the end of the query orders the output by and as requested.
p.s. Window functions show up super frequently during SQL interviews, so practice the 27+ window function questions on DataLemur
SQL Question 2: Second Highest Salary
Suppose you had a table of Bank of America employee salary data. Write a SQL query to find the 2nd highest salary at the company.
Bank of America Example Input:
| employee_id | salary |
|---|---|
| 1 | 2500 |
| 2 | 800 |
| 3 | 1000 |
| 4 | 1200 |
Example Output:
| second_highest_salary |
|---|
| 1200 |
Code your solution to this interview question interactively on DataLemur:
Answer:
You can find a step-by-step solution here: 2nd Highest Salary.
SQL Question 3: What does it mean to perform a self-join?
A self-join is a type of JOIN where a table is joined to itself. To execute a self-join, you must include the table name twice in the FROM clause and assign a different alias to each instance. You can then join the two copies of the table using a JOIN clause, and use a WHERE clause to specify the relationship between the rows.
For example, say you had website visitor data for Bank of America, exported from the company's Google Analytics account. In support of the web-dev team, you had to analyze pairs of pages for UX or navigational issues. As part of that analysis, you wanted to generate all pairs of URLs, but needed to avoid pairs where both the URLs were the same since that's not a valid pair.
The self-join query would like the following:
This query returns the url of each page () along with the url of the page that referred to it (). The self-join is performed using the field, which specifies the id of the page that referred the visitor to the current page, and avoids any pages that referred themself (aka data anomalies).
SQL Question 4: Design a database for Bank of America's loan system
Bank of America offers various types of loans to its customers. They want to keep track of customer information, loan type details, loan transaction details etc. Design a relational database schema to model this system. Which tables will you create? What rows and columns will they contain?
Assume the following facts:
- A customer can have multiple loans.
- Each loan is of a specific type (home, auto, personal, etc.)
- Each loan transaction includes detail about date, principal, interest, etc.
Consider the following tables:
Example input:
| customer_id | first_name | last_name | |
|---|---|---|---|
| 101 | John | Doe | johndoe@example.com |
| 102 | Jane | Doe | janedoe@example.com |
| 103 | Jim | Brown | jimbrown@example.com |
Example input:
| loan_type_id | loan_name | interest_rate | max_loan_amount |
|---|---|---|---|
| 1 | Home loan | 3.5% | 500000 |
| 2 | Auto loan | 4.0% | 30000 |
| 3 | Personal loan | 6.0% | 20000 |
Example input:
| loan_id | loan_type_id | customer_id | loan_amount | loan_duration_months |
|---|---|---|---|---|
| 001 | 1 | 101 | 100000 | 60 |
| 002 | 2 | 101 | 20000 | 36 |
| 003 | 3 | 102 | 15000 | 24 |
Example input:
| transaction_id | loan_id | transaction_date | principal | interest |
|---|---|---|---|---|
| 9001 | 001 | 2022-10-01 | 1500 | 350 |
| 9002 | 002 | 2022-10-02 | 450 | 50 |
| 9003 | 003 | 2022-10-03 | 650 | 50 |
Question: Write a SQL query that gives the current outstanding of each loan for each customer.
Answer:
This query joins all the tables using their appropriate foreign keys. It then groups by customer name and loan details to calculate the outstanding amount per loan by subtracting the sum of principal paid from the original loan amount.
SQL Question 5: How do you locate records in one table that are absent from another?
To locate records in one table that are absent from another, you can use a and then look for values in the right-side table.
For example, say you exported Bank of America's CRM (Customer Relationship Management) database, and had a table of sales leads, and a second table of companies.
Here's an example of how a query can find all sales leads that are not associated with a company:
This query returns all rows from the sales leads table, along with any matching rows from the companies table. If there is no matching row in the companies table, values will be returned for all of the right table's columns. The clause then filters out any rows where the column is , leaving only sales leads that are NOT associated with a company.
SQL Question 6: Average Account Balance
As an analyst at Bank of America, we often need to understand the average balance held in accounts as this helps the bank to assess profitability and risks. Write a SQL query to find the average balance for each account type (checking, savings) over the last 30 days.
Example Input:
| date | account_id | account_type | balance |
|---|---|---|---|
| 2022-09-01 | 001 | checking | 5000 |
| 2022-09-01 | 002 | savings | 7000 |
| 2022-09-02 | 001 | checking | 5300 |
| 2022-09-02 | 002 | savings | 7100 |
| 2022-09-03 | 001 | checking | 5200 |
| 2022-09-03 | 002 | savings | 7200 |
Example Output:
| account_type | avg_balance |
|---|---|
| checking | 5167 |
| savings | 7100 |
Answer:
This query filters out the account balance records for the last 30 days, then groups by account type, and calculates average balance by each account type. Only the records in the date range specified () are considered for calculating the average balance. This means the balance of each account type will show how much on average customers have in their account over the past 30 days.
To practice a very similar question try this interactive JPMorgan Chase Card Launch Success Question which is similar for requiring aggregation of financial data or this Amazon Average Review Ratings Question which is similar for requiring grouping and average calculation.
SQL Question 7: What is the purpose of the UNIQUE constraint?
The UNIQUE constraint is used to ensure the uniqueness of the data in a column or set of columns in a table. It prevents the insertion of duplicate values in the specified column or columns and helps to ensure the integrity and reliability of the data in the database.
For example, say you were on the Marketing Analytics team at Bank of America and were doing some automated keyword research:
Your keyword database might store SEO data like this:
In this example, the UNIQUE constraint is applied to the "keyword" field to ensure that each keyword is unique. This helps to ensure the integrity of the data in the database and prevents errors that could occur if two rows had the same keyword.
Check out Bank of America's global career page and see what role might be the best fit for you.
SQL Question 8: Calculate Click-Through Rates of Bank of America Digital Ad Campaign
For a financial institution like Bank of America, digital marketing campaigns can target different financial products and services. It is advanced analytics' role to assess the performance of these campaigns. Your task is to calculate the of Bank of America's digital ad campaign by dividing the number of users who clicked on the ad by the total number of users who viewed the ad.
Here we have two tables: and .
Example Input:
| view_id | user_id | view_date | ad_id |
|---|---|---|---|
| 321 | 345 | 06/08/2022 00:00:00 | 9001 |
| 465 | 654 | 06/09/2022 00:00:00 | 9002 |
| 892 | 763 | 06/10/2022 00:00:00 | 9001 |
| 241 | 935 | 06/11/2022 00:00:00 | 9003 |
| 678 | 124 | 07/02/2022 00:00:00 | 9002 |
Example Input:
| click_id | user_id | click_date | ad_id |
|---|---|---|---|
| 1901 | 345 | 06/08/2022 00:00:00 | 9001 |
| 2956 | 654 | 06/11/2022 00:00:00 | 9002 |
| 6473 | 763 | 06/12/2022 00:00:00 | 9001 |
| 6754 | 124 | 07/03/2022 00:00:00 | 9002 |
Answer:
Here's the PostgreSQL query that calculates the click-through rate for each ad:
This query fetches the total number of unique views and clicks per ad and calculates the as the ratio of total clicks to total views. The ensures that all ads viewed are included, even if they have not been clicked.
To practice a similar problem on DataLemur's free interactive SQL code editor, attempt this Meta SQL interview question:
How To Prepare for the Bank of America SQL Interview
The best way to prepare for a Bank of America SQL interview is to practice, practice, practice.
In addition to solving the earlier Bank of America SQL interview questions, you should also solve the 200+ DataLemur SQL Interview Questions which come from companies like Microsoft, Google, and Facebook.
Each interview question has hints to guide you, step-by-step solutions and best of all, there's an online SQL code editor so you can right in the browser run your query and have it executed.
To prep for the Bank of America SQL interview you can also be useful to practice interview questions from other banking & finanacial services companies like:
In case your SQL skills are weak, don't worry about diving straight into solving questions – refresh your SQL knowledge with this DataLemur SQL Tutorial.
This tutorial covers topics including WHERE with AND/OR/NOT and aggregate functions – both of which show up often in SQL interviews at Bank of America.
Bank of America Data Science Interview Tips
What Do Bank of America Data Science Interviews Cover?
Beyond writing SQL queries, the other question categories to prepare for the Bank of America Data Science Interview include:
- Statistics and Probability Questions
- Coding Questions in Python or R
- Product & Business Analytics Questions
- Machine Learning and Predictive Modeling Questions
- Behavioral & Resume-Based Questions
How To Prepare for Bank of America Data Science Interviews?
I'm sort of biased, but I believe the best way to prepare for Bank of America Data Science interviews is to read the book I wrote: Ace the Data Science Interview.
The book covers 201 interview questions taken from tech companies like Netflix, Google, & Airbnb. It also has a crash course on SQL, AB Testing & ML. And finally it's helped thousands of people land their dream job in data, which is how it's gotten to be an Amazon best-seller with over 1000+ 5-star reviews.