Back to questions
Your team at JPMorgan Chase is soon launching a new credit card. You are asked to estimate how many cards you'll issue in the first month.
Before you can answer this question, you want to first get some perspective on how well new credit card launches typically do in their first month.
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 table for a given card. Order the results starting from the biggest issued amount.
|1||2021||Chase Sapphire Reserve||170000|
|2||2021||Chase Sapphire Reserve||175000|
|3||2021||Chase Sapphire Reserve||180000|
|3||2021||Chase Freedom Flex||65000|
|4||2021||Chase Freedom Flex||70000|
|Chase Sapphire Reserve||170000|
|Chase Freedom Flex||65000|
Chase Sapphire Reserve card was launched on 1/2021 with an issued amount of 170,000 cards and the Chase Freedom Flex card was launched on 3/2021 with an issued amount of 65,000 cards.
The dataset you are querying against may have different input & output - this is just an example!
Hold on.. not so fast! If we would take the minimums for the year and month columns separately, the output wouldn't be accurate.
For example in the table below, the launch month for the Chase Sapphire Reserve card could be wrongly interpreted as 1/2021 although it was launched earlier on 11/2020.
|Chase Sapphire Reserve||150000||11||2020|
|Chase Sapphire Reserve||160000||12||2020|
|Chase Sapphire Reserve||170000||1||2021|
|Chase Sapphire Reserve||175000||2||2021|
|Chase Sapphire Reserve||180000||3||2021|
The following query shows the creation of the dates using . As the table has no date column, we use as the first day of the month.
Now we are ready to use the window function to obtain the launch dates. As the launch dates are different for both cards, it is important to partition by the .
For the next step, simply create a CTE from the previous query and select the records where the is equal to the using comparison operators. Finally, order the results from the biggest to the lowest issued amount.