SQL Technical Interview Questions and Answers

What interviewers are testing: Whether you distinguish SQL as the query language from the RDBMS engine that stores data and enforces transactions.

SQL (Structured Query Language) is the standard language for defining, querying, and manipulating relational data — tables with rows and columns linked by keys. Traditional relational DBMSs include PostgreSQL, MySQL, SQL Server, Oracle, and SQLite. Analytical systems/warehouses such as BigQuery and Snowflake also expose relational/SQL query models but have different storage and execution architectures.

Why interviewers ask this: They want to know you understand SQL is not "a database" — it is the interface to one.

SQL statement categories (useful vocabulary in system-design tangents):

Most technical screens are SELECT-heavy — SELECT with joins and aggregates — but senior roles may ask how migrations (DDL) or transactions (TCL) affect production behavior.

A strong answer is:

SQL is the language; the RDBMS is the engine. Interviews focus on SELECT and relational reasoning, but I know DDL/DML/TCL matter for how data is stored, updated, and kept consistent in production.

What interviewers are testing: Whether you explain primary keys as row identity and foreign keys as referential integrity that joins rely on.

Keys define identity and relationships between tables — the foundation of every join you write in an interview.

PRIMARY KEY

• Uniquely identifies each row in a table
• Implies NOT NULL (no duplicate keys, no missing keys)
• One primary key per table (can be composite — multiple columns together)

FOREIGN KEY

• Column(s) in a child table that reference a key in a parent table
• Enforces referential integrity — you cannot insert a child row with a parent id that does not exist (unless constraints are deferred or disabled)

Interview example: orders.customer_id → customers.customer_id. A JOIN between orders and customers relies on this relationship.

Practical tip: Interview schemas sometimes omit explicit FK constraints but still have logical keys. Ask: "Which column links these tables?" before joining on the wrong id.

See SQL CREATE TABLE for how keys are declared in DDL.

A strong answer is:

A primary key uniquely identifies rows in a table; a foreign key links a child table to a parent and enforces referential integrity. I confirm join keys before writing JOINs, even when FK constraints are not shown on the schema.

What interviewers are testing: Whether you distinguish row deletion from table-wide removal and object deletion, and know that transaction/rollback behavior varies by engine.

All three remove data or structure, but at different scope and with different safety implications — a common fundamentals question for backend and data roles.

Details interviewers probe:

• DELETE without WHERE deletes every row but keeps the table — still logged row-by-row in many engines
• TRUNCATE is usually optimized for removing all rows without ordinary row-by-row DELETE processing; exact logging, identity reset, locking, trigger, and rollback behavior is database-specific
• DROP removes the table object from the catalog. Rollback behavior depends on the database and transaction context — PostgreSQL can roll back many DDL statements inside a transaction; other engines differ. After commit, recovery usually means backup/restore or CREATE again — do not assume undo

Production caution: TRUNCATE and DROP are common incident causes; mention transactions and backups on senior loops.

A strong answer is:

DELETE removes specific rows and can often roll back in a transaction; TRUNCATE wipes all rows quickly without a WHERE; DROP removes the table object. I choose based on scope, and I treat DROP rollback as dialect-dependent — after commit you typically need recreation or restore.

What interviewers are testing: Whether you filter rows before grouping with WHERE and groups after aggregation with HAVING—or use a CTE when the logic is clearer.

Both filter rows, but at different stages of query execution — mixing them up is one of the most common SQL mistakes in interviews and production.

Execution order (simplified):

1. FROM / JOIN — assemble rows
2. WHERE — filter individual rows before grouping
3. GROUP BY — form groups
4. HAVING — filter groups after aggregation
5. SELECT / ORDER BY — project and sort

SELECT department_id, AVG(salary) AS avg_sal
FROM employees
WHERE status = 'active'           -- row filter: only active employees enter the average
GROUP BY department_id
HAVING AVG(salary) > 80000;       -- group filter: only departments above threshold

Classic trap: Putting a non-aggregated column in HAVING that belongs in WHERE (e.g., HAVING status = 'active'). Some databases allow it but it confuses readers and may hurt performance.

Cleaner pattern for complex filters: Aggregate in a CTE, then WHERE on the alias in the outer query.

A strong answer is:

WHERE filters rows before GROUP BY; HAVING filters groups after aggregation. I put row-level conditions in WHERE and aggregate thresholds in HAVING — or use a CTE when the logic gets hard to read.

What interviewers are testing: Whether you understand three-valued logic: ordinary comparisons involving NULL usually evaluate to UNKNOWN, so IS NULL, NOT EXISTS, and aggregate NULL rules matter.

NULL means unknown or missing — not zero, not empty string '', not false. Three-valued logic (TRUE, FALSE, UNKNOWN) drives many interview traps.

Rules to internalize:

Interview follow-ups:

• SUM of no rows returns NULL — use COALESCE(SUM(x), 0) when you need zero
• NOT IN (subquery) breaks if the subquery returns NULL — prefer NOT EXISTS
• Outer joins produce NULL for non-matching columns — anti-joins use WHERE right.key IS NULL

