LeetCode 코딩 테스트 - Monthly Transactions I(LV.Medium)

 

 

Table: Transactions

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| country       | varchar |
| state         | enum    |
| amount        | int     |
| trans_date    | date    |
+---------------+---------+
id is the primary key of this table.
The table has information about incoming transactions.
The state column is an enum of type ["approved", "declined"].

 

Write an SQL query to find for each month and country, the number of transactions and their total amount, the number of approved transactions and their total amount.

Return the result table in any order.

The query result format is in the following example.

 

Example 1:

Input: 
Transactions table:
+------+---------+----------+--------+------------+
| id   | country | state    | amount | trans_date |
+------+---------+----------+--------+------------+
| 121  | US      | approved | 1000   | 2018-12-18 |
| 122  | US      | declined | 2000   | 2018-12-19 |
| 123  | US      | approved | 2000   | 2019-01-01 |
| 124  | DE      | approved | 2000   | 2019-01-07 |
+------+---------+----------+--------+------------+
Output: 
+----------+---------+-------------+----------------+--------------------+-----------------------+
| month    | country | trans_count | approved_count | trans_total_amount | approved_total_amount |
+----------+---------+-------------+----------------+--------------------+-----------------------+
| 2018-12  | US      | 2           | 1              | 3000               | 1000                  |
| 2019-01  | US      | 1           | 1              | 2000               | 2000                  |
| 2019-01  | DE      | 1           | 1              | 2000               | 2000                  |
+----------+---------+-------------+----------------+--------------------+-----------------------+

 

테이블: Transactions

| Column Name | Type |

| id | int |

| country | varchar |

| state | enum |

| amount | int |

| trans_date | date |

• id는 기본 키(primary key)입니다.
• 이 테이블은 들어오는 거래에 대한 정보를 포함합니다.
• state 열은 ["approved", "declined"] 값을 가지는 enum 타입입니다.

---

문제 요구사항

각 월(month)과 국가(country)에 대해 다음 정보를 구하는 SQL 쿼리를 작성하세요:

1. 해당 월과 국가의 총 거래 수 (trans_count)
2. 해당 월과 국가의 총 거래 금액 (trans_total_amount)
3. 승인된(approved) 거래의 수 (approved_count)
4. 승인된(approved) 거래의 총 금액 (approved_total_amount)

결과는 어떤 순서로든 반환해도 됩니다.

---

예제

입력 테이블: Transactions

id | country | state | amount | trans_date

121	US	approved	1000	2018-12-18
122	US	declined	2000	2018-12-19
123	US	approved	2000	2019-01-01
124	DE	approved	2000	2019-01-07

기대 출력

month | country | trans_count | approved_count | trans_total_amount | approved_total_amount

2018-12	US	2	1	3000	1000
2019-01	US	1	1	2000	2000
2019-01	DE	1	1	2000	2000

 

SELECT
    # 1. 년-월 만 추출
    DATE_FORMAT(trans_date, '%Y-%m') AS month,
    country,
    # 3. 모든 행 수
    COUNT(*) AS trans_count,
    # 4. state = approved 인 행 수
    COUNT(CASE WHEN state = 'approved' THEN 1 END) AS approved_count,
    # 5. amount 총 합
    SUM(amount) AS trans_total_amount,
    # 6. state = approved 인 amount 총 합
    SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) AS approved_total_amount
FROM transactions
GROUP BY
	# 2. 년-월 & 국가 별로 그룹화
    DATE_FORMAT(trans_date, '%Y-%m'), country