Identifying Loan Non Starters and Finding Ten Payments Made

As a loan administrator, identifying non-starters and tracking payment histories are crucial tasks. In this article, we’ll explore how to identify loan non-starters by analyzing the payment history of customers and find loans where 10 payments have been made successfully.

Understanding Loan Schemas

Before diving into the SQL queries, let’s understand the schema of our tables:

Table: Schedule

| Column Name | Data Type |
| --- | --- |
| LoanID | int |
| PaymentDate | date |
| DemandAmount | decimal |
| InstallmentNo | int |

Table: Collection

| Column Name | Data Type |
| --- | --- |
| LoanID | int |
| TransactionDate | date |
| CollectionAmount | decimal |

In the Schedule table, we have columns for the loan ID, payment date, demand amount, and installment number. In the Collection table, we have columns for the loan ID, transaction date, and collection amount.

Identifying Non Starters

To identify non-starters, we need to find customers who haven’t made any payments. We can do this by analyzing the Schedule and Collection tables.

SELECT 
    a.LoanID, 
    if(SUM(b.CollectionAmount) = 0, 1, 0) as NonStarter 
FROM 
    Schedule a 
JOIN 
    Collection b ON a.LoanID = b.LoanID AND a.InstallmentNo = 1
GROUP BY 
    a.LoanID;

In this query:

• We join the Schedule and Collection tables on the loan ID and installment number.
• We use the SUM aggregation function to calculate the total collection amount for each loan.
• If the total collection amount is zero, we assign a value of 1 to the NonStarter column; otherwise, we assign 0.

This query will return all loans where no payments have been made (i.e., non-starters).

Identifying Loans with Ten Payments Made

To identify loans where 10 payments have been made successfully, we can use a similar approach:

SELECT 
    Demand.LoanID, 
    if(SUM(Demand.Collection) = SUM(Demand.DemandAmount), 1, 0) as DemandCollected 
FROM 
    Demand 
LEFT JOIN 
    Collection on Collection.LoanID = Demand.LoanID and Demand.InstallmentNo <= 10
GROUP BY 
    Demand.LoanID;

In this query:

• We join the Demand and Collection tables on the loan ID and installment number, but we use a left join to include loans with no payments (i.e., not part of the original dataset).
• We calculate the total demand amount for each loan using the SUM aggregation function.
• If the total collection amount equals the total demand amount for each loan, we assign a value of 1 to the DemandCollected column; otherwise, we assign 0.

This query will return all loans where 10 payments have been made successfully (i.e., where the total collection amount equals the total demand amount).

Combining Queries

We can combine these two queries using a union operator (UNION) to get both non-starters and loans with ten payments made:

SELECT 
    a.LoanID, 
    if(SUM(b.CollectionAmount) = 0, 1, 0) as NonStarter 
FROM 
    Schedule a 
JOIN 
    Collection b ON a.LoanID = b.LoanID AND a.InstallmentNo = 1
GROUP BY 
    a.LoanID;

UNION

SELECT 
    Demand.LoanID, 
    if(SUM(Demand.Collection) = SUM(Demand.DemandAmount), 1, 0) as DemandCollected 
FROM 
    Demand 
LEFT JOIN 
    Collection on Collection.LoanID = Demand.LoanID and Demand.InstallmentNo <= 10
GROUP BY 
    Demand.LoanID;

This combined query will return all loans where either no payments have been made (i.e., non-starters) or 10 payments have been made successfully.

Conclusion

In this article, we explored how to identify loan non-starters and find loans where ten payments have been made successfully using SQL queries. We discussed the schema of our tables, analyzed payment histories, and combined queries using a union operator to get both non-starters and loans with ten payments made. These techniques can be applied to various loan administration scenarios to improve efficiency and accuracy.

Last modified on 2025-04-16