Reference

SQL Core Questions

This lesson simulates common SQL interview patterns with increasing difficulty and explicit scoring criteria.

Focus areas:

grain correctness
joins and aggregation safety
ranking and tie handling
validation-first reasoning

Difficulty Ladder #

Easy: single-table aggregations with clear filters
Medium: multi-table joins and partitioned ranking
Hard: ambiguous constraints, tie rules, and follow-up changes

Core Prompt (Medium) #

For each product category, return the customer with the highest total completed order amount.

Loading SQL editor...

Validation Query (Required) #

sql

SELECT
  o.order_id,
  o.order_total,
  SUM(oi.line_total) AS item_total
FROM ecom_orders o
JOIN ecom_order_items oi
  ON oi.order_id = o.order_id
WHERE o.order_status = 'completed'
GROUP BY o.order_id, o.order_total
HAVING ROUND(o.order_total, 2) <> ROUND(SUM(oi.line_total), 2);

Hard Follow-Up Prompt #

Return top 2 customers per category, include ties at rank 2, and exclude customers with fewer than 2 completed orders in that category.

Loading SQL editor...

Interviewer Follow-Up Checklist #

Be prepared to answer:

what is the exact output grain?
how are ties handled?
why this window function and not another?
what validation query proves correctness?
what would you change if performance degrades?

Scoring Rubric #

Correctness (40%): grain, joins, and aggregations are accurate
Validation (25%): evidence-based verification is present
Communication (20%): assumptions and tradeoffs are clear
Performance awareness (15%): sensible optimization reasoning

Warning

Most SQL interview failures come from grain mistakes and missing validation, not missing syntax.