A complete transaction log that includes both purchases and refunds in one list, including any duplicate rows, has been requested by Stripe's finance team. Using the purchases and refunds tables, return customer_id, amount, and tx_date for every transaction, sorted by tx_date.
purchases
| column | type |
|---|---|
| id | INTEGER |
| customer_id | INTEGER |
| amount | NUMERIC |
| tx_date | DATE |
refunds
| column | type |
|---|---|
| id | INTEGER |
| customer_id | INTEGER |
| amount | NUMERIC |
| tx_date | DATE |
purchases
| id | customer_id | amount | tx_date |
|---|---|---|---|
| 1 | 101 | 50.00 | 2024-01-05 |
| 2 | 102 | 75.00 | 2024-01-08 |
refunds
| id | customer_id | amount | tx_date |
|---|---|---|---|
| 1 | 101 | 20.00 | 2024-01-06 |
| 2 | 103 | 75.00 | 2024-01-08 |
| customer_id | amount | tx_date |
|---|---|---|
| 101 | 50.00 | 2024-01-05 |
| 101 | 20.00 | 2024-01-06 |
| 102 | 75.00 | 2024-01-08 |
| 103 | 75.00 | 2024-01-08 |
All four rows appear. Two transactions share the same date (2024-01-08) and the same amount (75.00), but they belong to different customers, so both are kept. UNION ALL preserves every row without deduplication.