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 |