15-Minute Screen SQL Mock
Interview Mock Timer
Use this timer to simulate interview pressure. Prioritize correctness, then explain tradeoffs.
20:00
Short screen drill: one query, one validation, one concise explanation.
Screen Prompt (Medium) #
Return top 10 customers by completed-order revenue in the most recent 90 days of data, excluding customers with fewer than 2 completed orders. Break ties by customer_id ascending.
The dataset is static, so anchor the window to the data, not the clock: "most recent 90 days" means on or after MAX(order_date) - 90 days. Using CURRENT_DATE against a fixed dataset returns zero rows and is non-deterministic, so call that out in an interview.
Required output columns (use these exact aliases):
- customer_id
- completed_orders
- revenue
Loading SQL editor...
sql
-- 1) Data sanity: no negative completed totals in window
SELECT COUNT(*) AS bad_rows
FROM ecom_orders
WHERE order_status = 'completed'
AND order_date >= (SELECT MAX(order_date) - INTERVAL '90 days' FROM ecom_orders)
AND order_total < 0;
-- 2) Candidate self-check template (paste your final query into candidate_result)
WITH candidate_result AS (
SELECT
customer_id,
COUNT(*) AS completed_orders,
SUM(order_total) AS revenue
FROM ecom_orders
WHERE order_status = 'completed'
AND order_date >= (SELECT MAX(order_date) - INTERVAL '90 days' FROM ecom_orders)
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY revenue DESC
LIMIT 10
)
SELECT
SUM(CASE WHEN completed_orders < 2 THEN 1 ELSE 0 END) AS rows_below_order_threshold,
COUNT(*) AS returned_rows
FROM candidate_result;Scoring Rubric #
- query correctness (50%)
- validation evidence (25%)
- clarity and time control (25%)