Task
You are given a client report built from Moody's credit data that is returning NULL for specific credit metrics. Write a PostgreSQL query that identifies which report rows are missing metric values, shows the related entity and metric name, and flags whether the null came from the source metric table or from a failed match to the metric reference table.
Schema
| table | column | type | description |
|---|
| client_reports | report_id | INT | Primary key for the report row |
| client_reports | entity_id | INT | Entity being reported on |
| client_reports | metric_code | VARCHAR(20) | Metric requested in the report |
| client_reports | report_date | DATE | Report date |
| client_reports | metric_value | NUMERIC(12,4) | Final metric value shown in the report |
| credit_metrics | entity_id | INT | Entity identifier |
| credit_metrics | metric_code | VARCHAR(20) | Metric code in the source system |
| credit_metrics | metric_value | NUMERIC(12,4) | Source metric value |
| credit_metrics | as_of_date | DATE | Date the metric was captured |
| metric_reference | metric_code | VARCHAR(20) | Metric code key |
| metric_reference | metric_name | VARCHAR(100) | Human-readable metric name |
| metric_reference | is_active | BOOLEAN | Whether the metric is currently valid |
Sample data
| client_reports.report_id | entity_id | metric_code | report_date | metric_value |
|---|
| 1 | 101 | PD | 2024-06-30 | 0.0125 |
| 2 | 101 | LGD | 2024-06-30 | null |
| 3 | 102 | PD | 2024-06-30 | null |
| 4 | 103 | EAD | 2024-06-30 | 150000.0000 |
| credit_metrics.entity_id | metric_code | metric_value | as_of_date |
|---|
| 101 | PD | 0.0125 | 2024-06-30 |
| 101 | LGD | null | 2024-06-30 |
| 102 | PD | 0.0210 | 2024-06-30 |
| 103 | EAD | 150000.0000 | 2024-06-30 |
| 104 | PD | 0.0300 | 2024-06-30 |
| metric_reference.metric_code | metric_name | is_active |
|---|
| PD | Probability of Default | true |
| LGD | Loss Given Default | true |
| EAD | Exposure at Default | true |
| CCF | Credit Conversion Factor | false |
Expected output
| entity_id | metric_code | metric_name | report_date | report_value | source_value | null_reason |
|---|
| 101 | LGD | Loss Given Default | 2024-06-30 | null | null | source_metric_null |
| 102 | PD | Probability of Default | 2024-06-30 | null | 0.0210 | report_metric_missing |
| 103 | EAD | Exposure at Default | 2024-06-30 | 150000.0000 | 150000.0000 | none |
| 105 | CCF | null | 2024-06-30 | null | null | missing_reference |
Your query should surface only rows where the report metric is null or the metric cannot be resolved cleanly, so you can pinpoint whether the issue is in the source metric feed, the report layer, or the reference mapping.
