Hard SQL Interview Questions

Show answer

The optimiser uses either hash aggregation, building a hash table keyed by group values, which is fast for unsorted data with enough memory, or stream (sort) aggregation, which needs input sorted by the group keys, often from an index. Large groups that do not fit memory spill to disk. Indexes on grouping columns and pre-filtering reduce the cost.

Calculate the median salary per department.

Show answer

PostgreSQL and standard SQL: SELECT department_id, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary FROM employees GROUP BY department_id; SQL Server uses PERCENTILE_CONT as a window function with OVER (PARTITION BY department_id). Median resists outliers better than average for salary analysis.

A daily aggregation over 2 billion rows is too slow for a dashboard. What would you propose?

Show answer

Avoid recalculating raw data on every request. Build a summary table or materialised view at daily grain refreshed incrementally, partition the fact table by date, and use columnar storage if available. Pre-compute common metrics, index the summary on dimensions used in filters, and let the dashboard query the summary. Validate summary totals against raw data periodically.

What is a recursive CTE and how does it work?

Show answer

A recursive CTE has an anchor member that returns the starting rows and a recursive member that references the CTE itself, joined with UNION ALL. The database repeats the recursive part using the previous iteration's rows until no new rows appear. It is used for hierarchies like org charts, bill of materials and generating sequences. Add a depth limit to prevent infinite loops on cyclic data.

What is a lateral join or CROSS APPLY?

Show answer

A lateral join, written as JOIN LATERAL in PostgreSQL and MySQL 8 or CROSS APPLY and OUTER APPLY in SQL Server, lets a subquery in FROM reference columns of tables listed before it. It acts like a correlated subquery that can return multiple rows and columns, ideal for top N per group, for example the three latest orders for each customer.

Write a recursive CTE to show an employee hierarchy with levels.

Show answer

WITH RECURSIVE org AS ( SELECT emp_id, name, manager_id, 1 AS lvl FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.emp_id, e.name, e.manager_id, o.lvl + 1 FROM employees e JOIN org o ON e.manager_id = o.emp_id ) SELECT * FROM org ORDER BY lvl, name; SQL Server omits the RECURSIVE keyword.

Get the latest 3 orders for every customer using a lateral join.

Show answer

SELECT c.customer_id, o.order_id, o.order_date FROM customers c CROSS JOIN LATERAL ( SELECT order_id, order_date FROM orders WHERE orders.customer_id = c.customer_id ORDER BY order_date DESC LIMIT 3 ) o; With an index on (customer_id, order_date), each lookup is fast. SQL Server uses CROSS APPLY with TOP 3.

A long report query has eight nested subqueries and nobody can maintain it. How would you refactor it?

Show answer

Break it into CTEs with meaningful names, each producing one business step such as active customers, order totals and returns. Remove repeated logic, standardise filters, and add comments on business rules. Test each step's row count against the original output. If parts are reused across reports, move them into views. Finally compare results and execution plans to ensure nothing changed.

A recursive CTE on a category tree runs forever. What is the likely cause and fix?

Show answer

The data probably has a cycle, such as category A as parent of B and B as parent of A, so recursion never ends. Add a depth column and stop at a maximum level, track a path string or array and exclude nodes already visited, and use database limits such as MAXRECURSION in SQL Server. Then fix the cyclic data at the source with constraints or validation.

Tell me about a time you set SQL coding standards for a team.

Show answer

Situation: our analytics team of eight wrote queries in very different styles, making reviews slow. Task: create common standards. Action: I drafted guidelines on naming, CTE usage, formatting, comments and anti-patterns, added a linter to the repository, and held a short workshop. Result: review time dropped noticeably, new joiners onboarded faster, and production errors from ambiguous queries reduced.

How do you handle a situation where business users dispute your numbers?

Show answer

Situation: a regional manager said my sales report undercounted his region. Task: resolve it with facts. Action: I shared the query logic and definitions, compared a sample of his records with the database, and found his team counted orders on booking date while the report used invoice date. Result: we agreed on invoice date with finance, documented it, and added the definition to the report.

Why can't you use a window function in WHERE, and how do you filter on it?

Show answer

Window functions are evaluated after WHERE, GROUP BY and HAVING in the logical order, as part of SELECT, so their results do not exist when WHERE runs. To filter, compute the window function in a subquery or CTE and filter in the outer query. Some databases such as Snowflake and BigQuery support a QUALIFY clause for filtering window results directly.

What are the performance considerations of window functions?

Show answer

