Objective

Write a SQL query to calculate the variance in monthly card issuance for each credit card.

Table Schema:

• card_issuance

Task Requirements:

• Find the maximum issued amount for each card across all months
• Find the minimum issued amount for each card across all months
• Calculate the difference (max - min) for each card
• Sort results by the difference in descending order
  
  

Output columns: card_name, issued_difference

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

Explanation

Step 1: Understanding the Variance Calculation

We identify the range of issuance volumes for each card by finding the difference between the highest and lowest monthly issuance amounts.

Step 2: Finding Maximum and Minimum Issuance

MAX(issued_amount) - MIN(issued_amount) AS issued_difference

MAX() identifies the highest monthly issuance; MIN() identifies the lowest. Subtracting them gives the range of monthly variance for each card.

Step 3: Grouping by Card

GROUP BY card_name

Ensures max, min, and difference are calculated separately for each credit card product.

Step 4: Ordering the Results

ORDER BY issued_difference DESC

Sorts by difference descending to show cards with the highest issuance variance first.