00:00

2512. Delivered Orders with Latest Capture (Normalized)

HardSQLITE110 / 1584

Relevant companies

Amazon

Walmart

Target

Instacart

Shopify

eBay

Etsy

Wayfair

Chewy

Kroger

Costco

Best Buy

Home Depot

IKEA

Alibaba

Flipkart

Mercado Libre

Rakuten

Faire

Coupang

2512. Delivered Orders with Latest Capture (Normalized)

Hard110 / 1584

Relevant companies

Amazon

Walmart

Target

Instacart

Shopify

eBay

Etsy

Wayfair

Chewy

Kroger

Costco

Best Buy

Home Depot

IKEA

Alibaba

Flipkart

Mercado Libre

Rakuten

Faire

Coupang

Sign in to Practice

Write SQL and Python, run instantly in your browser, and track your progress.

Runs live in your browser — no setup
Save progress across all 1,500+ problems
Submit and validate your answer instantly

Business Problem

You are a Financial Analyst at Amazon. The finance team is preparing the quarterly revenue reconciliation report and needs a normalized view of all delivered orders from the last 90 days, paired with their most recent successful payment capture.

Your task is to combine data from the orders, shipments, and payments tables to create a clean dataset. For orders with multiple payment captures, only the most recent one should be included.

Schema: orders

Column Name	Type
order_id	INTEGER

Business Problem

Your task is to combine data from the orders, shipments, and payments tables to create a clean dataset. For orders with multiple payment captures, only the most recent one should be included.

Schema: orders

Column Name	Type
order_id	INTEGER

Delivered Orders with Latest Capture (Normalized) — SQL Hard | Let's Data Science | Let's Data Science

Column Name	Type
shipment_id	INTEGER
order_id	INTEGER
warehouse_code	TEXT
carrier_name	TEXT
service_level	TEXT
tracking_number	TEXT

Column Name	Type
shipment_id	INTEGER
order_id	INTEGER
warehouse_code	TEXT
carrier_name	TEXT
service_level	TEXT
tracking_number	TEXT

Column Name	Type
payment_id	INTEGER
order_id	INTEGER
method	TEXT
processor	TEXT
amount	REAL
currency	TEXT

Column Name	Type
payment_id	INTEGER
order_id	INTEGER
method	TEXT
processor	TEXT
amount	REAL
currency	TEXT

order_id	order_number	payment_id	capture_date	delivery_date	order_status_upper	currency_upper
15	ORD-10015	15	2025-08-23	2025-08-25	DELIVERED	USD
11	ORD-10011	11	2025-06-29	2025-06-28	DELIVERED

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

2512. Delivered Orders with Latest Capture (Normalized)

Relevant companies

Tags

2512. Delivered Orders with Latest Capture (Normalized)

Relevant companies

Tags

Sign in to Practice