# $1555. 银行账户概要
# https://leetcode-cn.com/problems/bank-account-summary/

# SQL架构
Create table If Not Exists Users (user_id int, user_name varchar(20), credit int);
Create table If Not Exists Transactions (trans_id int, paid_by int, paid_to int, amount int, transacted_on date);
Truncate table Users;
insert into Users (user_id, user_name, credit) values ('1', 'Moustafa', '100');
insert into Users (user_id, user_name, credit) values ('2', 'Jonathan', '200');
insert into Users (user_id, user_name, credit) values ('3', 'Winston', '10000');
insert into Users (user_id, user_name, credit) values ('4', 'Luis', '800');
Truncate table Transactions;
insert into Transactions (trans_id, paid_by, paid_to, amount, transacted_on) values ('1', '1', '3', '400', '2020-08-01');
insert into Transactions (trans_id, paid_by, paid_to, amount, transacted_on) values ('2', '3', '2', '500', '2020-08-02');
insert into Transactions (trans_id, paid_by, paid_to, amount, transacted_on) values ('3', '2', '1', '200', '2020-08-03');

# Write your MySQL query statement below
select
    u.user_id,
    u.user_name,
    u.credit + ifnull(
        sum(if(t.paid_by = u.user_id, -1, 1) * t.amount),
        0
    ) as credit,
    if(
        u.credit + ifnull(
            sum(if(t.paid_by = u.user_id, -1, 1) * t.amount),
            0
        ) < 0,
        'Yes',
        'No'
    ) as credit_limit_breached
from
    Users u
    left join Transactions t on t.paid_by = u.user_id
    or t.paid_to = u.user_id
group by
    u.user_id,
    u.user_name,
    u.credit;

# clean-up
drop table Users;
drop table Transactions;