Leetcode SQL (#1193 – #1194, #1204 – #1205)

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

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

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

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