SQL | LeetCode 1193. 월별, 국가별 거래 요약 통계 구하기

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                  |
+----------+---------+-------------+----------------+--------------------+-----------------------+

select 
    left(trans_date, 7) as month,
    country,
    count(*) as trans_count,
    sum(state = 'approved') as approved_count,
    sum(amount) as trans_total_amount,
    sum(case when state = 'approved' then amount else 0 end) as approved_total_amount
from transactions
group by month, country;

먼저 LEFT(trans_date, 7)를 사용해서 날짜에서 앞 7자리인 YYYY-MM 형식(예: 2024-01)을 추출해 월 단위로 그룹화합니다. 이건 DATE_FORMAT(trans_date, '%Y-%m') 대신 사용할 수 있습니다. COUNT(*)는 해당 월과 국가에서 발생한 전체 거래 수를 계산합니다. SUM(state = 'approved')는 state가 'approved'인 경우에만 1로 간주해 합산하므로 승인된 거래 수를 간단하게 구할 수 있습니다. 그다음 SUM(amount)는 전체 거래 금액의 합이고, SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END)는 승인된 거래의 금액만 더합니다.