Write a query to identify all mortgage loans that have been delinquent for more than 90 days at Cenlar FSB, including the borrower's payment history and the total outstanding principal balance.
Use the provided loan, borrower, loan account, and payment tables. Include loans without payment records, preserve payment chronology, and exclude loans at exactly 90 days delinquent or less.
loan_id, borrower details, delinquency days, payment details, prior payment date, payment status, and outstanding_principal_balance.loan_id, payment_date, and payment_id.| Column | Type | Description |
|---|---|---|
| loan_idPK | INT | Unique mortgage loan identifier |
| borrower_id | INT | Borrower associated with the loan |
| current_delinquency_days | INT | Current number of days the loan is delinquent |
| loan_status | VARCHAR(30) | Current servicing status |
| Column | Type | Description |
|---|---|---|
| borrower_idPK | INT | Unique borrower identifier |
| borrower_name | VARCHAR(100) | Borrower's full name |
| borrower_state | VARCHAR(2) | Two-letter borrower state code |
| Column | Type | Description |
|---|---|---|
| loan_idPK | INT | Mortgage loan identifier |
| original_principal | NUMERIC(14,2) | Original principal amount |
| outstanding_principal_balance | NUMERIC(14,2) | Current unpaid principal balance |
| Column | Type | Description |
|---|---|---|
| payment_idPK | INT | Unique payment identifier |
| loan_id | INT | Mortgage loan receiving the payment |
| payment_date | DATE | Date the payment was recorded |
| payment_amount | NUMERIC(12,2) | Total payment amount |
| principal_paid | NUMERIC(12,2) | Amount applied to principal |
| payment_method | VARCHAR(30) | Method used to submit payment |