Write a SQL query to verify that a specific policy update correctly cascaded to the claims database. Use update_id = 1002 as the specific update. Compare eligible claims with the policy update and its audit records, identifying missing applications, mismatched limits, and stale references.
1002update_id, policy_id, expected_claim_count, cascaded_claim_count, missing_audit_count, mismatched_limit_count, stale_reference_count, cascade_statusPASS only when all eligible claims were correctly updated and audited; otherwise return FAIL.| Column | Type | Description |
|---|---|---|
| update_idPK | INT | Unique policy update identifier |
| policy_id | INT | Policy affected by the update |
| old_coverage_limit | NUMERIC(12,2) | Coverage limit before the update |
| new_coverage_limit | NUMERIC(12,2) | Coverage limit expected after the update |
| updated_at | TIMESTAMP | Time the policy update became effective |
| status | VARCHAR(20) | Policy update processing status |
| Column | Type | Description |
|---|---|---|
| claim_id | INT | Business identifier for the claim |
| claim_version | INT | Version number for the claim |
| policy_id | INT | Policy associated with the claim |
| claim_created_at | TIMESTAMP | Claim creation timestamp |
| claim_updated_at | TIMESTAMP | Claim version update timestamp |
| claim_status | VARCHAR(20) | Current claim status |
| coverage_limit | NUMERIC(12,2) | Coverage limit stored on the claim |
| policy_update_id | INT | Policy update applied to the claim |
| Column | Type | Description |
|---|---|---|
| audit_idPK | INT | Unique audit record identifier |
| claim_id | INT | Claim affected by the update |
| policy_update_id | INT | Policy update recorded by the audit event |
| applied_at | TIMESTAMP | Time the cascade was recorded |
| source_system | VARCHAR(30) | System that recorded the audit event |