See ISNULL / NULL handling for dialect-specific functions.

A strong answer is:

NULL is unknown — I never compare with = NULL; I use IS NULL. I remember COUNT(column) skips NULLs, and I avoid NOT IN when the subquery might return NULL.

What interviewers are testing: Whether you use COALESCE for intentional fallbacks without replacing proper IS NULL filters.

COALESCE(expr1, expr2, …) returns the first non-NULL argument — the standard tool for NULL-safe display and fallbacks in reports.

Common interview uses:

What COALESCE does not fix: It does not replace proper IS NULL filters in WHERE. COALESCE(col, 0) = 0 matches both NULL and zero — know if that is intended.

vs IFNULL / NVL: Dialect-specific aliases; COALESCE is portable SQL.

See server COALESCE examples for more patterns.

A strong answer is:

I use COALESCE for the first non-NULL fallback — especially zero instead of NULL after outer joins or empty aggregates — and I pair it with NULLIF when I need safe division, not as a substitute for correct NULL filters.

What interviewers are testing: Whether you pick join types based on whether unmatched rows must survive—not from Venn diagrams alone.

Joins combine rows from two tables based on a join condition — usually equality on keys (ON a.id = b.a_id). The join type decides what happens when no match exists on one side.

How to narrate in an interview: "I need every customer, even those with zero orders — that's a LEFT JOIN from customers to orders."

Drill syntax: LEFT JOIN examples, RIGHT JOIN, FULL OUTER JOIN.

A strong answer is:

INNER keeps only matching rows; LEFT keeps all left rows with NULLs when there is no right match; FULL OUTER keeps both sides. I pick the type based on whether I need unmatched rows and say that aloud before coding.

What interviewers are testing: Whether you alias the same table for hierarchical or pairwise row relationships.

A self-join joins a table to itself — aliased as two different roles — when relationships exist between rows in the same table.

Common interview patterns:

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

LEFT JOIN keeps employees with no manager (CEO). INNER JOIN would drop them.

Modern alternative: LAG / recursive CTEs for hierarchies — but self-joins still appear in screens because they test whether you understand join logic.

A strong answer is:

I self-join when rows in one table relate to other rows in the same table — like employee to manager. I use clear aliases and LEFT JOIN when the parent row might not exist.

What interviewers are testing: Whether you find unmatched rows with LEFT JOIN ... IS NULL or NOT EXISTS—not NOT IN when NULLs are possible.

An anti-join returns rows in table A with no corresponding row in table B — "customers who never ordered," "orders without payments."

Three idioms (know all three; prefer NOT EXISTS when NULLs are possible):

-- 1. LEFT JOIN + NULL filter (most visual)
SELECT a.*
FROM orders a
LEFT JOIN payments b ON a.order_id = b.order_id
WHERE b.order_id IS NULL;

-- 2. NOT EXISTS (often efficient; NULL-safe)
SELECT a.*
FROM orders a
WHERE NOT EXISTS (
  SELECT 1 FROM payments b WHERE b.order_id = a.order_id
);

-- 3. NOT IN — only when subquery column is NOT NULL guaranteed
SELECT a.*
FROM orders a
WHERE a.order_id NOT IN (SELECT order_id FROM payments);

NULL trap with NOT IN: If the subquery returns any NULL order_id, the outer NOT IN may return no rows (UNKNOWN logic). NOT EXISTS avoids this.

See EXISTS examples for more patterns.

A strong answer is:

I use LEFT JOIN with WHERE right.key IS NULL or NOT EXISTS for rows in A with no match in B. I avoid NOT IN when the subquery might contain NULLs.

What interviewers are testing: Whether you recognize intentional Cartesian products and can diagnose accidental row explosions from missing join predicates.

A CROSS JOIN produces the Cartesian product: every row in A paired with every row in B. Row count = |A| × |B|.

Intentional uses:

• Build a date spine × product list for zero-filled reporting grids
• Generate combinations (small dimension tables only)

Why it is dangerous: A missing ON clause on an implicit join, or forgetting a filter, can turn 1,000 × 1,000 rows into one million rows — a classic production outage pattern.

Interview signal: When you see unexpectedly huge row counts, check for accidental cross joins.

A strong answer is:

CROSS JOIN pairs every row in A with every row in B. I use it deliberately for small dimension combos like date × product, and I watch for missing join conditions that create accidental Cartesian explosions.

What interviewers are testing: Whether you stack compatible result sets vertically with UNION versus combining related tables horizontally with JOIN.

They solve different problems — interviewers test whether you confuse stacking rows with combining columns.

Examples:

• JOIN: Add customer_name to each order row
• UNION ALL: Combine archived_orders and orders into one report

UNION ALL is faster when you do not need deduplication — prefer it unless duplicates must be removed.

Review SQL UNION examples.

A strong answer is:

JOIN combines related tables horizontally on keys; UNION stacks compatible result sets vertically. I use UNION ALL unless I explicitly need deduplication.

What interviewers are testing: Whether you know COUNT(*) counts rows while COUNT(column) skips NULLs.

COUNT seems simple but encodes NULL semantics — senior interviewers use it to test attention to detail.

