Business Problem

As a Financial Operations Analyst at Amazon, you're working with the billing team to create a real-time view of captured payments for orders that are still progressing through the fulfillment pipeline. This analysis is critical for cash flow management and identifying potential issues with order processing.

Your task is to identify all successfully captured customer payments that are linked to orders currently in progress (not yet completed, canceled, or returned). The finance team needs visibility into payments where the money has been collected but the corresponding orders are still being processed, shipped, or pending delivery.

The business defines "in-progress orders" as orders with statuses like 'confirmed', 'processing', 'shipped', 'in_transit', or similar active states - essentially any order that hasn't reached a final state like 'delivered', 'canceled', or 'returned'. For payments, you should only consider those with 'captured' status, as these represent successful charges where Amazon has actually collected the funds.

To help the finance team prioritize recent activity, results should be sorted by capture time with the most recent captures first. This allows them to focus on the latest transactions and identify any patterns or issues with recent order processing.

Schema: payments

Column Name	Type

Business Problem

Schema: payments

Column Name	Type

payment_id	INTEGER
order_id	INTEGER
method	TEXT
processor	TEXT
amount	REAL
currency	TEXT
status	TEXT
transaction_id	TEXT
auth_time	TEXT

payment_id	INTEGER
order_id	INTEGER
method	TEXT
processor	TEXT
amount	REAL
currency	TEXT
status	TEXT
transaction_id	TEXT
auth_time	TEXT

Column Name	Type
order_id	INTEGER
order_number	TEXT
customer_id	INTEGER
order_datetime	TEXT
status	TEXT
fulfillment_type	TEXT

Column Name	Type
order_id	INTEGER
order_number	TEXT
customer_id	INTEGER
order_datetime	TEXT
status	TEXT
fulfillment_type	TEXT

payment_id	order_id	amount	currency
35	35	105.78	USD
54	54	118.76	USD
1	1	55.32	USD
19	19	928.38	USD

1	ORD-10001	46	2025-08-20 07:01:34	shipped	pickup				pickup	50.96	0.0	4.36	0.0	55.32	captured	2025-08-20 07:01:34
2	ORD-10002	19	2025-02-24 04:56:03	delivered	ship

2754. Captured Payments for In-Progress Orders

Relevant companies

Tags

2754. Captured Payments for In-Progress Orders

Relevant companies

Tags

Sign in to Practice