Tell me about a time you solved a SQL problem on a transaction-level payments table and then designed the data model to support reporting or analytics.
For this exercise, use the provided payments and merchants tables to produce a reporting-ready monthly payment summary. Include only settled transactions and calculate refunds as negative amounts.
merchant_id, merchant_name, account_region, report_month, settled_transaction_count, and net_amount| Column | Type | Description |
|---|---|---|
| merchant_idPK | INT | Stable identifier for the merchant |
| merchant_name | VARCHAR(100) | Display name of the merchant |
| account_region | VARCHAR(30) | Reporting region assigned to the merchant |
| created_at | TIMESTAMP | Timestamp when the merchant record was created |
| Column | Type | Description |
|---|---|---|
| payment_idPK | INT | Unique transaction identifier |
| merchant_id | INT | Merchant associated with the payment |
| transaction_ts | TIMESTAMP | Time when the payment transaction occurred |
| transaction_type | VARCHAR(20) | Transaction direction, such as capture or refund |
| status | VARCHAR(20) | Processing status of the transaction |
| amount | NUMERIC(12,2) | Transaction amount in the stated currency |
| currency | VARCHAR(3) | ISO currency code |