question

Avi_007 avatar image
Avi_007 asked

I need help with following sql analysis

Loan Analysis LendingX is a fintech disbursing micro-loans to customers requiring financial assistance for professional projects, educational needs etc. A subset of the loan management service schema is described below

loan Table loan contains information about disbursed loans.

r/SQL - Loan Analysis

loan_installement Table loan_installement contains information about the computed monthly installements to be paid for each disbursed loan. The payment plan for the entire loan is computed and stored in this table when the loan is disbursed. The status column is updated based on the repayments

r/SQL - Loan Analysis

loan_payment Table loan_payment records information about repayment of borrowed loans.


r/SQL - Loan Analysis

Q. Write a query to calculate the total non-performing outstanding loan amount at each day from 01/06/2020 to 01/02/2021.

r/SQL - Loan Analysis

As an example, consider loan L4 in the sample data above. The total loan amount was 100000, with 25500 due on 02/08/2020 and so on and 4 installments were required. The loan has only 3 repayments against it in loan_payments table, for a total for 55000. 2020/10/31 is 90 days from the first installement due date, by which time the first installement has been paid in full. So non-performing loan value for this loan on 2020/10/31 is 0. 2020/12/01 is 90 days from the second installement due date, by which time only 40,000 out of the pending 51,000 has been paid. So 11,000 is counted as non performing. on 2020/12/09, 15,000 was paid, bringing the non performing amount back to 0. 2020/12/31 is 90 days from the third installement due date, by which time only 55,000 out of the pending 76,500 has been paid. So 21500 is counted as non performing.

Our query should return the following result:


r/SQL - Loan Analysis


r/SQL - Loan Analysis


r/SQL - Loan Analysis

…etc

The query should return non-performing loan value for entire portfolio of loans for each date. To generate this result, you may assume that a calendar table exists which has a listing of each day from 1950 to 2050, or you can use a generate series function for the database of your choosing.


Can anyone help me with this question?

analysis
1618748238670.png (52.2 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.