How would you design a scalable database schema to handle high-volume transaction logging and reporting requirements? Use the provided schema to produce a monthly report for active Mastercard merchants from January through March 2025, considering only transactions whose latest event is SETTLED.
merchant_id, merchant_name, report_month, transaction_count, total_amount, prior_month_amount, month_over_month_pct, and cumulative_amountmerchant_id, then report_month ascending| Column | Type | Description |
|---|---|---|
| merchant_idPK | INT | Unique Mastercard merchant identifier |
| merchant_name | VARCHAR(120) | Merchant display name |
| merchant_category | VARCHAR(60) | Merchant business category |
| is_active | BOOLEAN | Whether the merchant is active for reporting |
| Column | Type | Description |
|---|---|---|
| transaction_idPK | BIGINT | Unique transaction identifier |
| merchant_id | INT | Merchant associated with the transaction |
| created_at | TIMESTAMP | Transaction creation timestamp |
| amount | NUMERIC(12,2) | Transaction amount in the transaction currency |
| currency | VARCHAR(3) | ISO 4217 currency code |
| Column | Type | Description |
|---|---|---|
| event_idPK | BIGINT | Monotonically increasing event identifier |
| transaction_id | BIGINT | Transaction whose state changed |
| event_type | VARCHAR(30) | Transaction lifecycle state |
| event_at | TIMESTAMP | Time at which the event occurred |