9 KeyBank SQL Interview Questions (Updated 2024)
Data Scientists, Analysts, and Data Engineers at KeyBank code up SQL queries as a big part of their job. They use SQL for analyzing financial data for insights and maintaining the integrity of customer databases. Because of this, KeyBank usually asks SQL coding interview questions.
So, to help you study, here's 9 KeyCorp SQL interview questions – how many can you solve?
9 KeyCorp SQL Interview Questions
SQL Question 1: Identifying High-Value Customers for KeyBank
For KeyBank, an important metric may involve monitoring the customers who frequently engage in large-amount transactions. These "whale users" can be identified as customers who generate a high total amount of money transferred or received within a specific period of time. Suppose we have access to the transactions data, your task is to write a SQL query to find the top 5 users with the highest total transaction amount in the last one month.
Example Input:
Answer:
In the provided SQL query, transactions in the last month are filtered first. The summed transaction amount for each user is calculated using a GROUP BY clause. Then, the users are sorted in descending order of the total transaction amount to find the top five "whale users". Remember to replace the dates in the WHERE clause with the exact dates for the month that you are interested in or adjust the query to make it relative to the current date.
To practice a related super-user data analysis question on DataLemur's free online SQL coding environment, try this Microsoft Teams Power User SQL Interview Question:
SQL Question 2: Top 3 Department Salaries
Given a table of KeyBank employee salary data, write a SQL query to find the top 3 highest paid employees within each department.
KeyBank Example Input:
| employee_id | name | salary | department_id |
|---|---|---|---|
| 1 | Emma Thompson | 3800 | 1 |
| 2 | Daniel Rodriguez | 2230 | 1 |
| 3 | Olivia Smith | 2000 | 1 |
| 4 | Noah Johnson | 6800 | 2 |
| 5 | Sophia Martinez | 1750 | 1 |
| 8 | William Davis | 6800 | 2 |
| 10 | James Anderson | 4000 | 1 |
Example Input:
| department_id | department_name |
|---|---|
| 1 | Data Analytics |
| 2 | Data Science |
Example Output:
| department_name | name | salary |
|---|---|---|
| Data Analytics | James Anderson | 4000 |
| Data Analytics | Emma Thompson | 3800 |
| Data Analytics | Daniel Rodriguez | 2230 |
| Data Science | Noah Johnson | 6800 |
| Data Science | William Davis | 6800 |
Solve this question and run your code right in DataLemur's online SQL environment:
Answer:
We use the DENSE_RANK() window function to generate unique ranks for each employee's salary within their department, with higher salaries receiving lower ranks. Then, we wrap this up in a CTE and filter the employees with a ranking of 3 or lower.
If the code above is tough, you can find a detailed solution here: Top 3 Department Salaries.
SQL Question 3: In what circumstances might you choose to denormalize a database?
There are several advantages to normalizing a database, including less redundancy, more flexibility, and better performance.
-
Less Redundancy: Normalization reduces redundancy by breaking down a larger, more general table into smaller, more specific tables. This reduces the amount of data that needs to be accessed for queries.
-
More Flexibility: Normalization makes it easier to modify the structure of the database, as there is less redundancy, so it allows you to make changes to one table without affecting others. This makes it easier to adapt the database to changing business needs (a very real reality at KeyBank!)
-
Better Performance: Normalization can improve the performance of the database by reducing the amount of data that needs to be stored and accessed in a single table. This can result in faster query times and better overall performance.
SQL Question 4: Running Total of Deposits and Withdrawals
A KeyBank branch keeps a log of all deposits and withdrawals. Given a table that has columns for , , , (either 'deposit' or 'withdrawal'), and . Write a SQL query to generate a list with each account and date pairing, where the deposit and withdrawal amounts are summed up to that point in time (running total).
You need to use a SQL window function to accomplish this.
Example Input:
| transaction_id | account_id | transaction_date | transaction_type | amount |
|---|---|---|---|---|
| 101 | 123 | 2021-07-01 | deposit | 150.00 |
| 102 | 123 | 2021-07-02 | withdrawal | 50.00 |
| 103 | 456 | 2021-07-02 | deposit | 200.00 |
| 104 | 123 | 2021-07-07 | deposit | 500.00 |
| 105 | 456 | 2021-07-09 | withdrawal | 100.00 |
Example Output:
| account_id | transaction_date | deposit_total | withdrawal_total |
|---|---|---|---|
| 123 | 2021-07-01 | 150.00 | 0.00 |
| 123 | 2021-07-02 | 150.00 | 50.00 |
| 456 | 2021-07-02 | 200.00 | 0.00 |
| 123 | 2021-07-07 | 650.00 | 50.00 |
| 456 | 2021-07-09 | 200.00 | 100.00 |
Answer:
In this SQL query, we use a window function SUM() with a FILTER clause to add together the amounts of 'deposit' and 'withdrawal' up to each date for each account_id. ORDER BY is used inside the window function to organize the summations in the correct date order for each account. We also ordered the result by account_id and transaction_date for easy viewing.
Pro Tip: Window functions are a frequent SQL interview topic, so practice all the window function problems on DataLemur
Check out the KeyBank career page and sew what role might be the best for for you!
SQL Question 5: Can you explain what SQL constraints are, and why they are useful?
Constraints are just rules your DBMS has to follow when updating/inserting/deleting data.
Say you had a table of KeyBank products and a table of KeyBank customers. Here's some example SQL constraints you'd use:
NOT NULL: This constraint could be used to ensure that certain columns in the product and customer tables, such as the product name and customer email address, cannot contain NULL values.
UNIQUE: This constraint could be used to ensure that the product IDs and customer IDs are unique. This would prevent duplicate entries in the respective tables.
PRIMARY KEY: This constraint could be used to combine the NOT NULL and UNIQUE constraints to create a primary key for each table. The product ID or customer ID could serve as the primary key.
FOREIGN KEY: This constraint could be used to establish relationships between the KeyBank product and customer tables. For example, you could use a foreign key to link the customer ID in the customer table to the customer ID in the product table to track which products each customer has purchased.
CHECK: This constraint could be used to ensure that certain data meets specific conditions. For example, you could use a CHECK constraint to ensure that KeyBank product prices are always positive numbers.
DEFAULT: This constraint could be used to specify default values for certain columns. For example, you could use a DEFAULT constraint to set the customer registration date to the current date if no value is provided when a new customer is added to the database.
SQL Question 6: Determine the total amount deposited per customer
You are given a database that contains information about KeyBank's customers and their banking transactions. Model this scenario in SQL.
Your manager has asked you to come up with a query that would return the total amount deposited per customer, in descending order, for the last 90 days (assuming today's date is '2023-01-01').
Sample Input:
| customer_id | first_name | last_name |
|---|---|---|
| 5678 | John | Doe |
| 8765 | Jane | Smith |
| 1234 | Mary | Johnson |
| 2345 | James | Brown |
Sample Input:
| transaction_id | customer_id | transaction_date | type | amount |
|---|---|---|---|---|
| 1111 | 5678 | 2022-12-20 | deposit | 2000 |
| 2222 | 8765 | 2022-11-25 | deposit | 3000 |
| 3333 | 1234 | 2022-10-15 | deposit | 4000 |
| 4444 | 2345 | 2022-10-10 | withdrawal | 1000 |
| 5555 | 5678 | 2022-12-25 | deposit | 1000 |
| 6666 | 1234 | 2022-10-30 | deposit | 1000 |
Answer:
This PostgreSQL query joins the and tables on , then filters out transactions from the past 90 days that are deposits. It then groups the results by (which creates groups of transactions per customer) and calculates the sum of for each group, resulting in the total amount deposited by each customer. Finally, it orders the results in descending order by .
SQL Question 7: What's the difference between a one-to-one vs. a one-to-many relationship between two entities? Give examples.
In database schema design, a one-to-one relationship between two entities is where each entity is associated with only one instance of the other entity. For example, the relationship between a car and a license plate is one-to-one, because each car can only have one licensce plate, and each licensce plate belongs to exactly one car.
On the other hand, a one-to-many relationship is where one entity can be associated with multiple instances of the 2nd entity. For example, a teacher can teach multiple classes, but each class is associated with only one teacher.
SQL Question 8: Filter KeyBank Customer Database
Given the tables and , write an SQL query to retrieve a list of customers who have made transactions worth more than 1000 dollars in the state of New York (state code 'NY') during the year 2021.
Example Input:
| customer_id | first_name | last_name | state_code |
|---|---|---|---|
| 123 | John | Doe | NY |
| 265 | Jane | Smith | OH |
| 362 | Mary | Johnson | NY |
| 192 | James | Brown | PA |
| 981 | Patricia | Garcia | NY |
Example Input:
| transaction_id | customer_id | transaction_date | amount |
|---|---|---|---|
| 6171 | 123 | 01/10/2021 00:00:00 | 1050 |
| 7802 | 265 | 06/20/2021 00:00:00 | 500 |
| 5293 | 362 | 08/12/2021 00:00:00 | 1500 |
| 6352 | 192 | 09/15/2021 00:00:00 | 800 |
| 4517 | 981 | 10/22/2021 00:00:00 | 1200 |
Answer:
Here's a PostgreSQL query that answers the question:
This query first joins the table with the table on the column. Then it sets the conditions for the query: the state code must be 'NY', the transaction amount must be over 1000, and the year of the transaction must be 2021. The "EXTRACT" function is used to get the year from the timestamp. Finally, it retrieves the list of customers who satisfy all these conditions.
SQL Question 9: Analyzing Click-through Rates for Digital Marketing Campaigns at KeyBank
KeyBank is interested in understanding the effectiveness of their digital marketing campaigns. Specifically, they want to know the click-through rates of the advertisements they send out to potential customers. Each advertisement, when clicked, redirects the user to KeyBank's app or website. KeyBank tracked the click activity of its users for a month and compiled the data into two tables.
The first table, , stores information about each ad that was sent out. The is a unique identifier for each ad, identifies the recipient of the ad, and indicates when the ad was sent.
Example Input
| ad_id | user_id | timestamp |
|---|---|---|
| 1001 | 2345 | 2022-09-01 08:00:00 |
| 1002 | 2346 | 2022-09-01 08:00:05 |
| 1003 | 2347 | 2022-09-01 08:00:10 |
| 1004 | 2348 | 2022-09-01 08:00:15 |
The second table, , records information about whether a user clicked on an ad and when. Each row in the table corresponds to a single click by a user; the and map to the individual and ad clicked, respectively, and records the time of the click.
Example Input
| user_id | ad_id | click_timestamp |
|---|---|---|
| 2345 | 1001 | 2022-09-01 08:01:00 |
| 2346 | 1002 | 2022-09-01 08:05:00 |
| 2346 | 1002 | 2022-09-01 08:05:30 |
Based on this, write a PostgreSQL query to calculate the click-through rate for KeyBank's ad campaigns, defined as the number of unique clicks an ad receives divided by the number of times the ad was sent out (the number of impressions).
Answer:
This query first joins the and tables on both and to get a dataset that includes all ads and whether they were clicked. It calculates the click-through rate by dividing the number of unique clicks a given ad receives (calculated via ) by the number of times the ad was sent out (calculated via ). The result is a table with each and its corresponding click-through rate.
To solve a similar SQL interview question on DataLemur's free online SQL coding environment, solve this Facebook SQL Interview question:
Preparing For The KeyBank SQL Interview
The key to acing a KeyBank SQL interview is to practice, practice, and then practice some more!
In addition to solving the above KeyBank SQL interview questions, you should also solve the 200+ SQL Interview Questions on DataLemur which come from companies like Facebook, Google and unicorn tech startups.
Each exercise has multiple hints, detailed solutions and best of all, there is an interactive coding environment so you can right online code up your query and have it graded.
To prep for the KeyBank SQL interview it is also a great idea to practice interview questions from other banking & finanacial services companies like:
In case your SQL foundations are weak, forget about diving straight into solving questions – refresh your SQL knowledge with this SQL tutorial for Data Scientists & Analysts.
This tutorial covers SQL topics like joining a table to itself and LEAD window function – both of these show up routinely during SQL job interviews at KeyBank.
KeyCorp Data Science Interview Tips
What Do KeyBank Data Science Interviews Cover?
Besides SQL interview questions, the other topics tested in the KeyBank Data Science Interview include:
- Statistics & AB Testing Questions
- Coding Questions in Python or R
- Analytics and Product-Metrics Questions
- ML Interview Questions
- Behavioral Based Interview Questions
How To Prepare for KeyBank Data Science Interviews?
To prepare for KeyBank Data Science interviews read the book Ace the Data Science Interview because it's got:
- 201 interview questions taken from tech companies like Netflix, Google, & Airbnb
- a refresher covering Python, SQL & ML
- over 1000+ reviews on Amazon & 4.5-star rating