Card Launch Success - DataLemur

Card Launch Success 문제

출처 : DataLemur Card Launch Success

JPMorgan SQL Interview Question

Q.

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

---

질문

각 신용카드별로 출시 첫 달에 발급된 카드 수(issued_amount)를 구하고, 그 수가 많은 순서대로 정렬하라

내 답안 📕

WITH rank_table AS (
  SELECT *
        , RANK() OVER (PARTITION BY card_name ORDER BY issue_year ASC, issue_month ASC) AS rnk
  FROM monthly_cards_issued
)

SELECT card_name
     , issued_amount
FROM rank_table
WHERE rnk = 1
ORDER BY issued_amount DESC;