## 1193. Monthly Transactions I

Question:

Solution:

``````select
substr(trans_date,1,7) as month,
country,
count(distinct id) as trans_count,
count(distinct case when state = 'approved' then id else null 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 1,2
order by 1``````

## 1194. Tournament Winners

Question:

Solution:

``````select
b.group_id,
min(b.player_id) as player_id
from (
select
a.group_id,
a.player_id,
rank() over(partition by a.group_id order by a.total_score desc, a.player_id) as r
from(
select
p.group_id,
s.player_id,
sum(s.score) as total_score
from(
select
first_player as player_id,
sum(first_score) as score
from Matches
group by 1
union all
select
second_player as player_id,
sum(second_score) as score
from Matches
group by 1
) s
join Players p
on s.player_id = p.player_id
group by 1,2
) a
) b
where b.r = 1 /* restrict to players with the highest total points in each group */
group by 1
order by 1``````

## 1204. Last Person to Fit in the Elevator

Question:

Solution:

``````select
b.person_name
from(
select
a.person_id,
a.person_name,
a.weight,
a.cum_weight,
a.turn,
row_number() over (order by turn desc) as r
from (
select
*,
sum(weight) over(order by turn) as cum_weight
from Queue
) a
where a.cum_weight <= 1000
) b
where b.r = 1``````

## 1205. Monthly Transactions II

Question:

Solution:

``````select
c.*
from(
select
month,
country,
ifnull(count(case when state = 'approved' then id else null end),0) as approved_count,
ifnull(sum(case when state = 'approved' then amount else null end),0) as approved_amount,
ifnull(count(case when state = 'chargeback' then id else null end),0) as chargeback_count,
ifnull(sum(case when state = 'chargeback' then amount else null end),0) as chargeback_amount
from (
select
a.id,
cc.country,
month,
state,
amount
from (
select
id,
state,
amount,
substr(trans_date,1,7) as month
from Transactions
union
select
trans_id as id,
'chargeback' as state,
t.amount,
substr(trans_date,1,7) as month
from Chargebacks c
left join (
select
id,
amount
from Transactions
) t
on c.trans_id = t.id
) a
left join (
select distinct
id,
country
from Transactions
) cc
on a.id = cc.id
) b
group by 1,2
) c
where c.approved_count + c.approved_amount + c.chargeback_count + c.chargeback_amount > 0
order by month``````