This SQL query executes an analysis on a table, likely...

September 12, 2025 at 06:17 PM

select
'BK',
CountID = count(*),
Balance = sum(Prinbal),	
FICO = sum(case when FICO >0 then Prinbal else 0 end * FICO)/nullif(sum(case when FICO >0 then Prinbal else 0 end),0),
APR =nullif( sum(Prinbal * CURRRATE),0)/nullif(sum(Prinbal),0),
[Zero APR] = nullif( sum(case when CURRRATE = 0 then Prinbal end),0)/nullif(sum(Prinbal),0),					
LTV = 100*nullif( sum(Prinbal *  LTV),0)/nullif(sum(Prinbal),0),
[LTV<100] = nullif( sum(case when 100*LTV <100 then Prinbal end),0)/nullif(sum(Prinbal),0),
[LTV 100 to 120] = nullif( sum(case when 100*LTV>=100 and 100*LTV <=120 then Prinbal end),0)/nullif(sum(Prinbal),0),
[LTV>120] = nullif( sum(case when 100*LTV > 120 then Prinbal end),0)/nullif(sum(Prinbal),0),
[Original Term] = nullif( sum(Prinbal *  ORIGTERM),0)/nullif(sum(Prinbal),0),
[Remaining Term] = nullif( sum(Prinbal *  REMTERM),0)/nullif(sum(Prinbal),0),
[Term 84] = nullif( sum(case when ORIGTERM = 84 then Prinbal end),0)/nullif(sum(Prinbal),0),
New = nullif( sum(case when NewUsed = 'New' then Prinbal end),0)/nullif(sum(Prinbal),0),
DQ = nullif( sum(case when DAYSDEL >30 then Prinbal end),0)/nullif(sum(Prinbal),0),
CNL = -sum(PM41_Net_Loss_Node_adj)/sum(AMTFIN)
from [Sandbox_Finance].[CapitalMarkets].SC_SBNA_Unencumbered_NPL_20250831
where BK = 'Yes'
union
select
'DQ',
CountID = count(*),
Balance = sum(Prinbal),	
FICO = sum(case when FICO >0 then Prinbal else 0 end * FICO)/nullif(sum(case when FICO >0 then Prinbal else 0 end),0),
APR =nullif( sum(Prinbal * CURRRATE),0)/nullif(sum(Prinbal),0),
[Zero APR] = nullif( sum(case when CURRRATE = 0 then Prinbal end),0)/nullif(sum(Prinbal),0),					
LTV = 100*nullif( sum(Prinbal *  LTV),0)/nullif(sum(Prinbal),0),
[LTV<100] = nullif( sum(case when 100*LTV <100 then Prinbal end),0)/nullif(sum(Prinbal),0),
[LTV 100 to 120] = nullif( sum(case when 100*LTV>=100 and 100*LTV <=120 then Prinbal end),0)/nullif(sum(Prinbal),0),
[LTV>120] = nullif( sum(case when 100*LTV > 120 then Prinbal end),0)/nullif(sum(Prinbal),0),
[Original Term] = nullif( sum(Prinbal *  ORIGTERM),0)/nullif(sum(Prinbal),0),
[Remaining Term] = nullif( sum(Prinbal *  REMTERM),0)/nullif(sum(Prinbal),0),
[Term 84] = nullif( sum(case when ORIGTERM = 84 then Prinbal end),0)/nullif(sum(Prinbal),0),
New = nullif( sum(case when NewUsed = 'New' then Prinbal end),0)/nullif(sum(Prinbal),0),
DQ = nullif( sum(case when DAYSDEL >30 then Prinbal end),0)/nullif(sum(Prinbal),0),
CNL = -sum(PM41_Net_Loss_Node_adj)/sum(AMTFIN)
from [Sandbox_Finance].[CapitalMarkets].SC_SBNA_Unencumbered_NPL_20250831
where DAYSDEL > 30 
union
select
'Repo',
CountID = count(*),
Balance = sum(Prinbal),	
FICO = sum(case when FICO >0 then Prinbal else 0 end * FICO)/nullif(sum(case when FICO >0 then Prinbal else 0 end),0),
APR =nullif( sum(Prinbal * CURRRATE),0)/nullif(sum(Prinbal),0),
[Zero APR] = nullif( sum(case when CURRRATE = 0 then Prinbal end),0)/nullif(sum(Prinbal),0),					
LTV = 100*nullif( sum(Prinbal *  LTV),0)/nullif(sum(Prinbal),0),
[LTV<100] = nullif( sum(case when 100*LTV <100 then Prinbal end),0)/nullif(sum(Prinbal),0),
[LTV 100 to 120] = nullif( sum(case when 100*LTV>=100 and 100*LTV <=120 then Prinbal end),0)/nullif(sum(Prinbal),0),
[LTV>120] = nullif( sum(case when 100*LTV > 120 then Prinbal end),0)/nullif(sum(Prinbal),0),
[Original Term] = nullif( sum(Prinbal *  ORIGTERM),0)/nullif(sum(Prinbal),0),
[Remaining Term] = nullif( sum(Prinbal *  REMTERM),0)/nullif(sum(Prinbal),0),
[Term 84] = nullif( sum(case when ORIGTERM = 84 then Prinbal end),0)/nullif(sum(Prinbal),0),
New = nullif( sum(case when NewUsed = 'New' then Prinbal end),0)/nullif(sum(Prinbal),0),
DQ = nullif( sum(case when DAYSDEL >30 then Prinbal end),0)/nullif(sum(Prinbal),0),
CNL = -sum(PM41_Net_Loss_Node_adj)/sum(AMTFIN)
from [Sandbox_Finance].[CapitalMarkets].SC_SBNA_Unencumbered_NPL_20250831
where cRecoveryStatus <> '0' 
order by sum(Prinbal) desc

