[문제풀이] Leet code - Monthly Transactions I

출처

 

문제

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.

 

문제에 대한 해석

 

state컬럼의 approve를 구분하여 approved_count와 apporived_total_count를 만들어야 한다

풀이(MYSQL)

with base as (
    select date_format( trans_date ,'%Y-%m' ) as 'month',
    country, state,
    if(state='approved', 1,0) as 'approved',
    if(state='approved', amount,0) as 'approved_amount',
    amount
    from Transactions
    )
select month, 
    country,
    count(*) as 'trans_count',
    sum(approved) as 'approved_count',
    sum(amount) as 'trans_total_amount',
    sum(approved_amount) as 'approved_total_amount'
from base
group by month, country

 

좀더 쿼리를 간단하게 쓰고 싶으면

 

select date_format(trans_date, "%Y-%m") as month, 
    country, count(*) as trans_count, 
    sum(if(state='approved',1,0)) as approved_count, 
    sum(amount) as trans_total_amount, 
    sum(if(state='approved',amount,0))as approved_total_amount
from Transactions
group by date_format(trans_date, "%Y-%m"),country;

 

if 문 앞에 집계함수를 사용하면 된다