Bank Account Summary Problem
Description
LeetCode Problem 1555.
Table: Users
1
2
3
4
5
6
7
8
9
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| user_id | int |
| user_name | varchar |
| credit | int |
+--------------+---------+
user_id is the primary key for this table.
Each row of this table contains the current credit information for each user.
Table: Transactions
1
2
3
4
5
6
7
8
9
10
11
12
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| trans_id | int |
| paid_by | int |
| paid_to | int |
| amount | int |
| transacted_on | date |
+---------------+---------+
trans_id is the primary key for this table.
Each row of this table contains the information about the transaction in the bank.
User with id (paid_by) transfer money to user with id (paid_to).
Leetcode Bank (LCB) helps its coders in making virtual payments. Our bank records all transactions in the table Transaction, we want to find out the current balance of all users and check wheter they have breached their credit limit (If their current credit is less than 0).
Write an SQL query to report.
- user_id
- user_name
- credit, current balance after performing transactions.
- credit_limit_breached, check credit_limit (“Yes” or “No”)
Return the result table in any order.
The query result format is in the following example.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Users table:
+------------+--------------+-------------+
| user_id | user_name | credit |
+------------+--------------+-------------+
| 1 | Moustafa | 100 |
| 2 | Jonathan | 200 |
| 3 | Winston | 10000 |
| 4 | Luis | 800 |
+------------+--------------+-------------+
Transactions table:
+------------+------------+------------+----------+---------------+
| trans_id | paid_by | paid_to | amount | transacted_on |
+------------+------------+------------+----------+---------------+
| 1 | 1 | 3 | 400 | 2020-08-01 |
| 2 | 3 | 2 | 500 | 2020-08-02 |
| 3 | 2 | 1 | 200 | 2020-08-03 |
+------------+------------+------------+----------+---------------+
Result table:
+------------+------------+------------+-----------------------+
| user_id | user_name | credit | credit_limit_breached |
+------------+------------+------------+-----------------------+
| 1 | Moustafa | -100 | Yes |
| 2 | Jonathan | 500 | No |
| 3 | Winston | 9900 | No |
| 4 | Luis | 800 | No |
+------------+------------+------------+-----------------------+
Moustafa paid $400 on "2020-08-01" and received $200 on "2020-08-03", credit (100 -400 +200) = -$100
Jonathan received $500 on "2020-08-02" and paid $200 on "2020-08-08", credit (200 +500 -200) = $500
Winston received $400 on "2020-08-01" and paid $500 on "2020-08-03", credit (10000 +400 -500) = $9990
Luis didn't received any transfer, credit = $800
MySQL Solution
1
2
3
4
5
6
7
8
select *, (case when credit < 0 then "Yes" else "No" end) as credit_limit_breached
from (select u.user_id, u.user_name,
credit + ifnull(sum((case when u.user_id=t.paid_by then -t.amount
else t.amount end)), 0) as credit
from Users u
left join Transactions t
on u.user_id = t.paid_by or u.user_id = t.paid_to
group by u.user_id, u.user_name) t
LeetCode Database - Medium
LeetCode 1421
LeetCode 1440
LeetCode 1445
LeetCode 1454
LeetCode 1459
LeetCode 1468
LeetCode 1501
LeetCode 1532
LeetCode 1549
LeetCode 1555
LeetCode 1596
LeetCode 1613
LeetCode 1699
LeetCode 1709
LeetCode 1715
LeetCode 1747
LeetCode 1783
LeetCode 1811
More LeetCode Database
MySQL Tutorials