1193. Monthly Transactions I

간만에 풀어본 SQL문제..

요새 이래저래 바빠서 문제를 좀 못 풀었는데, 머리를 좀 식히고 싶기도 하고 SQL 감도 잃지 않기 위해 풀었다.

 

세상에... 머리 식히려고 SQL 문제를 푸는 날이 오게 될 줄이야...

 

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 date_format(trans_date, '%Y-%m') as month
     , country
     , count(*) as trans_count
     , sum(case when state='approved' then 1 else 0 end) 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 country, month;

 

어려운 문제는 아니었다. 차근차근, 조건들만 잘 맞춰주면 되는 문제였다.