[LeetCode] 1193. Monthly Transactions I - SQL

링크: https://leetcode.com/problems/monthly-transactions-i/

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

---

문제풀이

목표: 각각의 달과 나라별로 transaction의 개수와 총 amount, 인증된 transaction의 개수와 총 amount를 구하라

최종코드

SELECT 
    DATE_FORMAT(trans_date, '%Y-%m') month,
    country,
    COUNT(id) trans_count,
    SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) approved_count,
    SUM(amount) trans_total_amount,
    SUM((CASE WHEN state = 'approved' THEN 1 ELSE 0 END)*amount) approved_total_amount
FROM Transactions
GROUP BY DATE_FORMAT(trans_date, '%Y-%m'), country

설명

FROM 절

FROM Transactions

GROUP BY 절

GROUP BY DATE_FORMAT(trans_date, '%Y-%m'), country

• DATE_FORMAT 함수로 연, 월로 날짜 형식을 정하고, 월, 나라로 그룹화

SELECT 절

SELECT 
    DATE_FORMAT(trans_date, '%Y-%m') month,
    country,
    COUNT(id) trans_count,
    SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) approved_count,
    SUM(amount) trans_total_amount,
    SUM((CASE WHEN state = 'approved' THEN 1 ELSE 0 END)*amount) approved_total_amount

• trans_count: 그룹화된 id의 개수를 COUNT()
• approved_count: 그룹화된 결과에서 CASE WHEN을 사용해 state가 approved인 것은 1, 아닌 것은 0 으로 해서 모두 SUM()
• trans_total_amount: amount 모두 더하기
• approved_total_amount: 마찬가지로 CASE WHEN을 사용해 state가 approved 인 것은 1, 아니면 0으로 해서 그것을 amount와 곱한다. 이후 모든 값을 SUM