| Current Actual UPB | UPB at Issuance | Original Interest Rate | Loan Identifier | Seller Name | Monthly Reporting Period | Remaining Months To Maturity | Origination Date | Deal Name | Borrower Credit Score at Origination | Zero Balance Effective Date | Scheduled Principal Current | Remaining Months to Legal Maturity | Original UPB | Original Loan Term |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 485775.65 | 485775.65 | 6.875 | 136610574 | Flagstar Bank, National Association | 20230820 | 350 | 20221020 | CAS 2023 R08 G1 | 735 | 0 | 0 | 351 | 490000 | 360 |
| 248112.85 | 248112.85 | 6.375 | 136610575 | Planet Home Lending, LLC | 20230820 | 352 | 20221020 | CAS 2023 R08 G1 | 709 | 0 | 0 | 352 | 250000 | 360 |
| 468571.34 | 468571.34 | 6.5 | 136610576 | Rocket Mortgage, LLC | 20230820 | 351 | 20221020 | CAS 2023 R08 G1 | 787 | 0 | 0 | 351 | 473000 | 360 |
| 307534.77 | 307534.77 | 5.25 | 136610577 | Rocket Mortgage, LLC | 20230820 | 351 | 20220920 | CAS 2023 R08 G1 | 732 | 0 | 0 | 351 | 311000 | 360 |
| 348025.67 | 348025.67 | 5.5 | 136610578 | U.S. Bank N.A. | 20230820 | 350 | 20221020 | CAS 2023 R08 G1 | 763 | 0 | 0 | 351 | 352000 | 360 |
| 139258.92 | 139258.92 | 6 | 136610579 | Other | 20230820 | 339 | 20220920 | CAS 2023 R08 G1 | 720 | 0 | 0 | 351 | 142000 | 360 |
| 202982.62 | 202982.62 | 5.875 | 136610580 | U.S. Bank N.A. | 20230820 | 351 | 20221020 | CAS 2023 R08 G1 | 799 | 0 | 0 | 351 | 205000 | 360 |
| 172686.14 | 172686.14 | 6.575 | 136610581 | Planet Home Lending, LLC | 20230820 | 352 | 20221020 | CAS 2023 R08 G1 | 690 | 0 | 0 | 352 | 174000 | 360 |
| 110751.32 | 110751.32 | 5.375 | 136610582 | Other | 20230820 | 323 | 20221020 | CAS 2023 R08 G1 | 762 | 0 | 0 | 351 | 116000 | 360 |
| 231756.02 | 231756.02 | 5.5 | 136610583 | JPMorgan Chase Bank, National Association | 20230820 | 350 | 20220920 | CAS 2023 R08 G1 | 795 | 0 | 0 | 350 | 234000 | 360 |
Total 30200 rows
{
"01_read_payments": {
"type": "file_table",
"desc": "Read source files to a table",
"r": {
"urls": "{file_urls|stringlist}",
"columns": {
"col_loan_id": {
"col_type": "int",
"parquet": {
"col_name": "Loan Identifier"
}
},
"col_deal_name": {
"col_type": "string",
"parquet": {
"col_name": "Deal Name"
}
},
"col_seller_name": {
"col_type": "string",
"parquet": {
"col_name": "Seller Name"
}
},
"col_origination_date": {
"col_type": "int",
"parquet": {
"col_name": "Origination Date"
}
},
"col_original_interest_rate": {
"col_type": "float",
"parquet": {
"col_name": "Original Interest Rate"
}
},
"col_borrower_credit_score_at_origination": {
"col_type": "int",
"parquet": {
"col_name": "Borrower Credit Score at Origination"
}
},
"col_original_upb": {
"col_type": "decimal2",
"parquet": {
"col_name": "Original UPB"
}
},
"col_upb_at_issuance": {
"col_type": "decimal2",
"parquet": {
"col_name": "UPB at Issuance"
}
},
"col_original_loan_term": {
"col_type": "int",
"parquet": {
"col_name": "Original Loan Term"
}
},
"col_monthly_reporting_period": {
"col_type": "int",
"parquet": {
"col_name": "Monthly Reporting Period"
}
},
"col_current_actual_upb": {
"col_type": "decimal2",
"parquet": {
"col_name": "Current Actual UPB"
}
},
"col_remaining_months_to_legal_maturity": {
"col_type": "int",
"parquet": {
"col_name": "Remaining Months to Legal Maturity"
}
},
"col_remaining_months_to_maturity": {
"col_type": "int",
"parquet": {
"col_name": "Remaining Months To Maturity"
}
},
"col_zero_balance_effective_date": {
"col_type": "int",
"parquet": {
"col_name": "Zero Balance Effective Date"
}
}
}
},
"w": {
"name": "payments",
"having_tmp": "(w.zero_balance_effective_date == 0 || w.scheduled_principal_current > 0) && w.original_income > 0",
"fields": {
"loan_id": {
"expression": "r.col_loan_id",
"type": "int"
},
"deal_name": {
"expression": "r.col_deal_name",
"type": "string"
},
"origination_date": {
"expression": "r.col_origination_date",
"type": "int"
},
"seller_name": {
"expression": "r.col_seller_name",
"type": "string"
},
"original_interest_rate": {
"expression": "r.col_original_interest_rate",
"type": "float"
},
"borrower_credit_score_at_origination": {
"expression": "r.col_borrower_credit_score_at_origination",
"type": "int"
},
"original_upb": {
"expression": "r.col_original_upb",
"type": "decimal2"
},
"upb_at_issuance": {
"expression": "r.col_upb_at_issuance",
"type": "decimal2"
},
"original_loan_term": {
"expression": "r.col_original_loan_term",
"type": "int"
},
"payment_json": {
"expression": "strings.ReplaceAll(fmt.Sprintf(`{'monthly_reporting_period':%d,'current_actual_upb':%s,'remaining_months_to_legal_maturity':%d,'remaining_months_to_maturity':%d,'zero_balance_effective_date':%d}`,r.col_monthly_reporting_period, r.col_current_actual_upb, r.col_remaining_months_to_legal_maturity, r.col_remaining_months_to_maturity,r.col_zero_balance_effective_date), `'`,`\"`)",
"type": "string"
}
},
"indexes": {
"idx_payments_by_loan_id": "non_unique(loan_id)"
}
}
}
}
| rowid | batch_idx | borrower_credit_score_at_origination | deal_name | loan_id | original_interest_rate | original_loan_term | original_upb | origination_date | payment_json | seller_name | upb_at_issuance |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 2692698828023470054 | 0 | 763 | CAS 2023 R08 G1 | 136625690 | 6.25 | 360 | 291000.00 | 20221020 | {"monthly_reporting_period":20230820,"current_actual_upb":288660.84,"remaining_months_to_legal_matur ... total length 176 | Rocket Mortgage, LLC | 288660.84 |
| 3997131445414456708 | 1 | 689 | CAS 2023 R08 G1 | 136642797 | 6.75 | 360 | 407000.00 | 20221220 | {"monthly_reporting_period":20230820,"current_actual_upb":404597.56,"remaining_months_to_legal_matur ... total length 176 | Other | 404597.56 |
| 2928770662528460462 | 1 | 768 | CAS 2023 R08 G1 | 136662702 | 6.125 | 360 | 84000.00 | 20220920 | {"monthly_reporting_period":20230820,"current_actual_upb":83164.58,"remaining_months_to_legal_maturi ... total length 175 | Other | 83164.58 |
| 4433795961790658242 | 1 | 736 | CAS 2023 R08 G1 | 136650451 | 5.625 | 360 | 276000.00 | 20220920 | {"monthly_reporting_period":20230820,"current_actual_upb":272590.71,"remaining_months_to_legal_matur ... total length 176 | Rocket Mortgage, LLC | 272590.71 |
| 1266457818141924647 | 0 | 793 | CAS 2023 R08 G1 | 136616839 | 5 | 360 | 363000.00 | 20220820 | {"monthly_reporting_period":20230820,"current_actual_upb":358495.61,"remaining_months_to_legal_matur ... total length 176 | Other | 358495.61 |
| 4560171343055258208 | 0 | 734 | CAS 2023 R08 G1 | 136619674 | 7.625 | 360 | 191000.00 | 20221120 | {"monthly_reporting_period":20230820,"current_actual_upb":190043.18,"remaining_months_to_legal_matur ... total length 176 | Other | 190043.18 |
| 7624527983841155731 | 1 | 777 | CAS 2023 R08 G1 | 136643547 | 7.125 | 360 | 79000.00 | 20221120 | {"monthly_reporting_period":20230820,"current_actual_upb":78235.63,"remaining_months_to_legal_maturi ... total length 175 | United Wholesale Mortgage, LLC | 78235.63 |
| 7791730123361816823 | 0 | 793 | CAS 2023 R08 G1 | 136632792 | 5.625 | 360 | 480000.00 | 20221020 | {"monthly_reporting_period":20230820,"current_actual_upb":474480.85,"remaining_months_to_legal_matur ... total length 176 | Other | 474480.85 |
| 3649628387832893919 | 1 | 809 | CAS 2023 R08 G1 | 136670781 | 5.25 | 360 | 559000.00 | 20221020 | {"monthly_reporting_period":20230820,"current_actual_upb":546107.15,"remaining_months_to_legal_matur ... total length 176 | Other | 546107.15 |
| 8707679687933728275 | 1 | 808 | CAS 2023 R08 G1 | 136662618 | 5.99 | 360 | 600000.00 | 20221020 | {"monthly_reporting_period":20230820,"current_actual_upb":589153.26,"remaining_months_to_legal_matur ... total length 176 | Guaranteed Rate, Inc. | 589153.26 |
Total 60345 rows
{
"02_loan_ids": {
"type": "distinct_table",
"desc": "Select distinct loan ids",
"start_policy": "{manual_if_multi}",
"rerun_policy": "fail",
"r": {
"table": "payments",
"rowset_size": 10000,
"expected_batches_total": "{expected_batches|number}"
},
"w": {
"name": "loan_ids",
"fields": {
"loan_id": {
"expression": "r.loan_id",
"type": "int"
},
"deal_name": {
"expression": "r.deal_name",
"type": "string"
},
"origination_date": {
"expression": "r.origination_date",
"type": "int"
},
"seller_name": {
"expression": "r.seller_name",
"type": "string"
},
"original_interest_rate": {
"expression": "r.original_interest_rate",
"type": "float"
},
"borrower_credit_score_at_origination": {
"expression": "r.borrower_credit_score_at_origination",
"type": "int"
},
"original_upb": {
"expression": "r.original_upb",
"type": "decimal2"
},
"upb_at_issuance": {
"expression": "r.upb_at_issuance",
"type": "decimal2"
},
"original_loan_term": {
"expression": "r.original_loan_term",
"type": "int"
}
},
"indexes": {
"idx_loan_ids_loan_id": "unique(loan_id)",
"idx_loan_ids_deal_name": "non_unique(deal_name)"
}
}
}
}
| rowid | batch_idx | borrower_credit_score_at_origination | deal_name | loan_id | original_interest_rate | original_loan_term | original_upb | origination_date | seller_name | upb_at_issuance |
|---|---|---|---|---|---|---|---|---|---|---|
| 6457891039259649104 | 9 | 767 | CAS 2023 R08 G1 | 136660929 | 5.125 | 360 | 114000.00 | 20220920 | Other | 112240.33 |
| 8595116722281286274 | 0 | 755 | CAS 2023 R08 G1 | 136625253 | 4.875 | 360 | 379000.00 | 20221020 | Truist Bank | 374737.06 |
| 642312648763271491 | 5 | 777 | CAS 2023 R08 G1 | 136644773 | 6.125 | 360 | 336000.00 | 20221120 | Guaranteed Rate, Inc. | 282979.09 |
| 6431317821336340373 | 7 | 804 | CAS 2023 R08 G1 | 136622247 | 6.25 | 360 | 284000.00 | 20221020 | Wells Fargo Bank, N.A. | 281241.23 |
| 538570546331060335 | 7 | 713 | CAS 2023 R08 G1 | 136661077 | 6.125 | 360 | 213000.00 | 20220820 | Other | 210465.86 |
| 9125367591281352855 | 1 | 804 | CAS 2023 R08 G1 | 136636360 | 6.7 | 360 | 175000.00 | 20221120 | Truist Bank | 173758.69 |
| 3891149617198986514 | 8 | 698 | CAS 2023 R08 G1 | 136624835 | 5.875 | 360 | 505000.00 | 20221120 | Other | 500746.75 |
| 9003692999608911916 | 1 | 787 | CAS 2023 R08 G1 | 136649709 | 5.625 | 360 | 288000.00 | 20220920 | Other | 284532.60 |
| 7229415194510547357 | 6 | 745 | CAS 2023 R08 G1 | 136625596 | 5.75 | 360 | 880000.00 | 20221020 | Planet Home Lending, LLC | 871570.76 |
| 6276762338259684685 | 7 | 774 | CAS 2023 R08 G1 | 136652859 | 4.125 | 360 | 376000.00 | 20220920 | Other | 370708.19 |
Total 60345 rows
{
"02_deal_names": {
"type": "distinct_table",
"desc": "Select distinct deal names",
"start_policy": "{manual_if_multi}",
"rerun_policy": "fail",
"r": {
"table": "loan_ids",
"rowset_size": 10000,
"expected_batches_total": "{expected_batches|number}"
},
"w": {
"name": "deal_names",
"fields": {
"deal_name": {
"expression": "r.deal_name",
"type": "string"
}
},
"indexes": {
"idx_deal_names_deal_name": "unique(deal_name)"
}
}
}
}
| rowid | batch_idx | deal_name |
|---|---|---|
| 531989646856925890 | 0 | CAS 2023 R08 G1 |
Total 1 rows
{
"03_deal_total_upbs": {
"type": "table_lookup_table",
"desc": "For each deal, calculate total UPBs",
"start_policy": "{manual_if_multi}",
"r": {
"table": "deal_names",
"expected_batches_total": "{expected_batches|number}"
},
"l": {
"index_name": "idx_loan_ids_deal_name",
"join_on": "r.deal_name",
"idx_read_batch_size": 10000,
"right_lookup_read_batch_size": 10000,
"group": true,
"join_type": "left"
},
"w": {
"name": "deal_total_upbs",
"fields": {
"deal_name": {
"expression": "r.deal_name",
"type": "string"
},
"total_original_upb": {
"expression": "sum(l.original_upb)",
"type": "decimal2"
},
"total_upb_at_issuance": {
"expression": "sum(l.upb_at_issuance)",
"type": "decimal2"
},
"total_original_upb_for_nonzero_rates": {
"expression": "sum_if(l.original_upb, l.original_interest_rate > 0)",
"type": "decimal2"
},
"total_original_upb_for_nonzero_credit_scores": {
"expression": "sum_if(l.original_upb, l.borrower_credit_score_at_origination > 0)",
"type": "decimal2"
}
}
}
}
}
| rowid | batch_idx | deal_name | total_original_upb | total_original_upb_for_nonzero_credit_scores | total_original_upb_for_nonzero_rates | total_upb_at_issuance |
|---|---|---|---|---|---|---|
| 8630296434510783352 | 5 | CAS 2023 R08 G1 | 19356281000.00 | 19339292000.00 | 19356281000.00 | 18880190518.06 |
Total 1 rows
{
"04_loan_payment_summaries": {
"type": "table_lookup_table",
"desc": "For each loan, merge all payments into single json string",
"start_policy": "{manual_if_multi}",
"r": {
"table": "loan_ids",
"expected_batches_total": "{expected_batches|number}"
},
"l": {
"index_name": "idx_payments_by_loan_id",
"join_on": "r.loan_id",
"group": true,
"join_type": "left"
},
"w": {
"name": "loan_payment_summaries",
"fields": {
"loan_id": {
"expression": "r.loan_id",
"type": "int"
},
"deal_name": {
"expression": "r.deal_name",
"type": "string"
},
"origination_date": {
"expression": "r.origination_date",
"type": "int"
},
"seller_name": {
"expression": "r.seller_name",
"type": "string"
},
"original_interest_rate": {
"expression": "r.original_interest_rate",
"type": "float"
},
"borrower_credit_score_at_origination": {
"expression": "r.borrower_credit_score_at_origination",
"type": "int"
},
"original_upb": {
"expression": "r.original_upb",
"type": "decimal2"
},
"upb_at_issuance": {
"expression": "r.upb_at_issuance",
"type": "decimal2"
},
"original_loan_term": {
"expression": "r.original_loan_term",
"type": "int"
},
"payments_json": {
"expression": "string_agg(l.payment_json,\",\")",
"type": "string"
}
}
}
}
}
| rowid | batch_idx | borrower_credit_score_at_origination | deal_name | loan_id | original_interest_rate | original_loan_term | original_upb | origination_date | payments_json | seller_name | upb_at_issuance |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1615231738186287830 | 3 | 752 | CAS 2023 R08 G1 | 136661569 | 6.125 | 360 | 134000.00 | 20220820 | {"monthly_reporting_period":20230820,"current_actual_upb":132925.83,"remaining_months_to_legal_matur ... total length 176 | CitiMortgage, Inc. | 132925.83 |
| 7388934975796115909 | 8 | 784 | CAS 2023 R08 G1 | 136612118 | 6.5 | 360 | 370000.00 | 20221020 | {"monthly_reporting_period":20230820,"current_actual_upb":366923.6,"remaining_months_to_legal_maturi ... total length 175 | Other | 366923.60 |
| 7345126107333234134 | 0 | 735 | CAS 2023 R08 G1 | 136649774 | 7.125 | 360 | 284000.00 | 20220820 | {"monthly_reporting_period":20230820,"current_actual_upb":280901.84,"remaining_months_to_legal_matur ... total length 176 | Wells Fargo Bank, N.A. | 280901.84 |
| 2095797490938898800 | 6 | 780 | CAS 2023 R08 G1 | 136653220 | 6.625 | 360 | 253000.00 | 20221020 | {"monthly_reporting_period":20230820,"current_actual_upb":249839.53,"remaining_months_to_legal_matur ... total length 176 | Other | 249839.53 |
| 4381682178806700587 | 0 | 739 | CAS 2023 R08 G1 | 136637703 | 7.625 | 360 | 1013000.00 | 20221120 | {"monthly_reporting_period":20230820,"current_actual_upb":1006505.42,"remaining_months_to_legal_matu ... total length 177 | NewRez LLC | 1006505.42 |
| 8472024047843872395 | 0 | 781 | CAS 2023 R08 G1 | 136611546 | 5.125 | 360 | 412000.00 | 20221120 | {"monthly_reporting_period":20230820,"current_actual_upb":385685.84,"remaining_months_to_legal_matur ... total length 176 | Guaranteed Rate, Inc. | 385685.84 |
| 2014515344537304865 | 5 | 818 | CAS 2023 R08 G1 | 136618938 | 5.375 | 360 | 362000.00 | 20221020 | {"monthly_reporting_period":20230820,"current_actual_upb":158739.13,"remaining_months_to_legal_matur ... total length 176 | Guaranteed Rate, Inc. | 158739.13 |
| 5278786596005708792 | 8 | 795 | CAS 2023 R08 G1 | 136645760 | 4.99 | 360 | 295000.00 | 20221020 | {"monthly_reporting_period":20230820,"current_actual_upb":291740.39,"remaining_months_to_legal_matur ... total length 176 | PennyMac Loan Services, LLC | 291740.39 |
| 3282519401989431663 | 3 | 711 | CAS 2023 R08 G1 | 136662678 | 7.125 | 360 | 528000.00 | 20221120 | {"monthly_reporting_period":20230820,"current_actual_upb":524551.12,"remaining_months_to_legal_matur ... total length 176 | Guild Mortgage Company LLC | 524551.12 |
| 3715302605673428185 | 9 | 772 | CAS 2023 R08 G1 | 136647124 | 6.375 | 360 | 400000.00 | 20220920 | {"monthly_reporting_period":20230820,"current_actual_upb":396329.19,"remaining_months_to_legal_matur ... total length 176 | Rocket Mortgage, LLC | 396329.19 |
Total 60345 rows
{
"04_loan_smrs_clcltd": {
"type": "table_custom_tfm_table",
"custom_proc_type": "py_calc",
"desc": "Apply Python calculations to loan summaries",
"start_policy": "{manual_if_multi}",
"r": {
"table": "loan_payment_summaries",
"rowset_size": 1000,
"expected_batches_total": "{expected_batches|number}"
},
"p": {
"python_code_urls": [
"{dir_cfg}/py/payment_calc.py"
],
"calculated_fields": {
"sorted_payments_json": {
"expression": "sorted_payments_json(r.payments_json)",
"type": "string"
},
"payments_behind_ratio": {
"expression": "payments_behind_ratio(r.payments_json)",
"type": "float"
},
"paid_off_amount": {
"expression": "paid_off_amount(r.original_upb,r.upb_at_issuance,r.payments_json)",
"type": "decimal2"
},
"paid_off_ratio": {
"expression": "paid_off_ratio(r.original_upb,r.upb_at_issuance,r.payments_json)",
"type": "float"
}
}
},
"w": {
"name": "loan_smrs_clcltd",
"fields": {
"loan_id": {
"expression": "r.loan_id",
"type": "int"
},
"deal_name": {
"expression": "r.deal_name",
"type": "string"
},
"origination_date": {
"expression": "r.origination_date",
"type": "int"
},
"seller_name": {
"expression": "r.seller_name",
"type": "string"
},
"original_interest_rate": {
"expression": "r.original_interest_rate",
"type": "float"
},
"borrower_credit_score_at_origination": {
"expression": "r.borrower_credit_score_at_origination",
"type": "int"
},
"original_upb": {
"expression": "r.original_upb",
"type": "decimal2"
},
"upb_at_issuance": {
"expression": "r.upb_at_issuance",
"type": "decimal2"
},
"original_loan_term": {
"expression": "r.original_loan_term",
"type": "int"
},
"is_original_loan_term_30y": {
"expression": "int.iif(r.original_loan_term == 360, 1, 0)",
"type": "int"
},
"payments_json": {
"expression": "p.sorted_payments_json",
"type": "string"
},
"payments_behind_ratio": {
"expression": "p.payments_behind_ratio",
"type": "float"
},
"paid_off_amount": {
"expression": "decimal2(p.paid_off_amount)",
"type": "decimal2"
},
"paid_off_ratio": {
"expression": "p.paid_off_ratio",
"type": "float"
}
},
"indexes": {
"idx_loan_smrs_clcltd_deal_name": "non_unique(deal_name)",
"idx_loan_smrs_clcltd_deal_name_seller_name": "non_unique(deal_name,seller_name)"
}
}
}
}
| rowid | batch_idx | borrower_credit_score_at_origination | deal_name | is_original_loan_term_30y | loan_id | original_interest_rate | original_loan_term | original_upb | origination_date | paid_off_amount | paid_off_ratio | payments_behind_ratio | payments_json | seller_name | upb_at_issuance |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 603204304175406901 | 1 | 743 | CAS 2023 R08 G1 | 1 | 136660854 | 4.75 | 360 | 100000.00 | 20221020 | 1150.44 | 0.0115044 | 0 | [{"monthly_reporting_period": 20230820, "current_actual_upb": 98849.56, "remaining_months_to_legal_m ... total length 186 | Other | 98849.56 |
| 831208868887076987 | 6 | 791 | CAS 2023 R08 G1 | 1 | 136635500 | 7.125 | 360 | 252000.00 | 20221120 | 1857.36 | 0.0073704761905 | 0 | [{"monthly_reporting_period": 20230820, "current_actual_upb": 250142.64, "remaining_months_to_legal_ ... total length 187 | Flagstar Bank, National Association | 250142.64 |
| 7053664686515573330 | 9 | 806 | CAS 2023 R08 G1 | 1 | 136670778 | 6.625 | 360 | 296000.00 | 20221020 | 2677.36 | 0.0090451351351 | 0 | [{"monthly_reporting_period": 20230820, "current_actual_upb": 293322.64, "remaining_months_to_legal_ ... total length 187 | Other | 293322.64 |
| 1759235850494190894 | 9 | 702 | CAS 2023 R08 G1 | 1 | 136618278 | 6.375 | 360 | 403000.00 | 20221020 | 3927.38 | 0.0097453598015 | 0 | [{"monthly_reporting_period": 20230820, "current_actual_upb": 399072.62, "remaining_months_to_legal_ ... total length 187 | Rocket Mortgage, LLC | 399072.62 |
| 4576886964196765939 | 0 | 804 | CAS 2023 R08 G1 | 1 | 136618509 | 6.875 | 360 | 320000.00 | 20221020 | 2475.74 | 0.0077366875 | 0 | [{"monthly_reporting_period": 20230820, "current_actual_upb": 317524.26, "remaining_months_to_legal_ ... total length 187 | Other | 317524.26 |
| 8058973352069471630 | 3 | 754 | CAS 2023 R08 G1 | 1 | 136650930 | 5.875 | 360 | 578000.00 | 20220920 | 7137.56 | 0.0123487197232 | 0 | [{"monthly_reporting_period": 20230820, "current_actual_upb": 570862.44, "remaining_months_to_legal_ ... total length 187 | Other | 570862.44 |
| 234237901939414011 | 5 | 774 | CAS 2023 R08 G1 | 1 | 136630013 | 6.875 | 360 | 278000.00 | 20221120 | 2547.66 | 0.0091642446043 | 0 | [{"monthly_reporting_period": 20230820, "current_actual_upb": 275452.34, "remaining_months_to_legal_ ... total length 187 | United Wholesale Mortgage, LLC | 275452.34 |
| 2884255536010915568 | 5 | 739 | CAS 2023 R08 G1 | 1 | 136624837 | 5.125 | 360 | 364000.00 | 20220920 | 4356.55 | 0.011968543956 | 0 | [{"monthly_reporting_period": 20230820, "current_actual_upb": 359643.45, "remaining_months_to_legal_ ... total length 187 | Other | 359643.45 |
| 3111210525372372631 | 8 | 680 | CAS 2023 R08 G1 | 1 | 136614971 | 7.25 | 360 | 188000.00 | 20221120 | 12196.51 | 0.0648750531915 | 0 | [{"monthly_reporting_period": 20230820, "current_actual_upb": 175803.49, "remaining_months_to_legal_ ... total length 187 | United Wholesale Mortgage, LLC | 175803.49 |
| 8713878476140162668 | 7 | 703 | CAS 2023 R08 G1 | 1 | 136652127 | 6.5 | 360 | 113000.00 | 20221020 | 542.93 | 0.0048046902655 | 0 | [{"monthly_reporting_period": 20230820, "current_actual_upb": 112457.07, "remaining_months_to_legal_ ... total length 187 | Other | 112457.07 |
Total 60345 rows
{
"05_deal_seller_summaries": {
"type": "table_lookup_table",
"desc": "For each deal/seller, calculate aggregates from calculated loan summaries",
"start_policy": "{manual_if_multi}",
"rerun_policy": "fail",
"r": {
"table": "deal_sellers",
"expected_batches_total": "{expected_batches|number}"
},
"l": {
"index_name": "idx_loan_smrs_clcltd_deal_name_seller_name",
"join_on": "r.deal_name,r.seller_name",
"group": true,
"join_type": "left"
},
"w": {
"name": "deal_seller_summaries",
"fields": {
"deal_name": {
"expression": "r.deal_name",
"type": "string"
},
"seller_name": {
"expression": "r.seller_name",
"type": "string"
},
"avg_original_interest_rate": {
"expression": "avg(l.original_interest_rate)",
"type": "float"
},
"min_original_interest_rate": {
"expression": "min(l.original_interest_rate)",
"type": "float"
},
"max_original_interest_rate": {
"expression": "max(l.original_interest_rate)",
"type": "float"
},
"avg_borrower_credit_score_at_origination": {
"expression": "avg(float(l.borrower_credit_score_at_origination))",
"type": "float"
},
"min_borrower_credit_score_at_origination": {
"expression": "min(l.borrower_credit_score_at_origination)",
"type": "int"
},
"max_borrower_credit_score_at_origination": {
"expression": "max(l.borrower_credit_score_at_origination)",
"type": "int"
},
"total_original_upb": {
"expression": "sum(l.original_upb)",
"type": "decimal2"
},
"total_upb_at_issuance": {
"expression": "sum(l.upb_at_issuance)",
"type": "decimal2"
},
"total_loans": {
"expression": "count()",
"type": "int"
},
"total_original_loan_term_30y": {
"expression": "sum(l.is_original_loan_term_30y)",
"type": "int"
},
"avg_payments_behind_ratio": {
"expression": "avg(l.payments_behind_ratio)",
"type": "float"
},
"total_paid_off_amount": {
"expression": "sum(l.paid_off_amount)",
"type": "decimal2"
},
"avg_paid_off_ratio": {
"expression": "avg(l.paid_off_ratio)",
"type": "float"
}
}
}
}
}
| rowid | avg_borrower_credit_score_at_origination | avg_original_interest_rate | avg_paid_off_ratio | avg_payments_behind_ratio | batch_idx | deal_name | max_borrower_credit_score_at_origination | max_original_interest_rate | min_borrower_credit_score_at_origination | min_original_interest_rate | seller_name | total_loans | total_original_loan_term_30y | total_original_upb | total_paid_off_amount | total_upb_at_issuance |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2605030512062607645 | 761.10300081103 | 5.938240064882 | 0.0253620346696 | 0 | 7 | CAS 2023 R08 G1 | 823 | 7.625 | 0 | 2.99 | Guaranteed Rate, Inc. | 1233 | 1233 | 443720000.00 | 12278921.05 | 431441078.95 |
| 7681190877932109215 | 749.657512116317 | 6.008479806139 | 0.0180495347051 | 0 | 2 | CAS 2023 R08 G1 | 821 | 7.625 | 620 | 4.25 | Lakeview Loan Servicing, LLC | 619 | 612 | 231574000.00 | 4177208.66 | 227396791.34 |
| 3666906138091569880 | 748.060203283815 | 6.451825645035 | 0.0201185425195 | 0 | 7 | CAS 2023 R08 G1 | 824 | 7.99 | 0 | 2.75 | Planet Home Lending, LLC | 1279 | 1277 | 432167000.00 | 8169068.41 | 423997931.59 |
| 2717906083198624711 | 756.492569002123 | 5.7203492569 | 0.0276388077482 | 0 | 2 | CAS 2023 R08 G1 | 823 | 7.625 | 0 | 3.99 | Fifth Third Bank, National Association | 942 | 941 | 285578000.00 | 8575728.03 | 277002271.97 |
| 744404163417716791 | 764.380952380952 | 5.763708293914 | 0.0267600623223 | 0 | 4 | CAS 2023 R08 G1 | 828 | 7.625 | 0 | 2.75 | Wells Fargo Bank, N.A. | 3171 | 3166 | 1115305000.00 | 32102763.26 | 1083202236.74 |
| 4082150750686757249 | 751.008688097307 | 5.941403127715 | 0.0186537553046 | 0 | 1 | CAS 2023 R08 G1 | 821 | 8.125 | 620 | 3 | loanDepot.com, LLC | 1151 | 1151 | 388120000.00 | 7360117.11 | 380759882.89 |
| 8132355452051647433 | 736.685932388222 | 6.311190839695 | 0.0179177539925 | 0 | 5 | CAS 2023 R08 G1 | 820 | 8.125 | 601 | 4.25 | Nationstar Mortgage LLC | 917 | 913 | 266952000.00 | 5065464.57 | 261886535.43 |
| 292831738858778117 | 753.40875 | 6.69331875 | 0.0216680045482 | 0 | 3 | CAS 2023 R08 G1 | 819 | 8.125 | 0 | 3.5 | CrossCountry Mortgage, LLC | 800 | 798 | 237075000.00 | 5715230.05 | 231359769.95 |
| 6183503794039805140 | 753.587826086957 | 6.534476521739 | 0.019365020949 | 0 | 9 | CAS 2023 R08 G1 | 824 | 8.125 | 623 | 3.75 | Flagstar Bank, National Association | 575 | 575 | 194992000.00 | 4062149.92 | 190929850.08 |
| 3592591457388772981 | 754.732716227943 | 6.51039282615 | 0.018578250003 | 0 | 2 | CAS 2023 R08 G1 | 829 | 8.125 | 620 | 4.125 | United Wholesale Mortgage, LLC | 3457 | 3452 | 1148073000.00 | 22176379.26 | 1125896620.74 |
Total 25 rows
{
"05_deal_summaries": {
"type": "table_lookup_table",
"desc": "For each deal, calculate aggregates from calculated loan summaries",
"start_policy": "{manual_if_multi}",
"r": {
"table": "deal_total_upbs",
"expected_batches_total": "{expected_batches|number}"
},
"l": {
"index_name": "idx_loan_smrs_clcltd_deal_name",
"join_on": "r.deal_name",
"group": true,
"join_type": "left"
},
"w": {
"name": "deal_summaries",
"fields": {
"deal_name": {
"expression": "r.deal_name",
"type": "string"
},
"wa_original_interest_rate_by_original_upb": {
"expression": "sum(l.original_interest_rate * float(l.original_upb))",
"type": "float"
},
"min_original_interest_rate": {
"expression": "min_if(l.original_interest_rate, l.original_interest_rate > 0)",
"type": "float"
},
"max_original_interest_rate": {
"expression": "max(l.original_interest_rate)",
"type": "float"
},
"wa_borrower_credit_score_at_origination_by_original_upb": {
"expression": "sum(float(l.borrower_credit_score_at_origination) * float(l.original_upb))",
"type": "float"
},
"min_borrower_credit_score_at_origination": {
"expression": "min_if(l.borrower_credit_score_at_origination, l.borrower_credit_score_at_origination > 0)",
"type": "int"
},
"max_borrower_credit_score_at_origination": {
"expression": "max(l.borrower_credit_score_at_origination)",
"type": "int"
},
"total_original_upb": {
"expression": "r.total_original_upb",
"type": "decimal2"
},
"total_upb_at_issuance": {
"expression": "r.total_upb_at_issuance",
"type": "decimal2"
},
"total_original_upb_for_nonzero_rates": {
"expression": "r.total_original_upb_for_nonzero_rates",
"type": "decimal2"
},
"total_original_upb_for_nonzero_credit_scores": {
"expression": "r.total_original_upb_for_nonzero_credit_scores",
"type": "decimal2"
},
"total_loans": {
"expression": "count()",
"type": "int"
},
"total_original_loan_term_30y": {
"expression": "sum(l.is_original_loan_term_30y)",
"type": "int"
},
"avg_payments_behind_ratio": {
"expression": "avg(l.payments_behind_ratio)",
"type": "float"
},
"total_paid_off_amount": {
"expression": "sum(l.paid_off_amount)",
"type": "decimal2"
},
"avg_paid_off_ratio": {
"expression": "avg(l.paid_off_ratio)",
"type": "float"
}
}
}
}
}
| rowid | avg_paid_off_ratio | avg_payments_behind_ratio | batch_idx | deal_name | max_borrower_credit_score_at_origination | max_original_interest_rate | min_borrower_credit_score_at_origination | min_original_interest_rate | total_loans | total_original_loan_term_30y | total_original_upb | total_original_upb_for_nonzero_credit_scores | total_original_upb_for_nonzero_rates | total_paid_off_amount | total_upb_at_issuance | wa_borrower_credit_score_at_origination_by_original_upb | wa_original_interest_rate_by_original_upb |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 6121652427606545559 | 0.0231112037748 | 0 | 4 | CAS 2023 R08 G1 | 832 | 8.125 | 600 | 2.5 | 60345 | 60114 | 19356281000.00 | 19339292000.00 | 19356281000.00 | 476091393.37 | 18880190518.06 | 1.4670429761e+13 | 118134224964 |
Total 1 rows
{
"04_write_file_loan_smrs_clcltd": {
"type": "table_file",
"desc": "Write from table to file loan_summaries.parquet",
"r": {
"table": "loan_smrs_clcltd"
},
"w": {
"top": {
"order": "loan_id(asc)"
},
"url_template": "{dir_out}/loan_smrs_clcltd.parquet",
"columns": [
{
"parquet": {
"column_name": "loan_id"
},
"name": "loan_id",
"expression": "r.loan_id",
"type": "int"
},
{
"parquet": {
"column_name": "deal_name"
},
"name": "deal_name",
"expression": "r.deal_name",
"type": "string"
},
{
"parquet": {
"column_name": "seller_name"
},
"name": "seller_name",
"expression": "r.seller_name",
"type": "string"
},
{
"parquet": {
"column_name": "original_interest_rate"
},
"name": "original_interest_rate",
"expression": "r.original_interest_rate",
"type": "float"
},
{
"parquet": {
"column_name": "borrower_credit_score_at_origination"
},
"name": "borrower_credit_score_at_origination",
"expression": "r.borrower_credit_score_at_origination",
"type": "int"
},
{
"parquet": {
"column_name": "origination_date"
},
"name": "origination_date",
"expression": "r.origination_date",
"type": "int"
},
{
"parquet": {
"column_name": "original_upb"
},
"name": "original_upb",
"expression": "r.original_upb",
"type": "decimal2"
},
{
"parquet": {
"column_name": "upb_at_issuance"
},
"name": "upb_at_issuance",
"expression": "r.upb_at_issuance",
"type": "decimal2"
},
{
"parquet": {
"column_name": "original_loan_term"
},
"name": "original_loan_term",
"expression": "r.original_loan_term",
"type": "int"
},
{
"parquet": {
"column_name": "payments_json"
},
"name": "payments_json",
"expression": "r.payments_json",
"type": "string"
},
{
"parquet": {
"column_name": "payments_behind_ratio"
},
"name": "payments_behind_ratio",
"expression": "math.Round(r.payments_behind_ratio*100000)/100000",
"type": "float"
},
{
"parquet": {
"column_name": "paid_off_amount"
},
"name": "paid_off_amount",
"expression": "r.paid_off_amount",
"type": "decimal2"
},
{
"parquet": {
"column_name": "paid_off_ratio"
},
"name": "paid_off_ratio",
"expression": "math.Round(r.paid_off_ratio*100000)/100000",
"type": "float"
}
]
}
}
}
| loan_id | deal_name | seller_name | original_interest_rate | borrower_credit_score_at_origination | origination_date | original_upb | upb_at_issuance | original_loan_term | payments_json | payments_behind_ratio | paid_off_amount | paid_off_ratio | ||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 136610574 | CAS 2023 R08 G1 | Flagstar Bank, National Association | 6.875 | 735 | 20221020 | 490000 | 485775.65 | 360 | [{monthly_reporting_period": 20230820 | "current_actual_upb": 485775.65 | "remaining_months_to_legal_maturity": 351 | "remaining_months_to_maturity": 350 | "zero_balance_effective_date": 0}]" | 0 | 4224.35 | 0.00862 |
| 136610575 | CAS 2023 R08 G1 | Planet Home Lending, LLC | 6.375 | 709 | 20221020 | 250000 | 248112.85 | 360 | [{monthly_reporting_period": 20230820 | "current_actual_upb": 248112.85 | "remaining_months_to_legal_maturity": 352 | "remaining_months_to_maturity": 352 | "zero_balance_effective_date": 0}]" | 0 | 1887.15 | 0.00755 |
| 136610576 | CAS 2023 R08 G1 | Rocket Mortgage, LLC | 6.5 | 787 | 20221020 | 473000 | 468571.34 | 360 | [{monthly_reporting_period": 20230820 | "current_actual_upb": 468571.34 | "remaining_months_to_legal_maturity": 351 | "remaining_months_to_maturity": 351 | "zero_balance_effective_date": 0}]" | 0 | 4428.66 | 0.00936 |
| 136610577 | CAS 2023 R08 G1 | Rocket Mortgage, LLC | 5.25 | 732 | 20220920 | 311000 | 307534.77 | 360 | [{monthly_reporting_period": 20230820 | "current_actual_upb": 307534.77 | "remaining_months_to_legal_maturity": 351 | "remaining_months_to_maturity": 351 | "zero_balance_effective_date": 0}]" | 0 | 3465.23 | 0.01114 |
| 136610578 | CAS 2023 R08 G1 | U.S. Bank N.A. | 5.5 | 763 | 20221020 | 352000 | 348025.67 | 360 | [{monthly_reporting_period": 20230820 | "current_actual_upb": 348025.67 | "remaining_months_to_legal_maturity": 351 | "remaining_months_to_maturity": 350 | "zero_balance_effective_date": 0}]" | 0 | 3974.33 | 0.01129 |
| 136610579 | CAS 2023 R08 G1 | Other | 6 | 720 | 20220920 | 142000 | 139258.92 | 360 | [{monthly_reporting_period": 20230820 | "current_actual_upb": 139258.92 | "remaining_months_to_legal_maturity": 351 | "remaining_months_to_maturity": 339 | "zero_balance_effective_date": 0}]" | 0 | 2741.08 | 0.0193 |
| 136610580 | CAS 2023 R08 G1 | U.S. Bank N.A. | 5.875 | 799 | 20221020 | 205000 | 202982.62 | 360 | [{monthly_reporting_period": 20230820 | "current_actual_upb": 202982.62 | "remaining_months_to_legal_maturity": 351 | "remaining_months_to_maturity": 351 | "zero_balance_effective_date": 0}]" | 0 | 2017.38 | 0.00984 |
| 136610581 | CAS 2023 R08 G1 | Planet Home Lending, LLC | 6.575 | 690 | 20221020 | 174000 | 172686.14 | 360 | [{monthly_reporting_period": 20230820 | "current_actual_upb": 172686.14 | "remaining_months_to_legal_maturity": 352 | "remaining_months_to_maturity": 352 | "zero_balance_effective_date": 0}]" | 0 | 1313.86 | 0.00755 |
| 136610582 | CAS 2023 R08 G1 | Other | 5.375 | 762 | 20221020 | 116000 | 110751.32 | 360 | [{monthly_reporting_period": 20230820 | "current_actual_upb": 110751.32 | "remaining_months_to_legal_maturity": 351 | "remaining_months_to_maturity": 323 | "zero_balance_effective_date": 0}]" | 0 | 5248.68 | 0.04525 |
| 136610583 | CAS 2023 R08 G1 | JPMorgan Chase Bank, National Association | 5.5 | 795 | 20220920 | 234000 | 231756.02 | 360 | [{monthly_reporting_period": 20230820 | "current_actual_upb": 231756.02 | "remaining_months_to_legal_maturity": 350 | "remaining_months_to_maturity": 350 | "zero_balance_effective_date": 0}]" | 0 | 2243.98 | 0.00959 |
Total 60345 rows
{
"05_write_file_deal_seller_summaries": {
"type": "table_file",
"desc": "Write from table to file deal_seller_summaries.parquet",
"r": {
"table": "deal_seller_summaries"
},
"w": {
"top": {
"order": "deal_name(asc),seller_name(asc)"
},
"url_template": "{dir_out}/deal_seller_summaries.parquet",
"columns": [
{
"parquet": {
"column_name": "deal_name"
},
"name": "deal_name",
"expression": "r.deal_name",
"type": "string"
},
{
"parquet": {
"column_name": "seller_name"
},
"name": "seller_name",
"expression": "r.seller_name",
"type": "string"
},
{
"parquet": {
"column_name": "avg_original_interest_rate"
},
"name": "avg_original_interest_rate",
"expression": "math.Round(r.avg_original_interest_rate*1000)/1000",
"type": "float"
},
{
"parquet": {
"column_name": "min_original_interest_rate"
},
"name": "min_original_interest_rate",
"expression": "r.min_original_interest_rate",
"type": "float"
},
{
"parquet": {
"column_name": "max_original_interest_rate"
},
"name": "max_original_interest_rate",
"expression": "r.max_original_interest_rate",
"type": "float"
},
{
"parquet": {
"column_name": "avg_borrower_credit_score_at_origination"
},
"name": "avg_borrower_credit_score_at_origination",
"expression": "math.Round(r.avg_borrower_credit_score_at_origination*100)/100",
"type": "float"
},
{
"parquet": {
"column_name": "min_borrower_credit_score_at_origination"
},
"name": "min_borrower_credit_score_at_origination",
"expression": "r.min_borrower_credit_score_at_origination",
"type": "int"
},
{
"parquet": {
"column_name": "max_borrower_credit_score_at_origination"
},
"name": "max_borrower_credit_score_at_origination",
"expression": "r.max_borrower_credit_score_at_origination",
"type": "int"
},
{
"parquet": {
"column_name": "total_original_upb"
},
"name": "total_original_upb",
"expression": "r.total_original_upb",
"type": "decimal2"
},
{
"parquet": {
"column_name": "total_original_upb_paid_off_ratio"
},
"name": "total_original_upb_paid_off_ratio",
"expression": "math.Round(float(r.total_paid_off_amount)/float(r.total_original_upb)*100000)/100000",
"type": "float"
},
{
"parquet": {
"column_name": "total_upb_at_issuance"
},
"name": "total_upb_at_issuance",
"expression": "r.total_upb_at_issuance",
"type": "decimal2"
},
{
"parquet": {
"column_name": "total_loans"
},
"name": "total_loans",
"expression": "r.total_loans",
"type": "int"
},
{
"parquet": {
"column_name": "total_original_loan_term_30y"
},
"name": "total_original_loan_term_30y",
"expression": "r.total_original_loan_term_30y",
"type": "int"
},
{
"parquet": {
"column_name": "avg_payments_behind_ratio"
},
"name": "avg_payments_behind_ratio",
"expression": "math.Round(r.avg_payments_behind_ratio*100000)/100000",
"type": "float"
},
{
"parquet": {
"column_name": "total_paid_off_amount"
},
"name": "total_paid_off_amount",
"expression": "r.total_paid_off_amount",
"type": "decimal2"
},
{
"parquet": {
"column_name": "avg_paid_off_ratio"
},
"name": "avg_paid_off_ratio",
"expression": "math.Round(r.avg_paid_off_ratio*100000)/100000",
"type": "float"
}
]
}
}
}
| deal_name | seller_name | avg_original_interest_rate | min_original_interest_rate | max_original_interest_rate | avg_borrower_credit_score_at_origination | min_borrower_credit_score_at_origination | max_borrower_credit_score_at_origination | total_original_upb | total_original_upb_paid_off_ratio | total_upb_at_issuance | total_loans | total_original_loan_term_30y | avg_payments_behind_ratio | total_paid_off_amount | avg_paid_off_ratio |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| CAS 2023 R08 G1 | CitiMortgage, Inc. | 5.552 | 3.25 | 7.125 | 765.14 | 624 | 820 | 276412000 | 0.02568 | 269314771.16 | 746 | 745 | 0 | 7097228.84 | 0.02411 |
| CAS 2023 R08 G1 | CrossCountry Mortgage, LLC | 6.693 | 3.5 | 8.125 | 753.41 | 0 | 819 | 237075000 | 0.02411 | 231359769.95 | 800 | 798 | 0 | 5715230.05 | 0.02167 |
| CAS 2023 R08 G1 | DHI Mortgage Company, Ltd. | 4.885 | 3.25 | 7.375 | 758.65 | 0 | 827 | 268469000 | 0.02285 | 262333547.56 | 857 | 857 | 0 | 6135452.44 | 0.02289 |
| CAS 2023 R08 G1 | Fairway Independent Mortgage Corporation | 6.344 | 3.75 | 7.625 | 757.35 | 0 | 829 | 457905000 | 0.02773 | 445205133.09 | 1357 | 1355 | 0 | 12699866.91 | 0.02525 |
| CAS 2023 R08 G1 | Fifth Third Bank, National Association | 5.72 | 3.99 | 7.625 | 756.49 | 0 | 823 | 285578000 | 0.03003 | 277002271.97 | 942 | 941 | 0 | 8575728.03 | 0.02764 |
| CAS 2023 R08 G1 | Flagstar Bank, National Association | 6.534 | 3.75 | 8.125 | 753.59 | 623 | 824 | 194992000 | 0.02083 | 190929850.08 | 575 | 575 | 0 | 4062149.92 | 0.01937 |
| CAS 2023 R08 G1 | Guaranteed Rate, Inc. | 5.938 | 2.99 | 7.625 | 761.1 | 0 | 823 | 443720000 | 0.02767 | 431441078.95 | 1233 | 1233 | 0 | 12278921.05 | 0.02536 |
| CAS 2023 R08 G1 | Guild Mortgage Company LLC | 6.62 | 4.25 | 8 | 760.82 | 0 | 821 | 233925000 | 0.02983 | 226947065.53 | 685 | 685 | 0 | 6977934.47 | 0.02757 |
| CAS 2023 R08 G1 | JPMorgan Chase Bank, National Association | 6.217 | 3.5 | 8.125 | 759.27 | 620 | 823 | 253524000 | 0.0362 | 244345711.66 | 894 | 884 | 0 | 9178398.2 | 0.03168 |
| CAS 2023 R08 G1 | Lakeview Loan Servicing, LLC | 6.008 | 4.25 | 7.625 | 749.66 | 620 | 821 | 231574000 | 0.01804 | 227396791.34 | 619 | 612 | 0 | 4177208.66 | 0.01805 |
Total 25 rows
{
"05_write_file_deal_summaries": {
"type": "table_file",
"desc": "Write from table to file deal_summaries.parquet",
"r": {
"table": "deal_summaries"
},
"w": {
"top": {
"order": "deal_name(asc)"
},
"url_template": "{dir_out}/deal_summaries.parquet",
"columns": [
{
"parquet": {
"column_name": "deal_name"
},
"name": "deal_name",
"expression": "r.deal_name",
"type": "string"
},
{
"parquet": {
"column_name": "wa_original_interest_rate_by_original_upb"
},
"name": "wa_original_interest_rate_by_original_upb",
"expression": "math.Round(r.wa_original_interest_rate_by_original_upb/r.total_original_upb_for_nonzero_rates*1000)/1000",
"type": "float"
},
{
"parquet": {
"column_name": "min_original_interest_rate"
},
"name": "min_original_interest_rate",
"expression": "r.min_original_interest_rate",
"type": "float"
},
{
"parquet": {
"column_name": "max_original_interest_rate"
},
"name": "max_original_interest_rate",
"expression": "r.max_original_interest_rate",
"type": "float"
},
{
"parquet": {
"column_name": "wa_borrower_credit_score_at_origination_by_original_upb"
},
"name": "wa_borrower_credit_score_at_origination_by_original_upb",
"expression": "math.Round(r.wa_borrower_credit_score_at_origination_by_original_upb/r.total_original_upb_for_nonzero_credit_scores*100)/100",
"type": "float"
},
{
"parquet": {
"column_name": "min_borrower_credit_score_at_origination"
},
"name": "min_borrower_credit_score_at_origination",
"expression": "r.min_borrower_credit_score_at_origination",
"type": "int"
},
{
"parquet": {
"column_name": "max_borrower_credit_score_at_origination"
},
"name": "max_borrower_credit_score_at_origination",
"expression": "r.max_borrower_credit_score_at_origination",
"type": "int"
},
{
"parquet": {
"column_name": "total_original_upb"
},
"name": "total_original_upb",
"expression": "r.total_original_upb",
"type": "decimal2"
},
{
"parquet": {
"column_name": "total_original_upb_paid_off_ratio"
},
"name": "total_original_upb_paid_off_ratio",
"expression": "math.Round(float(r.total_paid_off_amount)/float(r.total_original_upb)*100000)/100000",
"type": "float"
},
{
"parquet": {
"column_name": "total_upb_at_issuance"
},
"name": "total_upb_at_issuance",
"expression": "r.total_upb_at_issuance",
"type": "decimal2"
},
{
"parquet": {
"column_name": "total_loans"
},
"name": "total_loans",
"expression": "r.total_loans",
"type": "int"
},
{
"parquet": {
"column_name": "total_original_loan_term_30y"
},
"name": "total_original_loan_term_30y",
"expression": "r.total_original_loan_term_30y",
"type": "int"
},
{
"parquet": {
"column_name": "avg_payments_behind_ratio"
},
"name": "avg_payments_behind_ratio",
"expression": "math.Round(r.avg_payments_behind_ratio*100000)/100000",
"type": "float"
},
{
"parquet": {
"column_name": "total_paid_off_amount"
},
"name": "total_paid_off_amount",
"expression": "r.total_paid_off_amount",
"type": "decimal2"
},
{
"parquet": {
"column_name": "avg_paid_off_ratio"
},
"name": "avg_paid_off_ratio",
"expression": "math.Round(r.avg_paid_off_ratio*100000)/100000",
"type": "float"
}
]
}
}
}
| deal_name | wa_original_interest_rate_by_original_upb | min_original_interest_rate | max_original_interest_rate | wa_borrower_credit_score_at_origination_by_original_upb | min_borrower_credit_score_at_origination | max_borrower_credit_score_at_origination | total_original_upb | total_original_upb_paid_off_ratio | total_upb_at_issuance | total_loans | total_original_loan_term_30y | avg_payments_behind_ratio | total_paid_off_amount | avg_paid_off_ratio |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| CAS 2023 R08 G1 | 6.103 | 2.5 | 8.125 | 758.58 | 600 | 832 | 19356281000 | 0.0246 | 18880190518.06 | 60345 | 60114 | 0 | 476091393.37 | 0.02311 |
Total 1 rows