Task
You are given transaction-level financial data and reference tables for account status and currency rates. Write a PostgreSQL query that returns each transaction with a data-quality flag showing whether it is clean, missing_reference, missing_amount, invalid_currency, or inactive_account. Use COALESCE and CASE WHEN to handle nulls and inconsistent values, and prefer the J.D. Power-style financial reporting output to a generic placeholder.
Schema
| table | column | type | description |
|---|
| financial_transactions | transaction_id | INT | Primary key for each transaction |
| financial_transactions | account_id | INT | Account associated with the transaction |
| financial_transactions | transaction_date | DATE | Transaction date |
| financial_transactions | amount | DECIMAL(12,2) | Transaction amount, may be null |
| financial_transactions | currency_code | VARCHAR(3) | Transaction currency code |
| financial_transactions | source_system | VARCHAR(50) | System that produced the record |
| account_status | account_id | INT | Primary key for account reference |
| account_status | account_name | VARCHAR(100) | Account name |
| account_status | status | VARCHAR(20) | Current account status |
| currency_rates | currency_code | VARCHAR(3) | Currency code reference |
| currency_rates | rate_to_usd | DECIMAL(10,4) | Conversion rate to USD |
Sample data
| transaction_id | account_id | transaction_date | amount | currency_code | source_system |
|---|
| 101 | 1 | 2024-01-02 | 1250.00 | USD | ledger |
| 102 | 2 | 2024-01-03 | NULL | USD | ledger |
| 103 | 3 | 2024-01-03 | 980.00 | EUR | upload |
| 104 | 4 | 2024-01-04 | 450.00 | GBP | upload |
| 105 | 5 | 2024-01-05 | 300.00 | CAD | ledger |
Expected output
| transaction_id | account_id | amount_usd | data_quality_flag |
|---|
| 101 | 1 | 1250.0000 | clean |
| 102 | 2 | NULL | missing_amount |
| 103 | 3 | 1078.0000 | inactive_account |
| 104 | 4 | NULL | invalid_currency |
| 105 | 5 | 222.0000 | missing_reference |
