# 8 Citizens Bank SQL Interview Questions (Updated 2024)

Updated on

June 30, 2024

Data Science, Data Engineering and Data Analytics employees at Citizens Bank write use SQL for analyzing banking transactions and predicting customer behavior based on their financial interactions. For this reason Citizens Bank asks prospective hires SQL interview problems.

So, to help you prepare, here’s 8 Citizens Financial Group SQL interview questions – can you solve them?

## 8 Citizens Financial Group SQL Interview Questions

### SQL Question 1: Calculate Average Loan Rate by Month

As a data analyst at Citizens Bank, you are tasked with analyzing loan data. Write a SQL query that calculates the average loan rate per month for each type of loan product.

Consider the "loans" table for this scenario is formatted as follows:

##### Example Input:
loan_idcustomer_idloan_opened_dateloan_product_idloan_rate
10235062021-01-1532404.5
23479012021-02-0998123.7
54321892021-02-2532405.0
67218042021-03-1398124.2
73194312021-03-2932404.7

The output should show the month, loan product id, and the average loan rate for that product in that month.

##### Example Output:
monthloan_product_idavg_loan_rate
0132404.5
0232405.0
0298123.7
0332404.7
0398124.2

Here is the SQL Query for PostgreSQL:

The above query will calculate the average loan rate by each type of loan product for each month in our dataset. We first convert 'loan_opened_date' into month format, and then group (using ) by 'loan_product_id' and 'month' to calculate the average loan rate. The clause is the window function being implemented in this query. Window functions perform calculation across a set of rows that are related to the current row.

To solve a related window function SQL problem on DataLemur's free interactive SQL code editor, solve this Google SQL Interview Question:

### SQL Question 2: 2nd Largest Salary

Given a table of Citizens Bank employee salary information, write a SQL query to find the 2nd highest salary amongst all the employees.

#### Citizens Bank Example Input:

employee_idsalary
12500
2800
31000
41200

#### Example Output:

second_highest_salary
1200

Write a SQL query for this interview question directly within the browser on DataLemur:

You can find a step-by-step solution with hints here: 2nd Highest Salary.

### SQL Question 3: What does the keyword do?

The keyword added to a statement can be used to get records without duplicates.

For example, say you had a table of Citizens Bank customers:

namecity
AkashSF
BrittanyNYC
CarlosNYC
DiegoSeattle
EvaSF
FayeSeattle

Suppose you wanted to figure out which cities the customers lived in, but didn't want duplicate results, you could write a query like this:

city
SF
NYC
Seattle

### SQL Question 4: Average Loan Amount by Loan Type for Citizens Bank

As a data analyst for Citizens Bank, you are asked to find out the average loan amount based on different loan types. The bank offers different loan types such as home loans, auto loans, personal loans, etc. Your manager wants this information to understand the average amount customers usually borrow for each type of loan.

Provided below is a sample table of the loans:

##### Example Input:
loan_idcustomer_idloan_typeloan_amountloan_date
1018745Home50000001/22/2021
1021842Auto2000003/15/2021
1037841Personal1500004/18/2021
1049271Home35000005/25/2021
1055461Auto3000006/19/2021

Your task is to write a SQL query that generates the average loan amount for each loan type and sorts the average loan amount in descending order.

This query first groups the loans by their type using the GROUP BY clause. It then calculates the average loan amount for each loan type using the AVG function. The result is then sorted in descending order by the average loan amount using the ORDER BY clause. The output of this query enables the bank to understand the average amount of money borrowed by their customers based on each loan type. This helps them to understand the preference of their customers based on loan amount for each loan type.

To practice a very similar question try this interactive Amazon Highest-Grossing Items Question which is similar for grouping and finding top items or this Stripe Repeated Payments Question which is similar for handling and analyzing transaction data.

Take a look at the Citizens Bank career page and see what role might be the best fit for you!

### SQL Question 5: What's denormalization, and when does it make sense to do it?

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 Citizens 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: Find Customers in specific city

For Citizens Bank, suppose you have been given access to the database of customer records. Your task is to find all customers who live in the city of 'Boston'.

