By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE user_financial_detail(
id INT PRIMARY KEY,
first_name varchar(20),
last_name varchar(20),
vpa VARCHAR(20),
credit_limit int
);
CREATE TABLE transaction_log(
id INT PRIMARY KEY,
paid_by VARCHAR(20),
paid_to VARCHAR(20),
amount INT,
transacted_on TIMESTAMP
);
INSERT INTO user_financial_detail VALUES
(1,'shea','caldwell','sc',5000),
(2,'martena','leblanc','ml',10000),
(3,'tashya','riley','tr',25000);
INSERT INTO transaction_log VALUES
(1,'ml','tr',13155,'2019/11/21'),
(2,'tr','ml',10883,'2019/09/10'),
(3,'sc','tr',15012,'2018/12/25'),
(4,'ml','sc',5700,'2018/05/18'),
(5,'tr','sc',18473,'2018/07/02');
Records: 3 Duplicates: 0 Warnings: 0
Records: 5 Duplicates: 0 Warnings: 0
select * from user_financial_detail;
| id | first_name | last_name | vpa | credit_limit |
|---|---|---|---|---|
| 1 | shea | caldwell | sc | 5000 |
| 2 | martena | leblanc | ml | 10000 |
| 3 | tashya | riley | tr | 25000 |
select * from transaction_log;
| id | paid_by | paid_to | amount | transacted_on |
|---|---|---|---|---|
| 1 | ml | tr | 13155 | 2019-11-21 00:00:00 |
| 2 | tr | ml | 10883 | 2019-09-10 00:00:00 |
| 3 | sc | tr | 15012 | 2018-12-25 00:00:00 |
| 4 | ml | sc | 5700 | 2018-05-18 00:00:00 |
| 5 | tr | sc | 18473 | 2018-07-02 00:00:00 |
select paid_by vpa, sum(-amount) total_amount
from transaction_log
group by paid_by
union all
select paid_to vpa, sum(amount) total_amount
from transaction_log
group by paid_to
| vpa | total_amount |
|---|---|
| ml | -18855 |
| sc | -15012 |
| tr | -29356 |
| ml | 10883 |
| sc | 24173 |
| tr | 28167 |
select vpa, sum(total_amount) balance
from (
select paid_by vpa, sum(-amount) total_amount
from transaction_log
group by paid_by
union all
select paid_to vpa, sum(amount) total_amount
from transaction_log
group by paid_to
) b
group by vpa
| vpa | balance |
|---|---|
| ml | -7972 |
| sc | 9161 |
| tr | -1189 |
select
concat(u.first_name, ' ', u.last_name) name,
b.vpa,
b.balance + u.credit_limit current_balance,
case
when b.balance + u.credit_limit >= 0 then 'no'
else 'yes'
end credit_limit_breached
from user_financial_detail u
inner join (
select vpa, sum(total_amount) balance
from (
select paid_by vpa, sum(-amount) total_amount
from transaction_log
group by paid_by
union all
select paid_to vpa, sum(amount) total_amount
from transaction_log
group by paid_to
) bi
group by vpa
) b on b.vpa = u.vpa
| name | vpa | current_balance | credit_limit_breached |
|---|---|---|---|
| shea caldwell | sc | 14161 | no |
| martena leblanc | ml | 2028 | no |
| tashya riley | tr | 23811 | no |