Each distinct PARTITION BY and ORDER BY combination usually needs a sort, which is expensive on large data and may spill to disk. Reuse the same window definition where possible, using a WINDOW clause, and create indexes that match the partition and order columns so sorts can be avoided. Filter rows before applying windows and avoid unnecessary large frames.

Find users who logged in on 3 or more consecutive days.

Show answer

WITH d AS ( SELECT DISTINCT user_id, login_date FROM logins ), g AS ( SELECT user_id, login_date, login_date - CAST(ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY login_date) AS INT) AS grp FROM d ) SELECT user_id, MIN(login_date) AS start_day, COUNT(*) AS days FROM g GROUP BY user_id, grp HAVING COUNT(*) >= 3; Consecutive dates minus row number give the same group value (PostgreSQL date arithmetic).

Calculate the time each user spent between consecutive events.

Show answer

SELECT user_id, event_time, event_name, event_time - LAG(event_time) OVER ( PARTITION BY user_id ORDER BY event_time ) AS gap_since_prev FROM events ORDER BY user_id, event_time; Gaps above 30 minutes can start a new session using a SUM of a flag over the same window, a common sessionisation pattern.

A retention report needs the percentage of users active in each month after signup. How would you build it in SQL?

Show answer

Compute each user's signup month with MIN over their activity, then derive month number as the difference between activity month and signup month. Group by cohort month and month number, counting distinct users. Divide by the cohort size using FIRST_VALUE or a window SUM over month zero within each cohort. Validate with a few known users and handle users with no activity.

What are slowly changing dimensions in data warehousing?

Show answer

Slowly changing dimensions track changes in dimension attributes over time. Type 1 overwrites old values with no history. Type 2 adds a new row with effective dates and a current flag, preserving full history, such as a customer moving city. Type 3 keeps a previous value column for limited history. Type 2 is most common when reports must reflect attributes as they were at transaction time.

What is BCNF and how is it different from 3NF?

Show answer

Boyce-Codd Normal Form requires that for every functional dependency, the determinant is a candidate key. 3NF allows a dependency where the dependent attribute is part of a candidate key, while BCNF does not. For example, if a teacher teaches only one subject but a student-subject pair determines the teacher, the table is in 3NF but not BCNF and should be split.

Product prices change often, but old orders must keep the price at the time of purchase. How do you design it?

Show answer

Store the unit price, discount and tax in order_items when the order is created instead of reading the current product price, so history is not affected by updates. Maintain a product_price_history table with effective_from and effective_to dates for analysis. Reports on past orders use order_items values, while catalogue pages use the current price.

Your team debates using UUIDs or auto-increment integers as primary keys. What do you advise?

Show answer

Auto-increment integers are compact, fast to index and naturally ordered, but expose counts and are hard to generate across distributed systems. UUIDs are globally unique and good for distributed inserts and merging data, but larger and random, causing index fragmentation. Time-ordered UUIDs such as UUIDv7 reduce fragmentation. Choose based on scale, distribution and security needs.

How do you handle disagreement with developers about schema changes?

Show answer

Situation: a developer wanted to drop foreign keys to speed up inserts. Task: protect data integrity without blocking him. Action: I measured insert performance with and without constraints, showed the overhead was under five percent, and suggested batch inserts and proper indexes instead. Result: we kept the constraints, insert speed improved through batching, and orphan records stayed at zero.

What is table partitioning and how does it help performance?

Show answer

Partitioning splits a large table into smaller physical pieces by a key, such as range partitions by month, list partitions by region, or hash partitions. Queries filtering on the partition key read only relevant partitions, called partition pruning. It also makes maintenance easier, such as dropping old months instantly. It does not help queries that do not filter on the partition key.

What is parameter sniffing and how do you handle it?

Show answer

In databases that cache plans, such as SQL Server, the plan for a parameterised query is compiled using the first parameter values. If those values are unusual, the cached plan may be poor for typical values, causing sudden slowness. Fixes include OPTION (RECOMPILE) for that query, OPTIMIZE FOR hints, plan guides, splitting procedures for different data shapes, or updating statistics.

Implement keyset pagination instead of OFFSET for large tables.

Show answer

Slow for deep pages: SELECT * FROM orders ORDER BY id LIMIT 50 OFFSET 500000; Keyset pagination: SELECT * FROM orders WHERE id > :last_seen_id ORDER BY id LIMIT 50; OFFSET reads and discards all skipped rows, while keyset seeks directly using the index on id, keeping each page fast.

Inserts into a heavily used table have become slow. What could be causing it?

