SQL Interview Questions – Bank Account Summary

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 0).

Write an SQL query to report.

• user_id,
• user_name,
• credit, current balance after performing transactions, and
• 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.

Problem Link – Bank Account Summary

Difficulty Level – Medium

Solution –

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.