00:00

2005. Top 3 Delivered Purchases per State

MediumSQLITE68 / 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

2005. Top 3 Delivered Purchases per State

Medium68 / 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 Regional Analytics Manager at Walmart. The team wants to see the top completed purchases in each U.S. state for benchmarking. Your task is to provide, for each state, the three purchases with the highest billed totals. Show the state, purchase identifier, billed amount, and purchase time, and include a rank of 1–3 within each state. Consider only completed purchases with successful payment and a U.S. shipping destination. Sort by state alphabetically; within each state, show the highest billed first and break ties by the smallest purchase identifier.

Schema: orders

Column Name	Type
order_id	INTEGER
order_number	TEXT
customer_id	INTEGER

Business Problem

Schema: orders

Column Name	Type
order_id	INTEGER
order_number	TEXT
customer_id	INTEGER

Top 3 Delivered Purchases per State — SQL Medium | Let's Data Science | Let's Data Science

ship_state	order_id	order_datetime	total_amount	state_rank
IL	68	2025-04-14 10:49:21	581.84	1
IL	28	2024-10-21 09:33:19	492.19	2
IL	15	2025-08-21 08:37:53	131.01	3

ship_state	order_id	order_datetime	total_amount	state_rank
IL	68	2025-04-14 10:49:21	581.84	1
IL	28	2024-10-21 09:33:19	492.19	2
IL	15	2025-08-21 08:37:53	131.01	3

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

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

2005. Top 3 Delivered Purchases per State

Relevant companies

Tags

2005. Top 3 Delivered Purchases per State

Relevant companies

Tags

Sign in to Practice