# 8 JPMorgan Chase SQL Interview Questions (Updated 2024)

Updated on

June 30, 2024

At JPMorgan Chase, SQL is crucial for analyzing financial transaction patterns and predicting future investment trends. So, it shouldn't surprise you that JPMorgan Chase almost always asks SQL questions during interviews for Data Analytics, Data Science, and Data Engineering jobs.

## 8 JPMorgan Chase SQL Interview Questions

### SQL Question 1: Calculate Monthly Average Transaction Amount for Each Merchant

You are given a table which consists of all the transactions that took place in JPMorgan Chase. A sale is represented by a positive and a refund is represented by a negative . Write a SQL query to calculate the monthly average transaction for each .

##### Example Output:

This query first converts the to a formatted 'Mon-YYYY' string to represent the month and year. It then groups by this new column as well as the to calculate the average transaction for each month and merchant. The ordering is first by then by to get the output in the desired format.

To practice another window function question on DataLemur's free interactive coding environment, solve this Amazon BI Engineer interview question:

### SQL Question 2: 2nd Highest Salary

Given a table of JPMorgan Chase employee salaries, write a SQL query to find the 2nd highest salary amongst all the employees.

#### JPMorgan Chase Example Input:

employee_idsalary
12500
2800
31000
41200

#### Example Output:

second_highest_salary
1200

Try this question and run your code right in the browser:

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

### SQL Question 3: Can you provide an example of two entities that have a one-to-one relationship, and another example of a one-to-many relationship?

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 4: Customer Account Activity Filter

As a data analyst for JPMorgan Chase, you have been tasked to identify accounts with either abnormally high transactions or dormant status. The brief is as follows:

• High transaction account: An account where the total number of transactions exceeds 100 in the last 30 days.
• Dormant account: An account with no transaction in the past 60 days.

You have the following dataset at your disposal:

##### Example Input:
account_idaccount_nameaccount_open_date
5551John Doe01/05/2020
6625Jane Smith06/18/2021
7142Peter Parker12/07/2019
8883Tony Stark12/15/2018
##### Example Input:
transaction_idaccount_idtransaction_datetransaction_amount
1011555109/02/2022200.00
2022662510/01/2022350.00
3033888308/01/20221500.00
4044555110/02/2022100.00
............
20235888310/10/20222000.00

Write a SQL Query to filter down and bring out these two categories of account from the dataset.

This query first fetches all accounts with more than 100 transactions in the last 30 days using a subquery and joins it with the accounts table. It then combines these results with the accounts that haven't had any transactions in the last 60 days using UNION. UNION ensures we don't have duplicate accounts in case an account falls in both categories.

### SQL Question 5: What's the SQL command do, and can you give an example?

When using , only rows that are identical in both sets will be returned.

For a concrete example, say you were a Data Analyst supporting the Sales Analytics team at JPMorgan Chase, and data on potential sales leads lived in both Salesforce and Hubspot CRMs. To write a query to analyze leads created before 2023 started, that show up in BOTH CRMs, you would use the command:

### SQL Question 6: Calculate The Average Transaction Amount per Branch

The table logs each transaction that takes place at various branches of JPMorgan Chase. Each row records the transaction id (), the branch id (), the customer id (), the transaction amount () and the date of the transaction ().

The interviewee is expected to write a SQL query that calculates the average transaction amount per branch for the year 2023.

#### Example Input:

transaction_idbranch_idcustomer_idtransaction_amounttransaction_date
110012001\$600001/01/2023
210022002\$700001/02/2023
310022003\$800001/03/2023
410032004\$900001/04/2023
510012005\$1000001/05/2023

#### Example Output:

branch_idavg_transaction_amount
1001\$8000.00
1002\$7500.00
1003\$9000.00

The SQL query finds the average transaction amount for each branch for the year 2023. It first filters the transactions for the year 2023 with the WHERE clause, then groups the transactions by branch id using the GROUP BY clause. Finally, it calculates the average transaction amount for each group using the AVG aggregate function.

To practice a very similar question try this interactive Stripe Repeated Payments Question which is similar for dealing with transaction data or this Uber User's Third Transaction Question which is similar for calculating statistics based on transactions.

### SQL Question 7: What's the purpose of the constraint?

A is a field in a table that references the of another table. It creates a link between the two tables and ensures that the data in the field is valid.

Say for example you had sales analytics data from JPMorgan Chase's CRM (customer-relationship management) tool.

The FOREIGN KEY constraint ensures that the data in the field of the "opportunities" table is valid, and prevents the insertion of rows in the table that do not have corresponding entries in the table. It also helps to enforce the relationship between the two tables and can be used to ensure that data is not deleted from the accounts table if there are still references to it in the opportunities.

Read about JPMorgan's Data and Analytics suite and how they utilize it to reach their business goals.

### SQL Question 8: Find Customer Information for a JPMorgan Chase Campaign

JPMorgan Chase wants to launch a marketing campaign for clients who are living in New York and have their names starting with 'A'. For this project, they want to obtain a list that includes the client's id, first name, last name, email and phone number. Please write a SQL query that will help obtain this list from the customer database.

Assuming we have a table with data arranged as follows:

##### Example Input:
customer_idfirst_namelast_nameemailphonecity
1AndrewJohnsonandrewj@email.com1383754928New York
2BethDavisbethd@email.com1435874590California
3AaronMorganaaronm@email.com1562749392New York
4ChrisWhitechrisw@email.com1698432075Texas
5AlexRussellalexr@email.com1526374980New York

The SQL query to obtain the required information:

This query will filter out customers with the first name starting with 'A' and lives in New York as they are the target for the upcoming marketing campaign. The LIKE keyword in SQL is used to search for a specified pattern in a column. 'A%' is a pattern that matches any string that starts with 'A'.

### How To Prepare for the JPMorgan Chase SQL Interview

The best way to prepare for a SQL interview, besides making sure you have strong SQL fundamentals, is to practice a ton of real SQL questions that were asked in recent job interviews. Besides solving the earlier JPMorgan Chase SQL interview questions, you should also solve the 200+ SQL questions from real Data Science & Analytics interviews which come from companies like Google, Microsoft and Silicon Valley startups.

Each problem on DataLemur has hints to guide you, full answers and best of all, there's an interactive coding environment so you can right online code up your SQL query answer and have it checked.

To prep for the JPMorgan Chase SQL interview it is also helpful to practice SQL problems from other banking & finanacial services companies like:

In case your SQL query 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 SQL topics like working with string/text data and how window functions work – both of these come up frequently in SQL job interviews at JPMorgan Chase.

### JPMorgan Chase Data Science Interview Tips

#### What Do JPMorgan Chase Data Science Interviews Cover?

In addition to SQL interview questions, the other question categories to practice for the JPMorgan Chase Data Science Interview are:

#### How To Prepare for JPMorgan Chase Data Science Interviews?

To prepare for JPMorgan Chase Data Science interviews read the book Ace the Data Science Interview because it's got:

• 201 interview questions sourced from tech companies like Netflix, Google, & Airbnb
• a crash course on SQL, Product-Sense & ML
• over 1000+ reviews on Amazon & 4.5-star rating