10 First Horizon SQL Interview Questions (Updated 2024)
Data Analysts & Data Scientists at First Horizon write SQL queries as a big part of their job. They use SQL for analyzing financial datasets for insights and managing customer data for personalized banking services. That's the reason behind why First Horizon asks interviewees SQL coding interview questions.
As such, to help prep you for the First Horizon SQL interview, here's 10 First Horizon SQL interview questions in this article.
10 First Horizon SQL Interview Questions
SQL Question 1: Calculate Monthly Average Customer Rating for Each Product
First Horizon is interested in understanding the customer satisfaction for each of their products on a monthly basis. They gather customer feedback in the form of star ratings ranging from 1 to 5.
You are given a table which contains customer reviews for different products. The columns in the table are as follows:
- (integer): unique id of each review
- (integer): unique id of each user
- (date): the date on which the review was submitted
- (integer): unique id of each product
- (integer): the number of stars given in the review (1 being the lowest and 5 the highest)
Your task is to write a SQL query that calculates the average star rating for each product on a monthly basis.
Example Input:
| review_id | user_id | submit_date | product_id | stars |
|---|---|---|---|---|
| 1 | 123 | 2022-01-15 | 1001 | 4 |
| 2 | 124 | 2022-02-10 | 1002 | 3 |
| 3 | 125 | 2022-02-15 | 1001 | 5 |
| 4 | 126 | 2022-03-20 | 1002 | 2 |
| 5 | 123 | 2022-03-25 | 1001 | 1 |
Answer:
This query groups reviews by month and product, then calculates the average star rating for each group. It finally sorts the result by month in ascending order and average star rating in descending order to bring the products with higher average rating to the top for each month.
To practice a similar window function interview problem which uses RANK() on DataLemur's free online SQL coding environment, solve this Amazon SQL question asked in a BI Engineer interview:
SQL Question 2: 2nd Highest Salary
Assume you had a table of First Horizon employee salary data. Write a SQL query to find the 2nd highest salary among all employees.
First Horizon Example Input:
| employee_id | salary |
|---|---|
| 1 | 2500 |
| 2 | 800 |
| 3 | 1000 |
| 4 | 1200 |
Example Output:
| second_highest_salary |
|---|
| 1200 |
Code your solution to this question directly within the browser on DataLemur:
Answer:
You can find a step-by-step solution here: 2nd Highest Salary.
Check out the First Horizon career page and see how they use SQL in their day-to-day-operations.
SQL Question 3: How does differ from just ?
is used to combine the output of multiple statements into one big result!
Suppose you were a Data Analyst at First Horizon working on a Sales Analytics project, and had data on sales leads from both the company's Salesforce CRM and it's legacy Hubspot CRM. To write a query to analyze leads created before 2023 started, across both CRMs, you could use in the following way:
filters out duplicates, so each email/job title/company only shows up once (even if it's in both Salesforce and HubSpot CRMs).
On the other hand, the operator does NOT filter out duplicates, so if a lead shows up in both CRMs, it'll be listed twice in the output of .
SQL Question 4: Customer Transaction Analysis
First Horizon is a bank, and they want to analyze customer transactions. Each transaction has a type (deposit, withdrawal, transfer), amount, date of transaction, and the customer who made it. Customers have an id, name, and the date they joined First Horizon.
Create a database model for this situation, and then write a query for the following question:
"What is the total amount of deposits, withdrawals, and transfers by customer and month, for customers who joined First Horizon in 2022?"
Example Input:
Example Input:
Answer:
This query joins the customers and transactions tables on the customer_id field. The clause filters out customers who joined in 2022. The clause groups by customer, month, and transaction type. Finally, the function is used to sum the amount of each transaction type for each customer by month. The results are then ordered by customer name and month.
SQL Question 5: How is a foreign key different from a primary key in a database?
To explain the difference between a primary key and foreign key, let's inspect employee data from First Horizon's HR database:
:
+------------+------------+------------+------------+ | employee_id| first_name | last_name | manager_id | +------------+------------+------------+------------+ | 1 | Aubrey | Graham | 3 | | 2 | Marshal | Mathers | 3 | | 3 | Dwayne | Carter | 4 | | 4 | Shawn | Carter | | +------------+------------+------------+------------+
In this table, could be a primary key. It is unique for each row in the table and cannot contain null values.
could be a foreign key. It references the of the manager of each employee in the table, establishing a relationship between the employees and their managers. This foreign key allows you to easily query the table to find out who a specific employee's manager is, or to find out which employees report to a specific manager.
It is also possible for a table to have multiple foreign keys that reference different primary keys in other tables. For example, the table could have additional foreign keys for the of the department where each employee works, and the l of the location where each employee is based.
SQL Question 6: Filter Customers Based on Account Status, Balance, and Account Creation Date
Given the customer's table of , write an SQL query to retrieve the details of customers who have an active account (), whose account balance is more than $20,000 (), and who created their account after January 1, 2020 ().
Example Input:
| customer_id | account_status | account_balance | account_creation_date |
|---|---|---|---|
| 1 | Active | 25000 | '2020-02-10' |
| 2 | Inactive | 30000 | '2019-05-15' |
| 3 | Suspended | 15000 | '2020-06-20' |
| 4 | Active | 19500 | '2018-03-25' |
| 5 | Active | 50000 | '2021-01-01' |
Answer:
This PostgreSQL query will return customer details whose account is active, their balance is more than $20,000, and their account was created after January 1, 2020. In our given sample data, rows 1 and 5 fulfill these criteria, so our result will be:
Example Output:
| customer_id | account_status | account_balance | account_creation_date |
|---|---|---|---|
| 1 | Active | 25000 | '2020-02-10' |
| 5 | Active | 50000 | '2021-01-01' |
SQL Question 7: What does it mean for a DBMS to enforce atomicity, consistency, isolation, and durability?
A DBMS (database management system), in order to ensure transactions are relaible and correct, tries to mantain the following ACID properties: Atomicity, Consistency, Isolation, and Durability
Here is what each of the ACID properties stands for:
Atomicity: ensures that a transaction is either completed in its entirety, or not completed at all. If a transaction fails halfway, the database does a rollback on the commit.
Consistency: ensures that a transaction will only be completed if adheres to the constraints defined in the database of the DB.
Isolation: ensures that concurrent transactions are isolated from each one another, so that the changes made by one transaction cannot be seen by a 2nd transaction until the 1st transaction is done.
**Durability: ** ensures that once a transaction has been committed, the database permanently stores the results in the DB.
As you can see, it's pretty important for First Horizon's data systems to be ACID compliant, else they'll be a big problem for their customers!
SQL Question 8: Calculate the Average Transaction Amount
As a data analyst at First Horizon bank, you have been tasked with a responsibility to find out the average transaction amount of customers to understand the financial behavior. Can you write a SQL query that will calculate the average transaction amount per customer for each banking product?
Example Input:
| transaction_id | customer_id | transaction_date | product_id | transaction_amount |
|---|---|---|---|---|
| 1 | 123 | 03/15/2022 | 101 | 200.00 |
| 2 | 123 | 04/01/2022 | 101 | 150.00 |
| 3 | 456 | 03/18/2022 | 102 | 500.00 |
| 4 | 789 | 04/20/2022 | 101 | 350.00 |
| 5 | 123 | 03/25/2022 | 102 | 300.00 |
Example Output:
| customer_id | product_id | avg_transaction_amount |
|---|---|---|
| 123 | 101 | 175.00 |
| 123 | 102 | 300.00 |
| 456 | 102 | 500.00 |
| 789 | 101 | 350.00 |
Answer:
This SQL query groups the transactions by each customer and for each product they use, then calculates the average transaction amount within these groups. The function in SQL calculates the average value of a specific column over the group of rows. In this case, it gives us the average transaction amount per customer for each product.
To practice a very similar question try this interactive Stripe Repeated Payments Question which is similar for analyzing transaction data or this Uber User's Third Transaction Question which is similar for focusing on specific customer transactions.
SQL Question 9: Find the Total Loan Amount for Each Loan Type per Branch
First Horizon offers different types of loans - personal, mortgage, car, etc. - from numerous branches. The CEO wants to know how much loan amount has been disbursed per loan type for every branch in the last calendar year.
Provide the , and in the output.
Table: Example Input:
| loan_id | branch_id | loan_type | customer_id | loan_amount | loan_given_date |
|---|---|---|---|---|---|
| 1001 | 'A' | 'car' | 3456 | 10000 | 01/10/2021 |
| 1002 | 'A' | 'home' | 3621 | 20000 | 02/11/2021 |
| 1003 | 'B' | 'car' | 4521 | 15000 | 31/12/2021 |
| 1004 | 'A' | 'car' | 2219 | 18000 | 23/12/2021 |
| 1005 | 'B' | 'home' | 1298 | 25000 | 15/04/2021 |
| 1006 | 'B' | 'personal' | 1386 | 8000 | 07/06/2021 |
Example Output:
| branch_id | loan_type | total_amount |
|---|---|---|
| 'A' | 'car' | 28000 |
| 'A' | 'home' | 20000 |
| 'B' | 'car' | 15000 |
| 'B' | 'home' | 25000 |
| 'B' | 'personal' | 8000 |
Answer:
This query groups the data by branch id and loan type, and then uses the SUM aggregate function to calculate the total loan amount given for each type of loan in each branch in the year 2021.
SQL Question 10: What does do?
The COALESCE() function returns the first non-NULL value from a list of values. This function is often used to replace a NULL with some default value, so that you can then take a or of some column without NULLs messing things up.
For example, suppose you ran a customer satisfaction survey for First Horizon and had statements like "I'd buy from First Horizon again". In the survey, customers would then answer how strongly they agreed with a statement on a scale of 1 to 5 (strongly disagree, disagree, neutral, agreee, strongly agree).
Because attention spans are short, many customers skipped many of the questions, and thus our survey data might be filled with NULLs:
| customer_id | question_id | agree_scale |
|---|---|---|
| 101 | 1 | 4 |
| 101 | 2 | 5 |
| 202 | 1 | 4 |
| 202 | 2 | NULL |
| 303 | 1 | 5 |
| 303 | 2 | NULL |
Before doing further analytics on this customer survey data, you could replace the NULLs in the column with the value of (because that corresponds to the default 'neutral' answer) using the function:
This would result in the following:
| customer_id | question_id | agree_scale |
|---|---|---|
| 101 | 1 | 4 |
| 101 | 2 | 5 |
| 202 | 1 | 4 |
| 202 | 2 | 3 |
| 303 | 1 | 5 |
| 303 | 2 | 3 |
First Horizon SQL Interview Tips
Assuming that you've already got basic SQL skills, the next best tip we have to prepare for the First Horizon SQL interview is to solve as many practice SQL interview questions as you can!
In addition to solving the earlier First Horizon SQL interview questions, you should also solve the 200+ DataLemur interview questions which come from companies like Amazon, Microsoft, Meta, and smaller tech companies.
Each exercise has multiple hints, full answers and most importantly, there is an interactive coding environment so you can right in the browser run your SQL query and have it executed.
To prep for the First Horizon SQL interview you can also be wise to practice SQL questions from other banking & finanacial services companies like:
However, if your SQL foundations are weak, don't worry about diving straight into solving questions – strengthen your SQL foundations with this SQL tutorial for Data Scientists & Analysts.
This tutorial covers topics including transforming strings with CONCAT()/LOWER()/TRIM() and functions like SUM()/COUNT()/AVG() – both of which pop up routinely in SQL job interviews at First Horizon.
First Horizon Data Science Interview Tips
What Do First Horizon Data Science Interviews Cover?
In addition to SQL interview questions, the other types of problems tested in the First Horizon Data Science Interview are:
- Statistics and Probability Questions
- Python Pandas or R Coding Questions
- Product Analytics Questions
- ML Modelling Questions
- Behavioral Based Interview Questions
How To Prepare for First Horizon Data Science Interviews?
To prepare for First Horizon Data Science interviews read the book Ace the Data Science Interview because it's got:
- 201 interview questions sourced from FAANG tech companies
- a refresher on Python, SQL & ML
- over 1000+ reviews on Amazon & 4.5-star rating