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 monthly_cards_issued table for a given card. Order
the results starting from the biggest issued amount.
table name: monthly_cards_issued
Solution:
go
with cte as
(
select *, rank() over (partition by card_name order by issue_year,issue_month) as rnk
from monthly_cards_issued
)
select card_name,issued_amount from cte where rnk =1
order by issued_amount desc
Output:
SQL Script:
CREATE TABLE [dbo].[monthly_cards_issued](
[issue_month]
[int] NULL,
[issue_year]
[int] NULL,
[card_name]
[nvarchar](50) NULL,
[issued_amount]
[int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[monthly_cards_issued] ([issue_month], [issue_year], [card_name],
[issued_amount]) VALUES (1, 2021, N'Chase Sapphire
Reserve',
170000)
GO
INSERT [dbo].[monthly_cards_issued] ([issue_month], [issue_year], [card_name],
[issued_amount]) VALUES (2, 2021, N'Chase Sapphire
Reserve',
175000)
GO
INSERT [dbo].[monthly_cards_issued] ([issue_month], [issue_year], [card_name],
[issued_amount]) VALUES (3, 2021, N'Chase Sapphire
Reserve',
180000)
GO
INSERT [dbo].[monthly_cards_issued] ([issue_month], [issue_year], [card_name],
[issued_amount]) VALUES (3, 2021, N'Chase Freedom
Flex', 65000)
GO
INSERT [dbo].[monthly_cards_issued] ([issue_month], [issue_year], [card_name],
[issued_amount]) VALUES (4, 2021, N'Chase Freedom
Flex', 70000)
GO