Show answer

Too many indexes increase write cost, so audit and remove unused ones. Random UUID primary keys cause page splits and fragmentation. Triggers, foreign key checks without indexes on child columns, lock contention from long transactions, and log disk bottlenecks also slow inserts. Use batch inserts, sequential keys, proper indexing of foreign keys and monitor waits to find the actual bottleneck.

The dashboard queries hit the production database and slow down the app at month end. What do you propose?

Show answer

Separate workloads: route reporting to a read replica or data warehouse, pre-aggregate month-end metrics into summary tables refreshed after close, and cache results for dashboards. Tune the heaviest queries with indexes and better predicates. Schedule batch reports outside business hours and set resource limits or query timeouts for reporting users so customer transactions are protected.

A query with many OR conditions across different columns is slow. How can you optimise it?

Show answer

OR across different columns often prevents a single index seek. Rewrite it as UNION ALL of separate queries, each using its own index, removing duplicates carefully if needed, or use IN for multiple values of the same column. Some optimisers can combine indexes with bitmap or index merge plans. Compare plans and timings to choose the best approach.

How do you balance performance tuning with delivery deadlines?

Show answer

Situation: a release was due in two days, but a key query was slow under load testing. Task: fix the risk without delaying the release. Action: I identified the top two bottlenecks from the plan, applied an index and a query rewrite that were low risk, and logged deeper redesign work as a follow-up ticket with data. Result: the release went live on time and the redesign was completed in the next sprint.

What is MVCC and how does it improve concurrency?

Show answer

Multi-Version Concurrency Control keeps multiple versions of rows, so readers see a consistent snapshot without blocking writers, and writers do not block readers. PostgreSQL stores old row versions in the table and cleans them with VACUUM, while MySQL InnoDB uses undo logs. MVCC enables high concurrency in read-heavy systems, but long-running transactions keep old versions alive and cause bloat.

What is the difference between optimistic and pessimistic locking?

Show answer

Pessimistic locking locks rows when read, using SELECT ... FOR UPDATE, so other transactions wait; it suits high-conflict cases such as seat booking. Optimistic locking does not lock but checks at update time that the row has not changed, usually with a version column: UPDATE ... SET version = version + 1 WHERE id = 1 AND version = 5. If zero rows update, the application retries. It suits low-conflict workloads.

Create an audit trigger that logs salary changes.

Show answer

PostgreSQL: CREATE FUNCTION log_salary() RETURNS trigger AS $$ BEGIN IF NEW.salary <> OLD.salary THEN INSERT INTO salary_audit(emp_id, old_sal, new_sal, changed_at) VALUES (OLD.emp_id, OLD.salary, NEW.salary, NOW()); END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg_salary AFTER UPDATE ON employees FOR EACH ROW EXECUTE FUNCTION log_salary();

Your application logs frequent deadlock errors at peak hours. How do you investigate and reduce them?

Show answer

Capture deadlock graphs or logs to see which statements and resources are involved. Common fixes are accessing tables and rows in the same order in all code paths, keeping transactions short, adding indexes so updates lock fewer rows, avoiding user interaction inside transactions, and using lower isolation where safe. Add retry logic for deadlock victims since some deadlocks cannot be fully eliminated.

A long-running report transaction is causing table bloat and blocking. What do you do?

Show answer

Long transactions hold snapshots and locks, preventing cleanup of old row versions and blocking DDL or writers. Move the report to a read replica, break it into smaller batches, use read committed snapshot where supported, and avoid leaving transactions open in client tools. Set statement or idle-in-transaction timeouts, and monitor sessions with long transaction durations.

How would you design data archiving for a table growing by 10 million rows a month?

Show answer

Partition the table by month so old partitions can be detached or moved to an archive table or cheaper storage without heavy deletes. Define a retention policy with business and compliance teams, for example 24 months online. Keep archived data queryable through a separate schema or warehouse, update indexes and statistics, and automate the process with monitoring and alerts.

Finance asks for the count of customers whose total spend crossed 1 lakh in each city. How do you write it?

Show answer

Aggregate in two levels. First compute total spend per customer and city in a CTE: SELECT city, customer_id, SUM(amount) AS spend FROM orders GROUP BY city, customer_id. Then in the outer query: SELECT city, COUNT(*) FROM cte WHERE spend > 100000 GROUP BY city. Trying to do it in one GROUP BY by city fails because the threshold applies to each customer, not each city.

An e-commerce app needs products with very different attributes, such as phones and shirts. How do you model them?