Objective

Write a SQL query to list each credit card along with the number of cards issued in its launch month.

Table Schema:

• monthly_cards_issued

Task Requirements:

• The launch month is the earliest month-year combination recorded for each card
• Retrieve the issued_amount for each card's launch month
• Sort the results by issued_amount in descending order
  
  

Output columns: card_name, issued_amount

WITH ranked AS (
    SELECT
        card_name,
        issued_amount,
        ROW_NUMBER() OVER (
            PARTITION BY card_name ORDER BY issue_year, issue_month
        ) AS rn
    FROM monthly_cards_issued
)
SELECT
    card_name,
    issued_amount
FROM ranked
WHERE rn = 1
ORDER BY issued_amount DESC;

Explanation

Step 1: Ranking Months Chronologically per Card

ROW_NUMBER() OVER (
    PARTITION BY card_name ORDER BY issue_year, issue_month
) AS rn

ROW_NUMBER() assigns a sequential rank to each row within each card's group. PARTITION BY card_name restarts the numbering for every card. ORDER BY issue_year, issue_month sorts chronologically, so the earliest month gets rank 1. Ordering by issue_year first is essential since month 12 of 2021 comes before month 1 of 2022.

Step 2: Filtering for the Launch Month

WHERE rn = 1

This keeps only the row with rank 1 for each card, which is the earliest recorded month (the launch month). Each card produces exactly one row after this filter.

Step 3: Selecting the Output

SELECT
    card_name,
    issued_amount

The card_name identifies the card and issued_amount is the number of cards issued during that launch month. No aggregation is needed since the WHERE rn = 1 filter already narrows each card down to a single row.

Step 4: Sorting by Issued Amount

ORDER BY issued_amount DESC

The results are sorted by issued_amount in descending order, showing the cards with the highest launch month issuance first.