Your interview prompt
Use the original Harbor tables from Job simulation 1 in a fresh scratch SQLite database. Return the top two distinct net-value levels among customers for January, including ties. Use completed orders and refunds recorded before February 1. Explain what “top two” would mean if the interviewer wanted exactly two customers instead.
Attempt before the answer
State one row per customer as the ranking grain. Sketch the refund aggregation, order join, customer aggregation and ranking stages. Predict the output and explain why ranking the individual refund-join rows would be wrong. Allow 15 minutes for SQL and five minutes for explanation.
Worked solution
The baseline rows are C=150 at place 1 and A=145 at place 2; B=80 is third. DENSE_RANK returns distinct value levels without gaps. RANK can skip levels after ties; ROW_NUMBER returns row positions. For exactly two customers, use ROW_NUMBER ordered by net descending and customer as a stated deterministic tie-breaker.
WITH refund_by_order AS (
SELECT order_id,SUM(amount) AS refunded
FROM refunds WHERE refunded_at < '2026-02-01' GROUP BY order_id
), customer_net AS (
SELECT o.customer,SUM(o.amount-COALESCE(r.refunded,0)) AS net
FROM orders o LEFT JOIN refund_by_order r ON r.order_id=o.order_id
WHERE o.status='completed' AND o.ordered_at>='2026-01-01'
AND o.ordered_at<'2026-02-01' GROUP BY o.customer
), ranked AS (
SELECT customer,net,DENSE_RANK() OVER (ORDER BY net DESC) AS place
FROM customer_net
)
SELECT customer,net,place FROM ranked WHERE place<=2
ORDER BY place,customer;Change the data
In a separate copy, add o7 for customer D, completed January 15, amount 150 with no refund. Expected rows: C=150 and D=150 at place 1, A=145 at place 2. There are three customers in the top two value levels. If you use RANK instead, A has rank 3 and is excluded. Reconcile the changed net total of 525 before trusting the ranking.
Reviewer rubric
Score 0–2 each for metric/cutoff clarity, join grain, aggregation, tie semantics and explanation. Two means correct with a stated reason; one means partly correct; zero means absent or wrong. Aim to explain all five rather than memorise the query. This self-practice score is not an employer assessment.
What a strong explanation sounds like
“I aggregate refunds by order before joining so multiple refunds cannot duplicate order value. Then I aggregate net value by customer and rank those customer rows. I chose dense rank because the request asks for distinct value levels, not a fixed row count. I would confirm the tie rule before building the dashboard.”
Try the activity before reading its reference. Keep your sources, commands and assumptions with your work. Companion activities are unsaved self-practice and do not award a certificate or change lesson progress.