8 JPMorgan Chase SQL Interview Questions (Updated 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.
To help you ace the JPMorgan Chase SQL interview, we've collected 8 JPMorgan Chase SQL interview questions in this article.
8 JPMorgan Chase SQL Interview Questions
SQL Question 1: Cards Issued Difference
Your team at JPMorgan Chase is preparing to launch a new credit card, and to gain some insights, you're analyzing how many credit cards were issued each month.
Write a query that outputs the name of each credit card and the difference in the number of issued cards between the month with the highest issuance cards and the lowest issuance. Arrange the results based on the largest disparity.
Table:
| Column Name | Type |
|---|---|
| card_name | string |
| issued_amount | integer |
| issue_month | integer |
| issue_year | integer |
Example Input:
| card_name | issued_amount | issue_month | issue_year |
|---|---|---|---|
| Chase Freedom Flex | 55000 | 1 | 2021 |
| Chase Freedom Flex | 60000 | 2 | 2021 |
| Chase Freedom Flex | 65000 | 3 | 2021 |
| Chase Freedom Flex | 70000 | 4 | 2021 |
| Chase Sapphire Reserve | 170000 | 1 | 2021 |
| Chase Sapphire Reserve | 175000 | 2 | 2021 |
| Chase Sapphire Reserve | 180000 | 3 | 2021 |
Example Output:
| card_name | difference |
|---|---|
| Chase Freedom Flex | 15000 |
| Chase Sapphire Reserve | 10000 |
Answer:
Solve this JP Morgan Chase SQL question for FREE on our interactive coding environment.
SQL Question 2: Card Launch Success
Your team at JPMorgan Chase is soon launching a new credit card. You are asked to estimate how many cards you'll issue in the first month.
Before you can answer this question, you want to first get some perspective on how well new credit card launches typically do in their first month.
Write a query that outputs the name of the credit card, and how many cards were issued in its launch month. The launch month is the earliest record in the table for a given card. Order the results starting from the biggest issued amount.
Table:
| Column Name | Type |
|---|---|
| issue_month | integer |
| issue_year | integer |
| card_name | string |
| issued_amount | integer |
Example Input:
| issue_month | issue_year | card_name | issued_amount |
|---|---|---|---|
| 1 | 2021 | Chase Sapphire Reserve | 170000 |
| 2 | 2021 | Chase Sapphire Reserve | 175000 |
| 3 | 2021 | Chase Sapphire Reserve | 180000 |
| 3 | 2021 | Chase Freedom Flex | 65000 |
| 4 | 2021 | Chase Freedom Flex | 70000 |
Example Output:
| card_name | issued_amount |
|---|---|
| Chase Sapphire Reserve | 170000 |
| Chase Freedom Flex | 65000 |
Answer:
You can find a step-by-step solution with hints here: Card Launch Success.
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_id | account_name | account_open_date |
|---|---|---|
| 5551 | John Doe | 01/05/2020 |
| 6625 | Jane Smith | 06/18/2021 |
| 7142 | Peter Parker | 12/07/2019 |
| 8883 | Tony Stark | 12/15/2018 |
Example Input:
| transaction_id | account_id | transaction_date | transaction_amount |
|---|---|---|---|
| 1011 | 5551 | 09/02/2022 | 200.00 |
| 2022 | 6625 | 10/01/2022 | 350.00 |
| 3033 | 8883 | 08/01/2022 | 1500.00 |
| 4044 | 5551 | 10/02/2022 | 100.00 |
| ... | ... | ... | ... |
| 20235 | 8883 | 10/10/2022 | 2000.00 |
Write a SQL Query to filter down and bring out these two categories of account from the dataset.
Answer:
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_id | branch_id | customer_id | transaction_amount | transaction_date |
|---|---|---|---|---|
| 1 | 1001 | 2001 | $6000 | 01/01/2023 |
| 2 | 1002 | 2002 | $7000 | 01/02/2023 |
| 3 | 1002 | 2003 | $8000 | 01/03/2023 |
| 4 | 1003 | 2004 | $9000 | 01/04/2023 |
| 5 | 1001 | 2005 | $10000 | 01/05/2023 |
Example Output:
| branch_id | avg_transaction_amount |
|---|---|
| 1001 | $8000.00 |
| 1002 | $7500.00 |
| 1003 | $9000.00 |
Answer:
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_id | first_name | last_name | phone | city | |
|---|---|---|---|---|---|
| 1 | Andrew | Johnson | andrewj@email.com | 1383754928 | New York |
| 2 | Beth | Davis | bethd@email.com | 1435874590 | California |
| 3 | Aaron | Morgan | aaronm@email.com | 1562749392 | New York |
| 4 | Chris | White | chrisw@email.com | 1698432075 | Texas |
| 5 | Alex | Russell | alexr@email.com | 1526374980 | New York |
The SQL query to obtain the required information:
Answer:
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:
- Probability & Stats Questions
- Coding Questions in Python or R
- Analytics and Product-Metrics Questions
- ML Interview Questions
- Behavioral Based Interview Questions
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