# HackerRank: [SQL Question for LinkedIn] BANK ACCOUNTS SUMMARY | MySQL Solution by APDaga

I came across a SQL question by HackerRank

Here I am providing my solution to the problem "Bank Accounts Summary".

It will help you learn and understand SQL in a better way.

Please make use of my blog posts for learning purposes only and feel free to ask your questions in the comment box below in case of any doubt.

### SQL Problem Statement:

Virtual World Bank (VWB) helps its users in making online payments using virtual payment address (vpa). It maintains its customer details like name, vpa and credit limit in the table user_financial_detail

Each user can easily transfer money from his/her account to another VWB user's account by using his/her uniquely assigned vpa and receipt user's vpa. The VWB records all such transactions in the table transaction_log, storing information such as the sender's vpa, receipient's vpa and the amount transferred.

VWB wants to find out the current balance of all the users who have ever transacted and check whether they have breached their credit limit.

Write a query that prints this information for all the users present in the table user_finincial_detail in the following format: name | vpa | current_balance | credit_limit_breached

The credit_limit_breached column should contain either 'YES' or 'NO'. If the user is overdrawn by more than the credit limit, return 'YES' in this column. Otherwise, return 'NO'.

The order of output does not matter.

### Schema:

There are 2 Tables: user_financial_detail, transaction_log.

### Sample Output:

```Shea Caldwell [email protected] 9161 NO
Martena Leblanc [email protected] -7972 NO
Tashya Riley [email protected] -1189 NO```

### Explanation:

• Shea Caldwell with vpa [email protected] has a current balance of 9161 and has not breached the credit limit of 5000. She has received 24173 (5700 + 18473) and sent 15012 as transfer amounts.

• Martena Leblanc with vpa [email protected] has a current balance of -7972 and has not breached the credit limit of 10000. She has received 10883 and sent 18855 (13155 + 5700) as transfer amounts.

• Tashya Riley with vpa [email protected] has a current balance of -1189 and has not breached the credit limit of 25000. She has received 28167 (13155 + 15012) and sent 29356 (10883 + 18473) as transfer amounts.

### Solution: (MySQL Query):

```SELECT first_name, last_name, vpa,
IFNULL(in_amt,0) - IFNULL(out_amt,0) AS current_balance,
CASE WHEN (credit_limit + IFNULL(in_amt,0) - IFNULL(out_amt,0)) < 0
THEN 'YES'
ELSE 'NO'
END AS credit_limit_breached
FROM user_financial_detail u
LEFT JOIN (
SELECT paid_by,
SUM(amount) as out_amt
FROM transaction_log
GROUP BY paid_by
) out_tran ON u.vpa = out_tran.paid_by
LEFT JOIN (
SELECT paid_to,
SUM(amount) as in_amt
FROM transaction_log
GROUP BY paid_to
) in_tran ON u.vpa = in_tran.paid_to```

NOTE:
• in_tran table have all the in-transactions (credit entries) by all the users (vpa)

• out_tran table have all the out-transactions (debit entries) by all the users (vpa)

• Current Balance = Total Amount Credited - Total Amount Debited

• If (Credit_Limit + Total Amount Credited - Total Amount Debited) < 0 Then we mark Credit_limit_breached as 'YES' otherwise 'NO'

• CASE WHEN is used to just like IF... Else... in other programming languages.

• LEFT JOIN returns all the records from the left table with records that have matching values in the right table.

--------------------------------------------------------------------------------