The table has the following structure:

##### Example Input:
customer_idfirst_namelast_namecitystateemail
1JohnDoeBostonMassachusettsjohndoe@example.com
2JaneSmithNew YorkNew Yorkjanesmith@example.com
3SarahBrownBostonMassachusettssarahbrown@example.com
4MarkThomasSan FranciscoCaliforniamarkthomas@example.com
5EmmaWilliamsSeattleWashingtonemmawilliams@example.com

Here is a SQL query that can find all customers in Boston:

You will get all customer records where the "city" column value is "Boston".

##### Example Output:
customer_idfirst_namelast_namecitystateemail
1JohnDoeBostonMassachusettsjohndoe@example.com
3SarahBrownBostonMassachusettssarahbrown@example.com

The result shows the customer details that match the specific condition 'Boston' in the city field.

### SQL Question 7: How does the constraint function, and in what scenarios might it be useful?

The CHECK constraint is used to set a rule for the data in a column. If a row is inserted or updated and the data in the column does not follow the rule specified by the CHECK constraint, the operation will be unsuccessful.The CHECK constraint is often used in conjunction with other constraints, such as NOT NULL or UNIQUE.

You might consider implementing the CHECK constraint in your database if you want to ensure that certain data meets specific conditions. This can be helpful for maintaining the quality and reliability of your data.

For example, you might use a CHECK constraint to ensure that a column contains only positive numbers, or that a date is within a certain range.

### SQL Question 8: Analyzing Customer Transaction Behavior

As a data analyst at Citizens Bank, analyze customer transaction behavior by writing a SQL query to find the total number of Checking and Savings accounts per customer along with their total transactions. Use a SQL JOIN operation to join the customers table with the accounts table and the transactions table.

##### Example Input:
customer_idfirst_namelast_name
1JohnDoe
2JaneSmith
3MaryJohnson
##### Example Input:
account_idcustomer_idaccount_type
11Checking
21Savings
32Checking
43Savings
##### Example Input:
account_idtransaction_idtransaction_amt
11100
22200
23300
34400
45500
##### Expected output:
customer_idfirst_namelast_nameaccount_typetotal_transactions
1JohnDoeChecking100
1JohnDoeSavings500
2JaneSmithChecking400
3MaryJohnsonSavings500

This SQL query operates by first joining the customers table with the accounts table based on the customer ID. It then joins the result with the transactions table based on the account ID. It subsequently groups the records by customer ID, first name, last name, and account type, while summing the transaction amounts to find the total transactions for each account type per customer.

Because joins come up so often during SQL interviews, take a stab at this Spotify JOIN SQL question:

### Preparing For The Citizens Bank SQL Interview

The key to acing a Citizens Bank SQL interview is to practice, practice, and then practice some more! Besides solving the above Citizens Bank SQL interview questions, you should also solve the 200+ FAANG SQL Questions on DataLemur which come from companies like Netflix, Airbnb, and Amazon.

Each DataLemur SQL question has hints to guide you, step-by-step solutions and best of all, there is an interactive coding environment so you can instantly run your SQL query answer and have it checked.

To prep for the Citizens Bank SQL interview you can also be a great idea to solve interview questions from other banking & finanacial services companies like:

But if your SQL coding skills are weak, forget about jumping right into solving questions – strengthen your SQL foundations with this SQL tutorial for Data Scientists & Analysts.

This tutorial covers things like LEAD window function and Subqueries – both of which show up often in Citizens Bank interviews.

### Citizens Financial Group Data Science Interview Tips

#### What Do Citizens Bank Data Science Interviews Cover?

Beyond writing SQL queries, the other types of problems to practice for the Citizens Bank Data Science Interview include:

#### How To Prepare for Citizens Bank Data Science Interviews?

The best way to prepare for Citizens Bank Data Science interviews is by reading Ace the Data Science Interview. The book's got:

• 201 Interview Questions from companies like Microsoft, Google & Amazon
• A Refresher covering SQL, Product-Sense & ML
• Amazing Reviews (1000+ reviews, 4.5-star rating)