Business Problem

You are a Fraud Analyst at Amazon. The Risk Operations team maintains a queue of orders that require manual review before further processing. Orders enter this queue based on specific risk patterns that have historically been associated with fraudulent activity.

Your task is to identify all orders that match ANY of the following three risk scenarios:

High-value payment failures: Orders with a total_amount of $500 or more where the payment_status is 'failed'. These represent significant revenue at risk.
International chargebacks: Orders shipped to a country other than 'US' (where ship_country is not NULL and not 'US') that have a payment_status of 'chargeback'. International chargebacks are difficult to dispute.
Suspicious voided payments: Orders with a payment_status of 'void' that remain in 'confirmed' status with a of $200 or more. This pattern may indicate payment manipulation attempts.

Business Problem

Your task is to identify all orders that match ANY of the following three risk scenarios:

High-value payment failures: Orders with a total_amount of $500 or more where the payment_status is 'failed'. These represent significant revenue at risk.
International chargebacks: Orders shipped to a country other than 'US' (where ship_country is not NULL and not 'US') that have a payment_status of 'chargeback'. International chargebacks are difficult to dispute.
Suspicious voided payments: Orders with a payment_status of 'void' that remain in 'confirmed' status with a of $200 or more. This pattern may indicate payment manipulation attempts.

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

order_id	order_number	status	payment_status	ship_country	total_amount
67	ORD-10067	delivered	chargeback	UK	2034.68
64	ORD-10064	shipped	failed	CA	1059.94
24	ORD-10024

order_id	order_number	status	payment_status	ship_country	total_amount
67	ORD-10067	delivered	chargeback	UK	2034.68
64	ORD-10064	shipped	failed	CA	1059.94
24	ORD-10024

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

1008. Orders Flagged for Fraud Review

Relevant companies

Tags

1008. Orders Flagged for Fraud Review

Relevant companies

Tags

Sign in to Practice