How to extract customers that are yet to complete their credit with outstanding amount and Date

Question

I have two tables one for Credit and the second for those who have paid their credits

Table 1

credit_id   customer_id total_amount_given  disbursement_date
1                1              50000          2020-09-02
2                2              60000          2020-09-02
3                1              10000          2020-10-05
4                3              100000         2020-09-02

Table 2

payment_id  credit_id   amount  type             payment_timestamp
1          1            50000   disbursement    2020-10-01 10:01:12
2          2             1000   repayment       2020-10-01 10:05:12
3          1             10000  repayment       2020-10-01 10:31:01
4          2             100    repayment       2020-11-01 17:11:01

Definition of Total outstanding balance = total disbursed amount (type=’disbursement’ in table 2) - total repaid amount (type=’repayment’ in table 2)

Assuming that all credit tenure irrespective of the credit amount is for 90 days only. I want to write a query to create a table that will give me total outstanding balance on each day from disbursement day till last repayment date of the rent for each customer – credit combination.

Required Table Structure

Required Table Structure date|Customer_id|credit_id|total_amount_disbursed|total_outstanding_amount|latest_repayment_date