Example: 10 users in a group, 3 with NULL email:

• COUNT(*) = 10
• COUNT(email) = 7

When it matters: "How many users provided an email?" → COUNT(email). "How many users?" → COUNT(*).

Do not use COUNT(DISTINCT order_id) as a band-aid for join double-counting — fix grain first (see the double-counting question in this section).

A strong answer is:

COUNT(*) counts rows; COUNT(column) counts non-NULL values in that column. I pick based on whether NULL means missing data I should exclude from the count.

What interviewers are testing: Whether you recognize one-to-many row multiplication and fix the grain through pre-aggregation or an appropriately scoped subquery before summing parent-level metrics.

Double-counting happens when you join before aggregating at the wrong grain — one parent row matches many child rows, so parent metrics get multiplied.

Classic mistake:

-- WRONG: order total counted once per line item
SELECT o.order_id, SUM(o.order_total) AS revenue
FROM orders o
JOIN order_items i ON o.order_id = i.order_id
GROUP BY o.order_id;

If an order has 5 line items, order_total is summed five times.

How to fix (pick grain first):

-- Metric comes from line items: aggregate to order grain FIRST, then join via orders
WITH order_revenue AS (
  SELECT order_id, SUM(quantity * unit_price) AS order_total
  FROM order_items
  GROUP BY order_id
)
SELECT c.customer_id, SUM(r.order_total) AS customer_revenue
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_revenue r ON o.order_id = r.order_id
GROUP BY c.customer_id;

-- Order total already on orders table: no line-item join needed for revenue
SELECT SUM(order_total) FROM orders;

If you must join one-to-many for filters (e.g., "orders containing product X"), use a distinct order list or subquery at order grain before aggregating order-level dollars.

COUNT(DISTINCT order_id) only when the business metric is literally "count distinct orders" — not a band-aid for inflated SUM.

Interview habit: Say grain aloud — "one row per order," "one row per user per day" — before writing GROUP BY.

A strong answer is:

Double-counting happens when I join one-to-many before aggregating parent metrics. I aggregate line items to order grain first, or keep order-level totals without the line join, then join to customers or time — and I state grain explicitly.

What interviewers are testing: Whether you clarify tie behavior and use DENSE_RANK or a subquery for the second distinct value—not only MAX hacks.

Clarify tie behavior first: if two people tie for highest, is there a "second" salary distinct value, or the next rank?

Subquery approach (distinct second value):

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Returns NULL if everyone has the same salary.

Window approach (handles ties with ranking):

WITH ranked AS (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT DISTINCT salary
FROM ranked
WHERE rnk = 2;

• DENSE_RANK — 1, 1, 2 when two people tie for top (good for "second distinct salary level")
• ROW_NUMBER — arbitrary tie-break; use only if interviewer wants exactly one row

Classic interview pattern for second-highest salary with tie handling.

A strong answer is:

I clarify tie rules, then use MAX with a subquery for the second distinct value or DENSE_RANK = 2 when ties matter. I mention NULL when all salaries are equal.

What interviewers are testing: Whether you partition and rank per group instead of applying a global LIMIT after GROUP BY.

Wrong approach: GROUP BY department_id + LIMIT 3 — LIMIT applies to the final result, not per group.

Right approach: window function + filter:

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC, employee_id
         ) AS rnk
  FROM employees
)
SELECT *
FROM ranked
WHERE rnk <= 3;

Function choice:

Common pattern for ranking within departments—appears constantly in data interviews.

See select first row in group for related patterns.

A strong answer is:

I partition by department, rank by salary with ROW_NUMBER or DENSE_RANK depending on tie rules, then filter rnk <= 3 in an outer query — never GLOBAL LIMIT after GROUP BY.

What interviewers are testing: Whether you know aggregates are unavailable in WHERE and filter them in HAVING or an outer query.

No — aggregate expressions are not available in WHERE because filtering happens before grouping.

Options:

-- HAVING (after GROUP BY)
SELECT department_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 80000;

-- CTE + WHERE (often clearer in interviews)
WITH dept_avg AS (
  SELECT department_id, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department_id
)
SELECT * FROM dept_avg WHERE avg_sal > 80000;

CTEs read top-to-bottom like your explanation — interviewers follow the logic more easily than nested HAVING with multiple aggregates.

A strong answer is:

I cannot filter aggregates in WHERE — only in HAVING after GROUP BY, or in an outer query on a CTE. I prefer CTE plus WHERE when the logic has multiple steps.

What interviewers are testing: Whether you deduplicate rows with DISTINCT versus define groups for aggregation with GROUP BY.

Both can reduce duplicate rows, but they serve different purposes — confusing them is a common interview mistake.

-- DISTINCT: unique country values
SELECT DISTINCT country FROM customers;

-- GROUP BY: count per country
SELECT country, COUNT(*) AS n
FROM customers
GROUP BY country;

Similar-looking output: SELECT DISTINCT country vs SELECT country FROM customers GROUP BY country — often the same rows, but meaning differs: one deduplicates; the other defines groups (and could add aggregates).

