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

byAkshay Daga (APDaga) -
0

HackerRank: [SQL Question for LinkedIn] BANK ACCOUNTS SUMMARY | MySQL Solution by APDaga Tech
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.

Click Here for the previous blog-post in the series.

Recommended SQL Courses:
  1. Udemy: SQL - MySQL for Data Analytics and Business Intelligence
  2. LinkedIn: Master SQL for Data Science
  3. edX: Databases: Advanced Topics in SQL
  4. edureka: SQL Essentials Training & Certification
  5. Coursera: Learn SQL Basics for Data Science
  6. Eduonix: SQL Queries

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.

user_financial_detail schema

transaction_log schema



Sample Data Tables:

user_financial_detail data

transaction_log data

Sample Output:

Shea Caldwell shea.caldwell@vwb 9161 NO
Martena Leblanc martena.leblanc@vwb -7972 NO
Tashya Riley tashya.reley@vwb -1189 NO

Explanation:

  • Shea Caldwell with vpa shea.caldwell@vwb 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 martena.leblanc@vwb 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 tashya.riley@vwb 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.

--------------------------------------------------------------------------------
Click here to see solutions for all Machine Learning Coursera Assignments.
&
Click here to see more codes for Raspberry Pi 3 and similar Family.
&
Click here to see more codes for NodeMCU ESP8266 and similar Family.
&
Click here to see more codes for Arduino Mega (ATMega 2560) and similar Family.
Feel free to ask doubts in the comment section. I will try my best to answer it.
If you find this helpful by any mean like, comment and share the post.
This is the simplest way to encourage me to keep doing such work.

Thanks & Regards,
-Akshay P Daga
Tags:
إرسال تعليق (0)
أحدث أقدم