Back to questions
Cards Issued Difference [JPMorgan Chase SQL Interview Question]
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 |
|---|---|
| issue_month | integer |
| issue_year | integer |
| card_name | string |
| issued_amount | 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 |
Explanation:
Chase Freedom Flex's best month was 70k cards issued and the worst month was 55k cards, so the difference is 15k cards.
Chase Sapphire Reserve’s best month was 180k cards issued and the worst month was 170k cards, so the difference is 10k cards.
The dataset you are querying against may have different input & output - this is just an example!
Solution
To find the difference between the best and worst performing months in card issuance, you can use the and functions which you can have a read here.
Apply the functions on the column, and simply calculate the difference between the two. As we are asked for the difference between both cards, it is important to group the results by the card name.
Don't forget to order the dataset according to the biggest difference!
PostgreSQL 14