Misuse: SELECT DISTINCT customer_id, SUM(amount) without GROUP BY is invalid — use GROUP BY customer_id.

A strong answer is:

DISTINCT removes duplicate rows in the result; GROUP BY defines groups for aggregation. They can look similar without aggregates, but I use GROUP BY when I need counts or sums per key.

What interviewers are testing: Whether you use CASE for conditional aggregation and bucketing with explicit ELSE semantics.

CASE WHEN is the workhorse for conditional logic in queries — especially conditional aggregation and bucketing without pivot syntax.

Patterns interviewers expect:

-- Conditional counts (CTR-style)
SUM(CASE WHEN event_type = 'click' THEN 1 ELSE 0 END) AS clicks

-- Conditional sums
SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS completed_revenue

-- Bucketing users
CASE
  WHEN total_spend >= 1000 THEN 'high'
  WHEN total_spend >= 100 THEN 'medium'
  ELSE 'low'
END AS spend_tier

-- Pivot-style metrics in one query
SUM(CASE WHEN product = 'A' THEN revenue ELSE 0 END) AS product_a_revenue,
SUM(CASE WHEN product = 'B' THEN revenue ELSE 0 END) AS product_b_revenue

Tips: Choose ELSE 0 when non-matching rows should contribute zero; omit it or use ELSE NULL when you intentionally want NULL semantics. Be explicit rather than relying on defaults. Use CASE in ORDER BY for custom sort orders.

See SQL IF in SELECT for related patterns.

A strong answer is:

I use CASE WHEN for conditional counts and sums, user bucketing, and pivot-style metrics in one GROUP BY—with explicit ELSE choices when branch semantics matter.

What interviewers are testing: Whether you choose decomposition for readability without assuming a CTE is automatically faster or always materialized.

Both break problems into steps; CTEs (WITH) usually win in interviews for readability and reuse.

Prefer a CTE when:

• Logic has multiple steps — filter events → aggregate per user → rank
• You reference the same intermediate set twice
• You want the interviewer to follow top-to-bottom narrative

Nested subquery is fine when:

• Single simple existence check (WHERE EXISTS (...))
• One-off scalar subquery (WHERE salary > (SELECT AVG...))

WITH active_users AS (
  SELECT user_id FROM users WHERE status = 'active'
),
monthly AS (
  SELECT user_id, DATE_TRUNC('month', event_at) AS month, COUNT(*) AS events
  FROM events
  WHERE user_id IN (SELECT user_id FROM active_users)
  GROUP BY 1, 2
)
SELECT * FROM monthly;

CTEs are primarily a readability/decomposition choice. Do not assume they are automatically materialized or faster; optimizer behavior varies by engine and reference count. Correlated subqueries can be expensive depending on the optimizer and data shape; inspect the plan before assuming a join rewrite is faster.

See SQL WITH clause examples.

A strong answer is:

I use CTEs for multi-step logic and readability; nested subqueries for simple scalar or EXISTS checks. I check the plan before assuming a join rewrite beats a correlated subquery.

What interviewers are testing: Whether you explain per-row subquery dependence and know optimizers may decorrelate it.

A correlated subquery references columns from the outer query. It can be thought of as per-row evaluation, although optimizers may rewrite or decorrelate it.

SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department_id = e.department_id  -- correlated: depends on outer e
);

Returns employees earning above their department average.

Equivalent JOIN pattern (often clearer):

WITH dept_avg AS (
  SELECT department_id, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department_id
)
SELECT e.name, e.salary
FROM employees e
JOIN dept_avg d ON e.department_id = d.department_id
WHERE e.salary > d.avg_sal;

In live interviews, write the version you can explain fastest — then mention the alternative.

A strong answer is:

A correlated subquery references the outer row — like comparing each employee to their department average. I can write it as a subquery or as JOIN to a grouped CTE; I pick whichever is clearer to explain.

What interviewers are testing: Whether you pick EXISTS for correlated existence checks and understand IN's NULL behavior with subqueries.

Both test membership; behavior diverges with NULLs and performance on large sets.

SELECT c.customer_id
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.order_date >= CURRENT_DATE - INTERVAL '30 days'
);

SELECT 1 inside EXISTS is idiomatic — the engine only checks row existence.

See EXISTS examples and NOT IN operator for traps.

A strong answer is:

IN for small static lists; EXISTS for correlated has-row checks, especially when the subquery might contain NULLs. I use NOT EXISTS instead of NOT IN when NULLs are possible.

What interviewers are testing: Whether you know the anchor/recursive UNION ALL shape and when hierarchies need cycle guards.

A recursive CTE walks hierarchical or graph-like data by repeatedly joining a working set to itself until no new rows appear.

Structure (memorize this shape):

1. Anchor member — base rows (roots)
2. UNION ALL
3. Recursive member — join anchor/recursive set to source table
4. Termination — recursive part returns no new rows

WITH RECURSIVE emp_tree AS (
  SELECT employee_id, manager_id, name, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, e.name, t.depth + 1
  FROM employees e
  JOIN emp_tree t ON e.manager_id = t.employee_id
)
SELECT * FROM emp_tree;

