SQL Interview Questions

WHERE filters rows before grouping; HAVING filters groups after aggregation. Cut rows early with WHERE, then filter aggregate results like COUNT or SUM with HAVING.

SELECT dept, COUNT(*) AS c
FROM emp
WHERE status = 'active'
GROUP BY dept
HAVING COUNT(*) > 5;

Common mistake: You can put COUNT() in WHERE. Aggregate filters belong in HAVING.

Trade-off: Filter with WHERE whenever possible so the grouping step touches fewer rows.

Follow-up to expect: Which runs first, and why does order matter for performance?

INNER JOIN returns only matched rows from both tables; LEFT JOIN returns every left-table row with NULLs where the right side has no match.

SELECT o.id, p.name
FROM orders o
LEFT JOIN payments p ON p.order_id = o.id;

Common mistake: A LEFT JOIN quietly turns into an inner join if you filter the right table on a non-NULL value in WHERE. Move that test into ON.

Trade-off: Use LEFT JOIN to surface missing matches; use INNER JOIN to require them.

Follow-up to expect: Find orders with no payment: which join and filter do you use?

Aggregates (except COUNT(*)) skip NULLs, so AVG and SUM ignore missing values. Any comparison to NULL yields unknown, not true, so those rows fail a WHERE filter.

SELECT COUNT(*) AS n_all, COUNT(col) AS n_valued, AVG(col)
FROM t
WHERE col <> 5;   -- NULL rows are excluded here too

Common mistake: COUNT(col) equals COUNT(*). It does not when col is NULL. And col = NULL is never true; use col IS NULL.

Trade-off: Decide deliberately whether NULLs should count; COALESCE to a sentinel only when that is the real intent.

Follow-up to expect: How would you also keep the NULL rows when filtering col <> 5?

The MAX(salary) < MAX trick misses duplicates and empty groups. A window function ranks distinct salaries so ties share a rank; DENSE_RANK returns the correct second-highest value with ties.

SELECT salary FROM (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS r
  FROM emp
) x WHERE r = 2;

Common mistake: MAX-of-two-MAXs is a clean second place. It is silently wrong on duplicate salaries and when no row matches.

Trade-off: Use DENSE_RANK for the second-highest distinct value, ROW_NUMBER for the second row.

Follow-up to expect: Now return the second-highest salary per department.

DISTINCT removes duplicate result rows by building a sort or hash set of them. That is correct, but it can add a hidden dedup step a plain projection avoids.

Common mistake: DISTINCT "fixes" duplicate rows from a bad join. The real fix is the join grain, not hiding rows.

Trade-off: Prefer fixing the JOIN; use EXISTS for a semi-join instead of DISTINCT to test membership.

Follow-up to expect: You used DISTINCT to hide fan-out. How do you remove the duplication instead?

A windowed aggregate with a frame gives a running sum with no self-join: SUM over the partition ordered by day accumulates rows up to the current one.

SELECT customer_id, day,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY day
  ) AS running_total
FROM orders;

Common mistake: Dropping ORDER BY inside OVER still totals per row. Without it every row shows the whole-partition total.

Trade-off: Window functions beat correlated subqueries and self-joins for readability and often performance.

Follow-up to expect: Turn this into a 7-day moving average. How does the frame change?

SQL uses three-valued logic (true, false, unknown). Comparing to NULL yields unknown, not true, so the row is filtered out. Use IS NULL / IS NOT NULL, and watch NOT IN with NULLs.

SELECT * FROM t WHERE col IS NULL;    -- correct
SELECT * FROM t WHERE col = NULL;     -- returns nothing

Common mistake: col <> 5 keeps NULL rows. It does not, because unknown fails the filter.

Trade-off: Prefer NOT EXISTS over NOT IN when the subquery can return NULLs, to avoid silently empty results.

Follow-up to expect: A NOT IN whose subquery returns one NULL yields nothing. Why?

Two queries, one interview: find duplicates by key with HAVING COUNT(*) > 1 (grain stated first), then dedupe with ROW_NUMBER over (PARTITION BY key ORDER BY a tie-stable column) and delete rows with rn > 1, or keep the min(id). The grade lives in choosing what “one row” means.

WITH ranked AS (
  SELECT id, ROW_NUMBER() OVER (
    PARTITION BY email ORDER BY created_at, id
  ) rn FROM users
)
DELETE FROM users WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

Why it matters: Real data is duplicated more often than it is missing; every analytics and backend loop touches it eventually.

Common mistake: That DISTINCT on the fly fixes the root cause, and that the dedupe is safe without a transaction, a backup, or at least a SELECT of what will be deleted.

Trade-off: Dedupe-at-write (unique index) vs dedupe-at-read (view/row_number) vs batch cleanup. Name the one you would ship and what each costs.

Follow-up to expect: Why does the ORDER BY inside the window need a unique tiebreaker?

UNION dedupes by sorting or hashing the combined result (an extra pass, unknown-cost) and collapses intentional duplicates; UNION ALL concatenates. Analytics queries that counted distinct events twice lose to the dedupe cost; report queries that need duplicate collapse lose to correctness.

Why it matters: It is a one-line question that opens the discussion the interviewer actually wants: do you know what your result grain and duplicates are?

Common mistake: Defaulting to UNION for safety. Hiding duplicates is not handling them; and the mirror error of UNION ALL on two branchy joins that double-count.

Trade-off: If you need both dedupe and performance, the answer is often GROUP BY key explicitly. Visible, optimizable, and explainable.

Follow-up to expect: Two branches, 3M rows each, 10 duplicates. Which do you pick and why is the answer not always UNION?

Two-step modeling: assign each user a signup-week cohort and each activity an event-week number, then join on user and GROUP BY (cohort, week) with COUNT(DISTINCT user). The classic self-join-free shape is a fact join to a cohort dimension.

WITH cohorts AS (
  SELECT user_id, DATE_TRUNC('week', signup_at) AS cw
  FROM users
),
acts AS (
  SELECT user_id, DATE_TRUNC('week', seen_at) AS ew
  FROM sessions GROUP BY 1, 2
)
SELECT c.cw, a.ew - c.cw AS wk,
       COUNT(DISTINCT a.user_id) AS retained
FROM cohorts c JOIN acts a USING (user_id)
GROUP BY 1, 2;

Why it matters: The retention question is where analyst loops verify you think in grains and date functions, not in copy-pasted window snippets.

Common mistake: Counting events instead of distinct users (one churned power-user becomes 40% of the row), and letting date-truncation timezone assumptions shift cohorts silently.

Trade-off: The matrix wants zeros for no-activity cells. Cross-join cohorts x weeks as the frame or let the BI layer backfill; say which and why.

Follow-up to expect: Now add a rolling 28-day active version. What changes in the join?