This SQL query executes an analysis on a table, likely related to financial or loan data, and creates a unified result set by combining the results from three different conditions (BK, DQ, Repo). Here's a breakdown of what the code does:

1. General Structure

The query uses three SELECT statements combined with UNION.
Each SELECT calculates aggregations and computes derived statistics.
Conditions (where BK = 'Yes', where DAYSDEL > 30, and where cRecoveryStatus <> '0') filter the data in each part of the UNION.
Finally, the result set is ordered in descending order of the total principal balance (sum(Prinbal)).

2. Columns Computed

Each SELECT statement computes the following columns:

Identifier ('BK', 'DQ', 'Repo'):
- A label corresponding to each part of the union.
CountID:
- The count of rows (count(*)) in the filtered dataset for the specific condition.
Balance:
- Sum of Prinbal (which likely stands for "principal balance").
FICO:
- A weighted average of FICO scores for loans where FICO > 0, weighted by Prinbal.
APR:
- Average interest rate, computed as the sum of principal-weighted CURRRATE divided by the sum of Prinbal.
[Zero APR]:
- The proportion of loans with CURRRATE = 0 compared to the total Prinbal.
LTV:
- Loan-to-value ratio, scaled by multiplying by 100.
LTV Brackets:
- Proportions of loans falling into different LTV brackets:
  - [LTV<100]: Loans with 100 * LTV < 100.
  - [LTV 100 to 120]: Loans with 100 * LTV between 100 and 120.
  - [LTV>120]: Loans with 100 * LTV > 120.
[Original Term] & [Remaining Term]:
- Weighted averages (based on Prinbal) of the loan's original term (ORIGTERM) and remaining term (REMTERM), respectively.
[Term 84]:
- Proportion of loans with an original term of exactly 84.
New:
- Proportion of loans designated as "new" (NewUsed = 'New').
DQ:
- Proportion of delinquent loans (DAYSDEL > 30).
CNL (Cumulative Net Loss):
- A calculation for cumulative net loss, computed as -sum(PM41_Net_Loss_Node_adj) / sum(AMTFIN).

3. Filters in Each Part of the UNION

First Block (BK):
- Filters where BK equals 'Yes'.
Second Block (DQ):
- Filters where DAYSDEL > 30 (indicating delinquent loans).
Third Block (Repo):
- Filters where cRecoveryStatus <> '0' (likely repossessed loans).

4. Final Output

Combines the aggregated results from the three conditions (BK, DQ, Repo) into a single result set.
Orders the rows based on sum(Prinbal) (total principal balance) in descending order.

5. Summary

The code aggregates loan or financial data into several key metrics for three distinct data subsets (e.g., 'Bankrupt' (BK), 'Delinquent' (DQ), and 'Repossession' (Repo)). Metrics include count of loans, total/principal balances, averages, proportions, and weighted averages for various financial attributes such as FICO scores, APR, loan-to-value ratios, terms, and delinquency. The results are displayed in a unified table ordered by the overall balance for easier comparative analysis.

Generate your own explanations

Download our vscode extension

Read other generated explanations

Built by @thebuilderjr