Uses: org charts, category trees, bill of materials, generating date sequences (gap detection).

Real hierarchies may contain cycles from bad data. In production, add cycle detection, a visited path, or the engine's cycle-handling feature where supported, and consider a maximum-depth guard.

Harder interview topic — fewer loops require writing one from scratch, but recognizing the pattern helps.

A strong answer is:

Recursive CTEs have an anchor query plus a recursive UNION ALL join — I use them for hierarchies like org charts or generating date spines when the database has no built-in generator.

What interviewers are testing: Whether you keep row detail with window functions instead of collapsing groups with GROUP BY.

GROUP BY collapses rows into groups — you lose row-level detail. Window functions (OVER (...)) compute aggregates across a set of rows while keeping every row.

Use GROUP BY when: You only need one row per group (total revenue per day).

Use window functions when: You need both detail and aggregate — each order row plus customer lifetime spend, daily revenue plus running total, employee row plus department rank.

SELECT order_id, customer_id, order_total,
       SUM(order_total) OVER (PARTITION BY customer_id) AS customer_lifetime
FROM orders;

Interview signal: If the question says "for each row, also show…" — think window, not GROUP BY.

See SQL ranking functions for the full family.

A strong answer is:

GROUP BY when I only need one row per group; window functions when I need row-level detail plus a ranking, running total, or comparison to the group — OVER PARTITION BY is the clue.

What interviewers are testing: Whether you choose ROW_NUMBER, RANK, or DENSE_RANK based on tie behavior.

All assign an ordering within a partition; they differ on ties.

Example salaries 100, 100, 90:

• ROW_NUMBER → 1, 2, 3 (tie broken arbitrarily)
• RANK → 1, 1, 3
• DENSE_RANK → 1, 1, 2

Clarify with interviewer before coding top-N problems.

A strong answer is:

ROW_NUMBER gives a unique rank for deduping one row per group; RANK and DENSE_RANK handle ties, with DENSE_RANK leaving no gaps — I ask which tie behavior the question expects.

What interviewers are testing: Whether you specify an explicit ROWS frame for an unambiguous cumulative sum.

A running total is a cumulative SUM over an ordered window — classic analytics pattern.

SELECT order_date,
       revenue,
       SUM(revenue) OVER (
         ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM daily_revenue
ORDER BY order_date;

Frame clause matters:

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — sum one physical row at a time through the current row (clear running total)
• Default frames vary by database and may use RANGE instead of ROWS — with duplicate ORDER BY values, peers can be included in the frame in surprising ways
• In interviews, always specify ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for an unambiguous running total

Variants: PARTITION BY region for per-region running totals.

A strong answer is:

I use SUM() OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) and specify ROWS explicitly — default RANGE frames and duplicate order keys can change results otherwise.

What interviewers are testing: Whether you explain ROWS as physical offsets versus RANGE as value-based boundaries with peer inclusion.

Window frames control which rows participate in a window aggregate. ROWS and RANGE sound similar but behave differently — especially with duplicate ORDER BY values.

PostgreSQL's default with an ORDER BY is effectively RANGE UNBOUNDED PRECEDING ... CURRENT ROW, including peers through the current row's last peer.

Example: Two rows on 2024-01-01 with revenue 100 and 50.

• With only ORDER BY order_date, peer rows have no guaranteed internal order for ROWS frames
• ORDER BY order_date, id plus ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW advances row by row deterministically

Interview rule: For a clear running total, write:

SUM(revenue) OVER (
  ORDER BY order_date, id
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

If multiple rows share the same ORDER BY value and row-by-row order matters, add a tie-breaker — e.g., ORDER BY order_date, id — so ROWS frames are fully deterministic.

Senior candidates who explain ROWS vs RANGE stand out on analytics loops.

A strong answer is:

ROWS counts physical row offsets; RANGE uses value-based boundaries and includes peers at the same sort value. I specify ORDER BY with a tie-breaker and ROWS BETWEEN for a deterministic running total.

What interviewers are testing: Whether you define ordering and partition correctly, handle the first row, and avoid divide-by-zero in percent change.

LAG reads a previous row in the window; LEAD reads the next row — essential for period-over-period metrics.

WITH with_prev AS (
  SELECT order_date,
         revenue,
         LAG(revenue) OVER (ORDER BY order_date) AS prev_revenue
  FROM daily_revenue
)
SELECT order_date,
       revenue,
       revenue - prev_revenue AS dod_change,
       100.0 * (revenue - prev_revenue) / NULLIF(prev_revenue, 0) AS dod_pct
FROM with_prev;

For readability, compute previous revenue once in a CTE when the same LAG() expression is reused — the inline version with repeated LAG() is also correct.

Common uses:

• Day-over-day / month-over-month growth
• Compare each event to the previous event per user (PARTITION BY user_id)
• Funnel analysis with LEAD for "time to next step"

The first row has no prior observation, so its change is naturally NULL. Usually leave it NULL or filter it; only substitute a value if the business definition explicitly requires one.

A strong answer is:

LAG pulls the previous row in order—I use it for day-over-day change with NULLIF on the denominator, leave the first row NULL unless the business requires a substitute, and PARTITION BY entity when the series is per user.

What interviewers are testing: Whether you divide by a window aggregate and guard a zero partition total.

Divide row value by window aggregate over the partition — no self-join needed.

SELECT category,
       product,
       sales,
       100.0 * sales / NULLIF(SUM(sales) OVER (PARTITION BY category), 0) AS pct_of_category
FROM product_sales;

Tips:

• Multiply by 100.0 for percentage; omit if interviewer wants ratio 0–1
• NULLIF on the partition total avoids divide-by-zero; decide whether a zero-total category should return NULL, zero, or be excluded
• Same pattern works for percent of grand total — omit PARTITION BY

A strong answer is:

I divide row sales by SUM(sales) OVER (PARTITION BY category) with NULLIF on the partition total—clarify ratio versus percentage and how zero totals should appear.

What interviewers are testing: Whether you filter window results in an outer query, CTE, or QUALIFY—not in the same-level WHERE.

Window functions are evaluated after WHERE, GROUP BY, and HAVING — you cannot filter on them in the same query level.

Within one query block: FROM → WHERE → GROUP BY → HAVING → window evaluation → projection/order processing. Because the window result does not exist when that query block's WHERE runs, filter it in an outer query/CTE—or use QUALIFY where supported.

Dedup pattern — latest row per user:

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY user_id
           ORDER BY created_at DESC, event_id DESC
         ) AS rn
  FROM events
)
SELECT *
FROM ranked
WHERE rn = 1;

