How would you approach a tricky SQL problem to correctly transform and validate the results?
Use the provided transaction, adjustment, account, and expected-result tables. Produce one row for each latest settled transaction, calculate its adjusted amount, and identify whether the result is valid, mismatched, incomplete, or associated with an unknown account.
transaction_id, account_name, net_amount, expected_amount, variance, and validation_status.transaction_id ascending.| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique source event identifier |
| transaction_id | VARCHAR(20) | Business transaction identifier |
| account_id | VARCHAR(20) | Account associated with the transaction |
| version_number | INT | Source version for the transaction |
| status | VARCHAR(20) | Transaction processing status |
| gross_amount | NUMERIC(12,2) | Original transaction amount |
| loaded_at | TIMESTAMP | Time the source event was loaded |
| Column | Type | Description |
|---|---|---|
| adjustment_idPK | INT | Unique adjustment identifier |
| transaction_id | VARCHAR(20) | Transaction receiving the adjustment |
| adjustment_type | VARCHAR(20) | Fee, refund, credit, or other adjustment type |
| adjustment_amount | NUMERIC(12,2) | Signed adjustment amount |
| Column | Type | Description |
|---|---|---|
| account_idPK | VARCHAR(20) | Unique account identifier |
| account_name | VARCHAR(100) | Account display name |
| Column | Type | Description |
|---|---|---|
| transaction_idPK | VARCHAR(20) | Transaction being validated |
| expected_amount | NUMERIC(12,2) | Reference amount expected after adjustments |