Problem Description –
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 whether they have breached their credit limit (If their current credit is less than
Write an SQL query to report.
credit, current balance after performing transactions, and
credit_limit_breached, check credit_limit (
Return the result table in any order.
The query result format is in the following example.
Problem Link – Bank Account Summary
Difficulty Level – Medium
WITH total_paid as ( SELECT paid_by, SUM(amount) as total_paid FROM Transactions GROUP BY 1 ), total_received as ( SELECT paid_to, SUM(amount) as total_received FROM Transactions GROUP BY 1 ) SELECT u.user_id, u.user_name, u.credit - IFNULL(tp.total_paid,0) + IFNULL(tr.total_received,0) as credit, CASE WHEN (u.credit - IFNULL(tp.total_paid,0) + IFNULL(tr.total_received,0)) < 0 THEN 'Yes' ELSE 'NO' END as credit_limit_breached FROM Users as u LEFT JOIN total_paid as tp ON u.user_id = tp.paid_by LEFT JOIN total_received as tr ON u.user_id = tr.paid_to ORDER BY 1
To solve this problem, we first calculated the total amount paid and received by each customers. Then we join these two table with the Users table using a LEFT JOIN. Then we simply calculate the credit. From the credit amount we subtracted the total paid amount and added the total received amount. And we also used a CASE statement to determine if someone exceeds their credit limit.