Some databases support QUALIFY (BigQuery, Snowflake) to filter window results inline:

SELECT user_id, event_type, created_at
FROM events
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY user_id
  ORDER BY created_at DESC, event_id DESC
) = 1;

In PostgreSQL, MySQL, and SQL Server you typically wrap the window result in a CTE or subquery instead.

See select first row in group.

A strong answer is:

Window functions run after WHERE, so I filter ROW_NUMBER in an outer query or CTE — latest row per user is PARTITION BY key, ORDER BY time DESC plus a unique tie-breaker, then WHERE rn = 1.

What interviewers are testing: Whether you know NTILE balances row counts into ordered buckets—not value-range quartiles.

NTILE(n) splits an ordered partition into n buckets of roughly equal size — quartiles, deciles, percentile bands.

SELECT customer_id, total_spend,
       NTILE(4) OVER (ORDER BY total_spend DESC) AS spend_quartile
FROM customer_totals;

Use cases in analytics interviews:

• Customer segmentation (top quartile spenders)
• Load balancing buckets
• A/B test stratification

Caveat: Bucket counts are balanced, not bucket ranges — for true percentile cutoffs use PERCENT_RANK or PERCENTILE_CONT (dialect-specific).

A strong answer is:

NTILE splits rows into N ordered buckets — I use it for quartile-style segmentation; I note it balances row counts, not value ranges.

What interviewers are testing: Whether you distinguish adjacent sorted rows from contiguous numeric IDs when defining "consecutive."

Consecutive means adjacent in a defined order — usually by id or timestamp, not numeric value alone. Clarify the ordering column before coding.

LAG approach (readable):

WITH flagged AS (
  SELECT num,
         LAG(num, 1) OVER (ORDER BY id) AS prev1,
         LAG(num, 2) OVER (ORDER BY id) AS prev2
  FROM logs
)
SELECT DISTINCT num
FROM flagged
WHERE num = prev1 AND num = prev2;

Self-join alternative: Join on id+1 and id+2 only if IDs are contiguous—different from adjacent rows after sorting when IDs have gaps.

Clarify whether "consecutive" means adjacent rows after sorting or consecutive numeric IDs/timestamps. LAG solves adjacent-row repetition; joining on id + 1 requires contiguous IDs.

A strong answer is:

I clarify whether consecutive means adjacent sorted rows or contiguous IDs, then use LAG for adjacent-row repetition or a self-join only when IDs are guaranteed contiguous.

What interviewers are testing: Whether you find duplicate values with GROUP BY/HAVING and handle NULL emails appropriately.

Find values that appear more than once — pure GROUP BY + HAVING:

SELECT email
FROM users
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;

If NULL itself is considered a duplicate business value, omit the IS NOT NULL filter.

To list all rows with duplicate emails (not just the email string):

SELECT u.*
FROM users u
JOIN (
  SELECT email FROM users WHERE email IS NOT NULL GROUP BY email HAVING COUNT(*) > 1
) d ON u.email = d.email;

Delete duplicates keeping one row: ROW_NUMBER() in a CTE, delete where rn > 1 (dialect permitting). See select duplicate records and delete duplicate rows.

A strong answer is:

GROUP BY email HAVING COUNT(*) > 1 finds duplicate values when email IS NOT NULL; if I need all duplicate rows or to keep one copy, I join back or use ROW_NUMBER in a CTE.

What interviewers are testing: Whether you parameterize rank and distinguish the Nth salary value from employees at that level.

Parameterize rank instead of hard-coding 2 for second highest:

