Your question is Diagnose Moody's Output Data Mismatches. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
You are given a client reconciliation workflow where a Moody's tool output must be compared against the client's internal data. Write a PostgreSQL query that returns, for each run date and mismatch type, how many records are affected. Classify each record as missing_in_client, missing_in_moodys, value_mismatch, or match based on the available keys and field values.
Use the provided moodys_tool_output and client_internal_data tables. Keep the result ordered by run date and mismatch type so the output is easy to review during investigation.
| Column | Type | Description |
|---|---|---|
| run_date | DATE | Moody's tool run date |
| record_id | INT | Moody's record identifier |
| client_id | VARCHAR(20) | Client-facing identifier |
| risk_score | INT | Moody's computed risk score |
| status | VARCHAR(20) | Moody's status value |
| Column | Type | Description |
|---|---|---|
| run_date | DATE | Client extract date |
| record_id | INT | Client record identifier |
| client_id | VARCHAR(20) | Client-facing identifier |
| risk_score | INT | Client risk score |
| status | VARCHAR(20) | Client status value |