Write a complex SQL query involving multiple joins, subqueries, and window functions to extract specific payment metrics at WePay.
Use the provided payment, merchant, refund, and dispute data. Include activity from January and February 2025.
merchant_name, country_code, payment_month, payment_count, successful_payment_count, gross_captured_amount, refund_amount, dispute_amount, net_amount, approval_rate, previous_month_gross_amount, and country_month_rankcountry_code, payment_month, country_month_rank, and merchant_name| Column | Type | Description |
|---|---|---|
| merchant_idPK | INT | Unique merchant identifier |
| merchant_name | VARCHAR(100) | Merchant display name |
| country_code | VARCHAR(2) | Merchant country code |
| Column | Type | Description |
|---|---|---|
| payment_idPK | INT | Unique payment identifier |
| merchant_id | INT | Merchant receiving the payment |
| payment_date | DATE | Date the payment was created |
| status | VARCHAR(20) | Payment processing status |
| amount | DECIMAL(12,2) | Payment amount |
| currency | VARCHAR(3) | Payment currency |
| Column | Type | Description |
|---|---|---|
| refund_idPK | INT | Unique refund identifier |
| payment_id | INT | Payment being refunded |
| refund_amount | DECIMAL(12,2) | Refund amount |
| refund_date | DATE | Date of refund |
| Column | Type | Description |
|---|---|---|
| dispute_idPK | INT | Unique dispute identifier |
| payment_id | INT | Payment under dispute |
| dispute_amount | DECIMAL(12,2) | Disputed amount |
| dispute_status | VARCHAR(20) | Dispute resolution status |