WITH ranked AS (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT DISTINCT salary
FROM ranked
WHERE rnk = :n;

Discuss with interviewer:

• For the Nth distinct salary value, use SELECT DISTINCT salary WHERE rnk = :n
• For employees at the Nth salary level, return employee columns without DISTINCT
• Empty result if fewer than N distinct salary levels exist

A strong answer is:

I rank salaries with DENSE_RANK or ROW_NUMBER depending on tie rules, filter WHERE rnk = N, and discuss what happens when ties exist or N exceeds distinct values.

What interviewers are testing: Whether you define cohort, return day, timezone, and rate calculation with a safe denominator.

Retention = fraction of a cohort that returns on a target day after signup (or first action).

Convert timestamps to the reporting timezone before deriving cohort/activity dates when the product is global. Confirm the signup table has one row per user.

Steps:

1. Define cohort — users by signup date
2. Define return event — any activity on day 7 (or exactly day 7 — clarify!)
3. retained / cohort_size per cohort date

WITH cohort AS (
  SELECT user_id, DATE(signup_at) AS signup_date
  FROM users
),
activity AS (
  SELECT user_id, DATE(event_at) AS activity_date
  FROM events
)
SELECT c.signup_date,
       COUNT(DISTINCT c.user_id) AS cohort_size,
       COUNT(DISTINCT a.user_id) AS retained_d7,
       1.0 * COUNT(DISTINCT a.user_id)
         / NULLIF(COUNT(DISTINCT c.user_id), 0) AS d7_retention_rate
FROM cohort c
LEFT JOIN activity a
  ON c.user_id = a.user_id
 AND a.activity_date = c.signup_date + INTERVAL '7 days'
GROUP BY c.signup_date;

Clarify: Calendar day 7 vs "any time in week 2" changes the join. Report rate (0–1 or multiply by 100 for percent), not only numerator and denominator. Rolling retention windows use the same cohort logic with a different day offset.

A strong answer is:

I define the cohort by signup date, LEFT JOIN activity on the target return day, and return cohort_size, retained_d7, and d7_retention_rate with NULLIF on the denominator — clarifying whether day 7 is an exact calendar day or a window.

What interviewers are testing: Whether you count distinct users in a trailing calendar window with inclusive-day and timezone clarity.

Rolling 7-day active users = distinct users with at least one event in the last 7 days ending on each report date — a staple analytics metric (DAU/WAU cousins).

Approach: For each calendar day, count distinct user_id where event_date is between report_date - 6 days and report_date (7 days inclusive — clarify definition).

WITH daily_users AS (
  SELECT DATE(event_at) AS event_date, user_id
  FROM events
  GROUP BY 1, 2
),
spine AS (
  SELECT DISTINCT event_date AS report_date FROM daily_users
)
SELECT s.report_date,
       COUNT(DISTINCT d.user_id) AS rolling_7d_active_users
FROM spine s
JOIN daily_users d
  ON d.event_date BETWEEN s.report_date - INTERVAL '6 days' AND s.report_date
GROUP BY s.report_date
ORDER BY s.report_date;

Alternatives: RANGE window on dates (dialect-specific), or self-join with date inequality (watch performance).

Production note: The spine above uses only dates that appear in events — fine for a short interview answer. In production, use a real date spine (generate_series, recursive CTE, or dim_date) so days with zero activity still appear in the report.

Clarify with interviewer: 7 days inclusive vs trailing 168 hours; timezone for DATE(event_at).

Pairs naturally with Day-7 retention (cohort) — rolling active is calendar-window, not cohort-based.

A strong answer is:

I dedupe user-day events, then for each report date count distinct users with activity in the trailing 7 calendar days using BETWEEN on dates — and I clarify inclusive days and timezone up front.

What interviewers are testing: Whether you aggregate clicks and impressions at the correct event grain with safe division.

CTR = clicks / impressions — usually aggregated per day or campaign.

SELECT DATE(event_at) AS event_date,
       SUM(CASE WHEN event_type = 'click' THEN 1 ELSE 0 END) AS clicks,
       SUM(CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END) AS impressions,
       1.0 * SUM(CASE WHEN event_type = 'click' THEN 1 ELSE 0 END)
         / NULLIF(SUM(CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END), 0) AS ctr
FROM events
GROUP BY DATE(event_at);

Confirm the table grain first—this query assumes one event row represents one click or one impression; if rows contain counts, sum the count column instead.

Proactive mentions:

• NULLIF(..., 0) prevents divide-by-zero on days with no impressions
• Use 1.0 * or CAST for floating-point division
• Alternative: COUNT(*) FILTER (WHERE event_type = 'click') in PostgreSQL

A strong answer is:

I sum clicks and impressions per day with conditional aggregation, divide with NULLIF to avoid zero impressions, and use floating-point division for CTR.

What interviewers are testing: Whether you name the dialect-specific median approach or a ranked fallback.

Median = middle value of ordered data — dialect support varies.

PostgreSQL (ordered-set aggregate):

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees;

SQL Server requires an OVER clause:

SELECT DISTINCT
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary)
    OVER () AS median_salary
FROM employees;

PostgreSQL-style window fallback (integer division semantics vary by engine):

