How to approach SQL interview problems
Clarify the tables, grain, desired output, null behavior, and duplicate rules before writing SQL. Then explain correctness and likely performance bottlenecks.
SQL fundamentals
What is the difference between WHERE and HAVING?
WHERE filters rows before grouping. HAVING filters groups after GROUP BY and can use aggregate conditions.
INNER JOIN versus LEFT JOIN?
INNER JOIN returns matching rows from both sides. LEFT JOIN keeps every left-side row and fills unmatched right-side columns with NULL.
UNION versus UNION ALL?
UNION removes duplicate result rows, which requires extra work. UNION ALL concatenates results without deduplication and is usually faster.
Primary key versus foreign key?
A primary key uniquely identifies a row. A foreign key references a key in another table and can enforce referential integrity.
How does NULL behave?
NULL represents missing or unknown data. Compare with IS NULL, not equality, and account for three-valued logic in filters and joins.
Aggregation and analytical SQL
What does GROUP BY do?
It collapses rows into groups defined by one or more expressions so aggregate functions can calculate a value per group.
What is a window function?
A window function calculates across related rows without collapsing them. It is useful for ranks, running totals, lagged values, and partition-level metrics.
ROW_NUMBER, RANK, and DENSE_RANK: what differs?
ROW_NUMBER is always unique, RANK leaves gaps after ties, and DENSE_RANK does not leave gaps.
How do you calculate a running total?
Use SUM over a window ordered by the sequence column, with a suitable frame such as rows from unbounded preceding to current row.
How do you find duplicate records?
Group by the candidate key columns and filter groups with COUNT greater than one. Define carefully which columns make a duplicate.
Data modeling and performance
What is an index?
An index is an auxiliary structure that speeds selected reads but consumes storage and adds maintenance cost to writes. Column order matters in composite indexes.
What is normalization?
Normalization separates data to reduce duplication and update anomalies. Analytical workloads may deliberately denormalize for simpler or faster reads.
What is a query execution plan?
It shows the operations selected by the optimizer, including scans, joins, sorts, and estimated costs. Compare estimates with actual row counts when diagnosing issues.
How do you optimize a slow query?
Measure the plan, reduce scanned rows, use appropriate indexes, avoid unnecessary columns and repeated work, update statistics, and confirm improvement under realistic load.
CTE versus subquery?
Both can express intermediate results. A CTE often improves readability and recursion support, but performance depends on the database optimizer and materialization behavior.
Transactions and interview exercises
What does ACID mean?
Atomicity, consistency, isolation, and durability describe properties that help transactions preserve valid data despite concurrency and failures.
What are isolation levels?
Isolation levels trade concurrency for protection against anomalies such as dirty reads, non-repeatable reads, and phantoms. Exact behavior varies by database.
How do you find the second-highest salary?
One approach uses DENSE_RANK over distinct salaries and selects rank two. Clarify whether ties should return multiple employees.
How do you get the latest row per customer?
Use ROW_NUMBER partitioned by customer and ordered by timestamp descending, then select row number one, with a tie-breaker for determinism.
How would you analyze monthly retention?
Define cohorts by first activity month, join later activity by user, calculate periods since acquisition, and divide retained users by the original cohort size.
Where Cluegent helps
Cluegent supports permitted live workflows with transcript context, typed prompts, screenshot-aware answers, resume context, custom response behavior, quick action buttons, and a private desktop overlay. It is most useful when you already understand the subject and need help staying structured under pressure.
Frequently asked questions
Which SQL dialect should I use in interviews?
Use the dialect requested by the employer. When none is specified, state your assumptions and use broadly supported SQL where possible.
Are SQL interviews only for data roles?
No. Backend, full-stack, analytics, data science, product, finance, and operations roles commonly test SQL.