Top three spending tiers
Return every customer in the top three distinct March completed-spending tiers, including all ties. Return customer_id, spend_cents, and spend_tier. Tiers must be consecutive (1, 2, 3). Row order does not matter.
Output
customer_idspend_centsspend_tierquery.sql
DuckDB SQLRestoring your saved draft…
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), ranked AS (SELECT customer_id, spend_cents, DENSE_RANK() OVER (ORDER BY spend_cents DESC) AS spend_tier FROM spend) SELECT * FROM ranked WHERE spend_tier <= 3;
DENSE_RANK does not skip a tier after ties, so it matches ‘three distinct spending amounts’.
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_idstatuscompleted / cancelled / pending
Fictional teaching data. Money is USD cents; timestamps are UTC.