SELECT, WHERE, GROUP BY, and HAVING
Use WHERE to filter source rows before aggregation and HAVING to filter grouped results. Explain the grain of the output and choose only columns that belong in that grain.
SELECT region, COUNT(DISTINCT customer_id) AS customers,\n SUM(revenue) AS revenue\nFROM orders\nWHERE order_date >= '2026-01-01'\nGROUP BY region\nHAVING SUM(revenue) > 100000;Logical query execution order
Understand the conceptual order FROM and JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and LIMIT. This explains why a SELECT alias may not be available in WHERE and why filtering before aggregation differs from filtering after it.
- Identify the row set produced by FROM and JOIN.
- Apply row-level filters before grouping.
- Calculate grouped results, then filter them with HAVING.
- Select, sort, and limit the final output.
CASE expressions and conditional metrics
CASE is commonly used to create business categories and conditional aggregations. Make conditions mutually understandable, include an ELSE branch, and test boundary values.
SELECT\n SUM(CASE WHEN status = 'Completed' THEN revenue ELSE 0 END) AS completed_revenue,\n SUM(CASE WHEN status = 'Refunded' THEN revenue ELSE 0 END) AS refunded_revenue\nFROM orders;JOINs
Before joining, identify whether each key is unique. A one-to-many join can multiply rows and inflate totals. Validate row counts and key uniqueness before and after the join.
SELECT o.order_id, o.order_date, c.customer_segment\nFROM orders o\nLEFT JOIN customers c\n ON c.customer_id = o.customer_id;Common table expressions
CTEs make multi-step reasoning easier to read and test. Use descriptive names and inspect each step independently when debugging.
WITH monthly_sales AS (\n SELECT DATE_FORMAT(order_date, '%Y-%m-01') AS month,\n SUM(revenue) AS revenue\n FROM orders\n GROUP BY DATE_FORMAT(order_date, '%Y-%m-01')\n)\nSELECT month, revenue\nFROM monthly_sales\nORDER BY month;Subqueries and EXISTS
Subqueries are useful when one result filters or enriches another. EXISTS is often a clear way to test whether a related record is present without multiplying rows.
SELECT c.customer_id, c.customer_name\nFROM customers c\nWHERE EXISTS (\n SELECT 1\n FROM orders o\n WHERE o.customer_id = c.customer_id\n AND o.order_date >= '2026-01-01'\n);Window functions
Window functions calculate across related rows without collapsing them. Practice ranking, running totals, previous-period comparisons, and selecting the first or most recent record.
SELECT customer_id, order_date, revenue,\n LAG(revenue) OVER (\n PARTITION BY customer_id ORDER BY order_date\n ) AS previous_revenue\nFROM orders;Dates and time periods
Interview questions often require daily, weekly, or monthly grouping, rolling periods, and period-over-period comparisons. Clarify time zone, inclusive boundaries, partial periods, and whether the date represents creation, completion, or another business event.
SELECT DATE(order_date) AS order_day,\n SUM(revenue) AS revenue\nFROM orders\nWHERE order_date >= CURRENT_DATE - INTERVAL 7 DAY\n AND order_date < CURRENT_DATE + INTERVAL 1 DAY\nGROUP BY DATE(order_date)\nORDER BY order_day;Aggregations
Know when to use COUNT, COUNT DISTINCT, SUM, AVG, MIN, and MAX. Confirm whether the metric should be calculated per transaction, customer, account, or another business grain.
SELECT product_category,\n COUNT(DISTINCT order_id) AS orders,\n SUM(revenue) AS revenue,\n AVG(revenue) AS average_line_revenue\nFROM order_lines\nGROUP BY product_category;NULL handling
NULL means missing or unknown, not zero. Use IS NULL for tests and COALESCE only when the chosen replacement has a valid business meaning.
SELECT customer_id,\n COALESCE(marketing_channel, 'Unknown') AS channel\nFROM customers\nWHERE email IS NOT NULL;Duplicate detection
Define what should be unique before searching for duplicates. A repeated customer may be valid, while a repeated transaction identifier may indicate a pipeline problem.
SELECT transaction_id, COUNT(*) AS row_count\nFROM payments\nGROUP BY transaction_id\nHAVING COUNT(*) > 1;Data quality checks
Useful checks include row counts, uniqueness, required-field completeness, accepted categories, valid ranges, referential integrity, and reconciliation against a trusted total.
SELECT\n COUNT(*) AS rows_checked,\n SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) AS missing_customer_ids,\n SUM(CASE WHEN revenue < 0 THEN 1 ELSE 0 END) AS negative_revenue_rows\nFROM orders;Debugging an incorrect query
When a result looks wrong, simplify it. Check source row counts, remove joins, inspect key uniqueness, test filters separately, and compare a small known sample. Explain your debugging process during an interview instead of silently rewriting the entire query.
- Confirm the expected output grain.
- Check whether a join changes the row count unexpectedly.
- Inspect NULLs, date boundaries, and excluded statuses.
- Reconcile the result with a trusted total or manually checked sample.
Readable and efficient SQL
Correctness comes first, followed by clarity and reasonable performance. Use meaningful aliases, avoid SELECT star in production reporting, filter unnecessary rows, and do not repeat expensive calculations without need. Mention that database-specific optimization requires an execution plan and knowledge of the schema.
Business reporting queries
A reporting query should encode clear metric definitions and comparison periods. Explain inclusions, exclusions, date logic, and how late-arriving data is handled.
SELECT DATE_FORMAT(order_date, '%Y-%m-01') AS month,\n SUM(revenue) AS revenue,\n COUNT(DISTINCT customer_id) AS active_customers\nFROM orders\nWHERE status = 'Completed'\nGROUP BY DATE_FORMAT(order_date, '%Y-%m-01')\nORDER BY month;