Layout

March spending ranks with gaps

PROBLEM 15 / 24 · ADVANCEDAll problems ↗

Aggregate completed spending per customer in March 2026, then return customer_id, spend_cents, and spend_rank using competition ranking: ties share a rank and leave gaps. Rank by spending descending only; do not break ranking ties with customer_id. Row order does not matter.

Outputcustomer_idspend_centsspend_rank

query.sql

DuckDB SQL

Restoring your saved draft…

⌘ / Ctrl + Enter to run

Run a query to see results.

Reveal solution & explanation
WITH spend AS (SELECT customer_id, SUM(order_total_cents) AS spend_cents FROM orders WHERE status = 'completed' AND ordered_at >= TIMESTAMP '2026-03-01' AND ordered_at < TIMESTAMP '2026-04-01' GROUP BY customer_id) SELECT customer_id, spend_cents, RANK() OVER (ORDER BY spend_cents DESC) AS spend_rank FROM spend;

For amounts 300, 200, 200, 100, RANK gives 1, 2, 2, 4. Adding customer_id inside the window ordering destroys the spending ties.

Data & schema

order_id
INTEGER · PK
customer_id
INTEGER
ordered_at
TIMESTAMP
status
VARCHAR
order_total_cents
INTEGER
Table notes & relationships

One row per order. Only completed orders count as revenue unless a question says otherwise. Times are UTC.

  • customer_id → customers.customer_id
  • status completed / cancelled / pending

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