Business Context
You’re on the data engineering team at a fintech payment processor that supports thousands of e-commerce merchants and processes millions of card transactions per day. Finance and Risk teams reconcile revenue by exporting three different operational feeds (checkout orders, customer-service refunds, and payment-network chargebacks) into Excel and manually stitching them together.
This workflow is error-prone: refunds don’t carry customer/currency directly, chargebacks may not be linked to an order, and each system uses different identifiers. Your task is to produce a single, audit-friendly ledger view for January 2025 that standardizes identifiers, timestamps, and sign conventions.
Task
Write a SQL query that returns a consolidated ledger of all January 2025 financial events from orders, refunds, and chargebacks.
Requirements
- Include only events where the event timestamp is in [2025-01-01, 2025-02-01).
- Output columns (in order):
event_id, event_type, event_ts, order_id, customer_id, currency, gross_amount, net_amount, source_system.
event_id must be prefixed: ord_ for orders, ref_ for refunds, cb_ for chargebacks.- Sign conventions:
- Orders:
gross_amount = order_total (positive), net_amount = order_total - processing_fee.
- Refunds:
gross_amount = -refund_amount, net_amount = -(refund_amount + refund_processing_fee).
- Chargebacks:
gross_amount = -dispute_amount, net_amount = -(dispute_amount + dispute_fee).
- Refunds must be enriched with
customer_id and currency by joining to orders on order_id.
- Chargebacks may have
order_id = NULL; keep them.
- Order the final result by
event_ts, then event_id.
Table Definitions
orders
| column | type | description |
|---|
| order_id | INT | Primary key for an order |
| order_ts | TIMESTAMP | Timestamp when the order was placed |
| customer_id | INT | Customer who placed the order |
| currency | CHAR(3) | ISO currency code |
| order_total | DECIMAL(10,2) | Total amount charged to the customer |
| processing_fee | DECIMAL(10,2) | Payment processing fee for the order |
refunds
| column | type | description |
|---|
| refund_id | INT | Primary key for a refund |
| order_id | INT | Order being refunded |
| refund_ts | TIMESTAMP | Timestamp when the refund was issued |
| refund_amount | DECIMAL(10,2) | Amount refunded to the customer |
| refund_processing_fee | DECIMAL(10,2) | Fee incurred to process the refund |
chargebacks
| column | type | description |
|---|
| chargeback_id | INT | Primary key for a chargeback/dispute record |
| order_id | INT (nullable) | Order associated with the dispute (can be NULL) |
| customer_id | INT | Customer tied to the dispute |
| dispute_ts | TIMESTAMP | Timestamp when the dispute was posted/opened |
| dispute_amount | DECIMAL(10,2) | Amount disputed/charged back |
| dispute_fee | DECIMAL(10,2) | Fee assessed by the payment network |
| currency | CHAR(3) | ISO currency code |
Sample Data
orders
| order_id | order_ts | customer_id | currency | order_total | processing_fee |
|---|
| 1001 | 2025-01-05 10:15:00 | 501 | USD | 120.00 | 3.60 |
| 1002 | 2025-01-20 14:02:00 | 502 | USD | 75.00 | 2.25 |
| 1003 | 2024-12-31 23:50:00 | 503 | EUR | 50.00 | 1.50 |
refunds
| refund_id | order_id | refund_ts | refund_amount | refund_processing_fee |
|---|
| 9001 | 1001 | 2025-01-07 09:00:00 | 20.00 | 0.50 |
| 9002 | 1002 | 2025-02-02 11:30:00 | 75.00 | 1.00 |
| 9003 | 1001 | 2025-01-25 16:45:00 | 10.00 | 0.25 |
chargebacks
| chargeback_id | order_id | customer_id | dispute_ts | dispute_amount | dispute_fee | currency |
|---|
| 8001 | 1002 | 502 | 2025-01-28 08:10:00 | 75.00 | 15.00 | USD |
| 8002 | NULL | 504 | 2025-01-12 13:00:00 | 40.00 | 10.00 | USD |
| 8003 | 1003 | 503 | 2025-01-03 10:00:00 | 50.00 | 12.00 | EUR |
Expected Output (for the sample data)
| event_id | event_type | event_ts | order_id | customer_id | currency | gross_amount | net_amount | source_system |
|---|
| ord_1001 | ORDER | 2025-01-05 10:15:00 | 1001 | 501 | USD | 120.00 | 116.40 | checkout |
| ref_9001 | REFUND | 2025-01-07 09:00:00 | 1001 | 501 | USD | -20.00 | -20.50 | cs_tool |
| cb_8002 | CHARGEBACK | 2025-01-12 13:00:00 | NULL | 504 | USD | -40.00 | -50.00 | payments_risk |
| ord_1002 | ORDER | 2025-01-20 14:02:00 | 1002 | 502 | USD | 75.00 | 72.75 | checkout |
| ref_9003 | REFUND | 2025-01-25 16:45:00 | 1001 | 501 | USD | -10.00 | -10.25 | cs_tool |
| cb_8001 | CHARGEBACK | 2025-01-28 08:10:00 | 1002 | 502 | USD | -75.00 | -90.00 | payments_risk |