WITH ordered AS (
  SELECT salary,
         ROW_NUMBER() OVER (ORDER BY salary) AS rn,
         COUNT(*) OVER () AS cnt
  FROM employees
)
SELECT AVG(salary) AS median_salary
FROM ordered
WHERE rn IN ((cnt + 1) / 2, (cnt + 2) / 2);

Even count averages the two middle values. State your dialect — interviewers accept logic with minor syntax fixes.

A strong answer is:

In PostgreSQL I use the aggregate PERCENTILE_CONT(0.5) form; in SQL Server I add OVER (). Otherwise I rank rows and average the middle one or two. I say which dialect I am assuming.

What interviewers are testing: Whether you compare facts to a complete date spine to find missing days.

Detect gaps by comparing a complete date spine to facts.

Concept:

SELECT d.day
FROM dates d
LEFT JOIN daily_metrics m ON d.day = m.day
WHERE m.day IS NULL;

Build the spine:

• PostgreSQL: generate_series
• Recursive CTE from min to max date
• Warehouse dimension table dim_date

Tests whether you think in complete grids — common in analytics screens ("which days did we not report revenue?").

A strong answer is:

I generate a continuous date series, LEFT JOIN facts, and filter WHERE the fact key IS NULL — using generate_series or a recursive CTE depending on dialect.

What interviewers are testing: Whether you explain normalization goals and when denormalization helps read-heavy analytics.

Normalization organizes tables to reduce redundancy and update anomalies — each fact lives in one place, linked by keys.

Normal forms (interview shorthand):

OLTP / application databases favor normalized schemas — orders, customers, line items as separate tables.

When to denormalize:

• Analytics / warehouses — star schema with fact + dimension tables trades storage for faster reads
• Read-heavy dashboards — pre-joined wide tables or materialized aggregates
• Latency-sensitive reports when join cost at query time exceeds duplication cost

Interview framing: "Normalized for writes and integrity; denormalized for read performance when we measure the trade-off."

A strong answer is:

Normalization reduces redundancy and update bugs through 1NF–3NF; I denormalize for analytics read performance when measured join cost justifies duplication — star schemas in warehouses, not random duplicate columns in OLTP.

What interviewers are testing: Whether you know when indexes help selective lookups and when writes or non-sargable predicates limit them.

An index is a lookup structure (often B-tree) that speeds find operations at the cost of write maintenance and storage.

Helps when:

• WHERE filters on high-selectivity columns (few rows match)
• JOIN keys and foreign keys used in lookups
• ORDER BY columns that align with index order (covering indexes avoid table lookups)

Hurts or helps little when:

• Tiny tables (full scan is fine)
• A standalone low-selectivity index is often less useful for broad queries, but usefulness depends on data distribution, predicates, index design, and engine — low-cardinality columns can still help in composite, filtered/partial, or covering indexes
• Heavy INSERT/UPDATE/DELETE workloads (every index must update)
• Functions on indexed columns — WHERE YEAR(created_at) = 2024 often cannot use index on created_at (use range on raw column instead)

Composite index tip: Equality-prefix then range is a common pattern, but choose column order from the actual query workload and execution plan — also consider sorting/grouping, selectivity, prefix reuse, covering goals, and optimizer capabilities.

A strong answer is:

I keep predicates sargable where possible—for example a date range rather than extracting YEAR()—unless the engine has an expression or function index that matches the predicate.

What interviewers are testing: Whether you explain ACID properties and isolation trade-offs beyond keyword recall.

Transactions group SQL statements into one logical unit — critical for payments, inventory, and any multi-step write.

Interview depth: Isolation levels and anomalies vary by engine. Mention isolation anomalies — dirty read, non-repeatable read, phantom read — and that higher isolation costs concurrency.

See SQL transactions explained.

A strong answer is:

ACID means transactions are all-or-nothing, keep valid state, isolate concurrent sessions, and persist after commit. I pick isolation level based on consistency needs vs concurrency.

What interviewers are testing: Whether you diagnose from the execution plan and row counts before guessing at indexes or rewriting syntax.

Correctness first — a fast wrong answer is worse than a slow right one. Then optimize with evidence, not guesses.

Structured approach:

1. EXPLAIN / EXPLAIN ANALYZE — sequential scan vs index scan, row estimates vs actuals, join order. EXPLAIN ANALYZE executes the query, so use it carefully on expensive production workloads; start with EXPLAIN or a safe replica when needed
2. Check whether frequently joined or filtered key columns have useful indexes; foreign-key constraints do not universally imply a child-side lookup index
3. Reduce selected columns — avoid SELECT * in production pipelines
4. Write selective, sargable predicates and verify in the plan that filters are applied efficiently; rewrite WHERE YEAR(d) = 2024 to a date range when needed
5. Fix grain — accidental row explosion from joins inflates work
6. Pre-aggregate — materialized views or summary tables for dashboards

In interviews: Write the correct query, narrate one optimization you would check on EXPLAIN, and mention measuring before/after.

A strong answer is:

I make the query correct, inspect the plan, fix join grain and indexing issues, keep predicates sargable, and only then consider materialized views for repeated heavy reports.