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.
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
loan_payment Table loan_payment records information about repayment of borrowed loans.
Q. Write a query to calculate the total non-performing outstanding loan amount at each day from 01/06/2020 to 01/02/2021.
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:
…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?
