Layout

Largest order in each country

PROBLEM 22 / 24 · ADVANCEDAll problems ↗

Return all completed orders tied for the largest order_total_cents within each customer country. Return country, order_id, and order_total_cents. Keep every tie. Row order does not matter.

Outputcountryorder_idorder_total_cents

query.sql

DuckDB SQL

Restoring your saved draft…

⌘ / Ctrl + Enter to run

Run a query to see results.

Reveal solution & explanation
WITH ranked AS (SELECT c.country, o.order_id, o.order_total_cents, RANK() OVER (PARTITION BY c.country ORDER BY o.order_total_cents DESC) AS r FROM orders o JOIN customers c ON c.customer_id = o.customer_id WHERE o.status = 'completed') SELECT country, order_id, order_total_cents FROM ranked WHERE r = 1;

ROW_NUMBER would arbitrarily choose one winner. RANK expresses that all equally large orders should be retained.

Data & schema

customer_id
INTEGER · PK
customer_name
VARCHAR
country
VARCHAR
email
VARCHAR
signup_date
DATE
Table notes & relationships

Buyer accounts. Some buyers have no orders, and some email addresses are NULL.

Fictional teaching data. Money is USD cents; timestamps are UTC.