SQL Series #1: Dense_Rank() Window Function

🔍 Problem Statement

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 monthly_cards_issued table for a given card. Order the results starting from the biggest issued amount.

Dataset

The Multiple Common Table Expression (CTE) Approach

The multiple CTE approach to solve this problem requires several steps, utilizing a series of subqueries and joins. Here's how it looks:

Step 1: Find the minimum issue year for each card

Step 2: Get the associated minimum month for the minimum year

Step 3: Get the issued amount for each card, minimum year, and month, and retrieve the issued amount in descending order

While this approach works, it’s verbose and involves several subqueries and joins, making the code harder to read and maintain.

Window Function Approach

Now, let’s look at how a window function simplifies this process:

With window functions, the query becomes more concise and easier to understand.

We will be using window function called DENSE_RANK(). DENSE_RANK() is used to assign ranks to rows within a partition, ensuring that there are no gaps in the ranking sequence, even when ties occur. This function is particularly useful when you need consistent ranking without skipping numbers, making it ideal for scenarios like leaderboard creation or performance evaluations.

In this case, the DENSE_RANK() function allows to rank each card's records by issue year and month, making it straightforward to identify the launch month.