In this problem, you’re analyzing credit card issuance data for JPMorgan Chase to identify how many cards were issued each month for different card types. The goal is to find the difference between the maximum and minimum monthly issuance amounts for each credit card and arrange the results based on the largest disparity.

Problem Statement

Given a table named monthly_cards_issued, which contains information about the number of credit cards issued each month for different card types, write a query to output the name of each credit card and the difference between the highest and lowest issuance numbers. The result should be sorted in descending order of this difference.

Table: monthly_cards_issued

Example Input:

Example Output:

Explanation:

• For “Chase Freedom Flex”:
• Highest issuance: 70,000 (April 2021)
• Lowest issuance: 55,000 (January 2021)
• Difference: 70,000 – 55,000 = 15,000
• For “Chase Sapphire Reserve”:
• Highest issuance: 180,000 (March 2021)
• Lowest issuance: 170,000 (January 2021)
• Difference: 180,000 – 170,000 = 10,000

Step-by-Step Solution

1. Calculate Max and Min Issuance: For each card, find the maximum and minimum issuance amounts using aggregation functions.
2. Calculate the Difference: Subtract the minimum issuance from the maximum issuance for each card.
3. Sort the Results: Order the output by the difference in descending order.

SQL Query

Here’s how to write the SQL query to find the differences in issuance amounts:

SELECT 
    card_name, 
    MAX(issued_amount) - MIN(issued_amount) AS difference
FROM 
    monthly_cards_issued
GROUP BY 
    card_name
ORDER BY 
    difference DESC;

Explanation of the Query

1. Grouping and Aggregation (GROUP BY and Aggregation Functions):

• GROUP BY card_name groups the results by each unique credit card name.
• MAX(issued_amount) finds the highest number of cards issued for each card.
• MIN(issued_amount) finds the lowest number of cards issued for each card.
• MAX(issued_amount) - MIN(issued_amount) calculates the difference between the highest and lowest issuance.

1. Sorting the Results (ORDER BY):

• ORDER BY difference DESC sorts the output by the difference in descending order, displaying cards with the largest disparities first.

Conclusion

This query efficiently identifies the variability in credit card issuance for each card type over the months. By using SQL aggregation functions (MAX and MIN) along with GROUP BY, you can extract insightful data patterns, which can be vital for business analysis and strategic planning.

Whether you’re preparing for an interview or analyzing real-world data, this problem helps solidify your understanding of how to use SQL for advanced data aggregation and analysis.