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.
| Table | Column | Type | Description |
|---|---|---|---|
| moodys_tool_output | run_date | DATE | Moody's tool run date |
| moodys_tool_output | record_id | INT | Moody's record identifier |
| moodys_tool_output | client_id | VARCHAR(20) | Client-facing identifier |
| moodys_tool_output | risk_score | INT | Moody's computed risk score |
| moodys_tool_output | status | VARCHAR(20) | Moody's status value |
| client_internal_data | run_date | DATE | Client extract date |
| client_internal_data | record_id | INT | Client record identifier |
| client_internal_data | client_id | VARCHAR(20) | Client-facing identifier |
| client_internal_data | risk_score | INT | Client risk score |
| client_internal_data | status | VARCHAR(20) | Client status value |
moodys_tool_output
| run_date | record_id | client_id | risk_score | status |
|---|---|---|---|---|
| 2024-05-01 | 1 | AC-100 | 82 | active |
| 2024-05-01 | 2 | AC-101 | 67 | active |
| 2024-05-01 | 3 | AC-102 | 91 | review |
| 2024-05-02 | 4 | AC-200 | 55 | active |
| 2024-05-02 | 5 | AC-201 | 44 | closed |
| 2024-05-02 | 6 | AC-202 | 73 | review |
client_internal_data
| run_date | record_id | client_id | risk_score | status |
|---|---|---|---|---|
| 2024-05-01 | 1 | AC-100 | 82 | active |
| 2024-05-01 | 2 | AC-101 | 70 | active |
| 2024-05-01 | 7 | AC-107 | 88 | review |
| 2024-05-02 | 4 | AC-200 | 55 | active |
| 2024-05-02 | 5 | AC-201 | 44 | suspended |
| 2024-05-02 | 8 | AC-208 | 61 | active |
| run_date | mismatch_type | record_count |
|---|---|---|
| 2024-05-01 | match | 1 |
| 2024-05-01 | missing_in_client | 1 |
| 2024-05-01 | missing_in_moodys | 1 |
| 2024-05-01 | value_mismatch | 1 |
| 2024-05-02 | match | 1 |
| 2024-05-02 | missing_in_client | 1 |
| 2024-05-02 | missing_in_moodys | 1 |
| 2024-05-02 | value_mismatch | 1 |
