40 questions with answers for candidates with 10+ years of experience — 11 medium, 29 hard.
What interviewers expect: 10 years experience
At ten years and above, roles include principal engineer, data architect, head of data engineering or DBA manager. Interviews focus on platform strategy, team building, vendor and cost decisions, and aligning data systems with business goals. Technical depth is assessed through architecture reviews and trade-off discussions rather than writing queries.
This set is weighted towards Aggregation and Grouping (6), Transactions and Advanced SQL (6), Window Functions (6), Indexes and Performance (5).
Platform strategy Team leadership Cost and vendor decisions Architecture reviews
Solved at this levelStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
✓ 3 questions free • Unlock the rest in seconds
01
Rewrite a non-SARGable date filter to use an index.
Slow:
SELECT * FROM orders
WHERE YEAR(order_date) = 2025
AND MONTH(order_date) = 3;
Fast:
SELECT * FROM orders
WHERE order_date >= '2025-03-01'
AND order_date < '2025-04-01';
The second form compares the raw column, so an index on order_date can be used with a range seek.
02
Average order value looks too low in a report. What could be wrong?
Check the grain: if the query averages order items instead of orders, each line counts separately and the average drops. Joins can also duplicate rows. Verify whether cancelled or zero-value orders are included and how NULL amounts are treated. Compute SUM(amount) / COUNT(DISTINCT order_id) at order level and compare with business definitions before changing the report.
Avoid indexes on small tables where scans are cheap, on columns with very low selectivity such as a boolean flag used alone, on tables with heavy writes and few reads, and duplicates of existing indexes. Every index slows inserts and updates and uses memory and storage. Review unused indexes periodically with database statistics and drop them after checking dependencies.
04
The dashboard queries hit the production database and slow down the app at month end. What do you propose?
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.
05
What is a deadlock and how do databases handle it?
A deadlock occurs when two transactions each hold a lock the other needs, so both wait forever. For example, T1 locks account A and wants B, while T2 locks B and wants A. The database detects the cycle and kills one transaction as the victim, rolling it back with an error. Applications should retry the victim and access resources in a consistent order.
06
Finance asks for the count of customers whose total spend crossed 1 lakh in each city. How do you write it?
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.
07
How do you balance performance tuning with delivery deadlines?
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.
08
Get the latest 3 orders for every customer using a lateral join.
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.
09
Implement keyset pagination instead of OFFSET for large tables.
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.
Isolation levels balance consistency and concurrency. Read Uncommitted allows dirty reads. Read Committed, the default in PostgreSQL and SQL Server, prevents dirty reads but allows non-repeatable reads. Repeatable Read, the default in MySQL InnoDB, prevents non-repeatable reads, and InnoDB also prevents most phantoms with gap locks. Serializable prevents all anomalies but reduces concurrency. Choose based on business correctness needs.
11
A long report query has eight nested subqueries and nobody can maintain it. How would you refactor it?
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.
12
What is the difference between putting a condition in ON versus WHERE in a LEFT JOIN?
In a LEFT JOIN, a condition in ON filters the right table before matching, so all left rows are still kept. A condition on the right table in WHERE runs after the join and removes rows where the right side is NULL, effectively turning it into an inner join. To get all customers with only their 2025 orders, put the date condition in ON.
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.
14
What are the performance considerations of window functions?
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.
15
How do you handle a situation where business users dispute your numbers?
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.
16
A retention report needs the percentage of users active in each month after signup. How would you build it in SQL?
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.
17
Your team debates using UUIDs or auto-increment integers as primary keys. What do you advise?
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.
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.
19
What is the difference between optimistic and pessimistic locking?
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.
20
Tell me about a time you set SQL coding standards for a team.
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.
21
Tell me about a time you had to deliver a report under a tight deadline.
Situation: the sales head needed region-wise quarterly numbers for a client meeting the same afternoon. Task: deliver accurate figures within three hours. Action: I confirmed metric definitions, reused tested queries, wrote one grouped query with ROLLUP for subtotals and cross-checked totals with the finance summary. Result: the report was delivered in two hours, and it later became a scheduled weekly report.
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.
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.
24
An e-commerce app needs products with very different attributes, such as phones and shirts. How do you model them?
Options are a common products table plus category-specific tables (table per type), an entity-attribute-value table, or a JSON or JSONB column for flexible attributes. A practical design keeps shared fields like name, price and category as columns, stores variable attributes in JSONB with indexes on frequently filtered keys, and validates required attributes in the application or with check constraints.
25
Product prices change often, but old orders must keep the price at the time of purchase. How do you design it?
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.
26
Write a query to pivot order counts by status into columns.
SELECT
customer_id,
SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) AS delivered,
SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled,
SUM(CASE WHEN status = 'Returned' THEN 1 ELSE 0 END) AS returned
FROM orders
GROUP BY customer_id;
Conditional aggregation works in every database, unlike vendor-specific PIVOT syntax.
27
How do SQL dialects differ and how do you write portable SQL?
Databases differ in row limiting (LIMIT, TOP, FETCH), string concatenation (|| versus + versus CONCAT), date functions, identity columns (AUTO_INCREMENT, IDENTITY, SERIAL), upsert syntax and case sensitivity. For portable SQL, use ANSI features such as CASE, COALESCE, standard joins, window functions and FETCH FIRST where supported, keep vendor-specific logic isolated, and test against each target database.
28
A report sums amounts stored in a FLOAT column and finance says totals are off by a few paise. Why?
FLOAT stores approximate binary values, so decimals such as 0.1 cannot be represented exactly and rounding errors accumulate over millions of rows. Money should be stored as DECIMAL or NUMERIC with fixed scale, for example DECIMAL(12,2). Fix it by migrating the column type carefully, validating totals before and after, and rounding only at presentation time.
29
What are slowly changing dimensions in data warehousing?
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.
30
A join between two large tables takes minutes. How would you speed it up?
Check the execution plan for scans, join type and row estimates. Ensure join keys have indexes and matching data types, because implicit conversion blocks index use. Filter both sides early, select only needed columns, update statistics, and avoid functions on join columns. For very large data, consider partitioning, pre-aggregated tables, or batch processing outside peak hours.
WITH m AS (
SELECT DATE_TRUNC('month', order_date) AS month,
SUM(amount) AS revenue
FROM orders GROUP BY 1
)
SELECT month, revenue,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2) AS mom_pct
FROM m ORDER BY month;
NULLIF avoids division by zero.
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();
33
What is the difference between ROWS and RANGE frames?
ROWS counts physical rows, so 2 PRECEDING means exactly two rows before. RANGE works on values of the ORDER BY column, so rows with the same value are treated together, and value ranges like INTERVAL '7 days' PRECEDING are possible in some databases. With duplicate dates, a RANGE running total jumps for all ties at once, while ROWS increases row by row.
34
Show all dates in a month with sales, including days with zero sales.
WITH days AS (
SELECT generate_series(DATE '2025-01-01',
DATE '2025-01-31', INTERVAL '1 day')::date AS d
)
SELECT d.d, COALESCE(SUM(s.amount), 0) AS sales
FROM days d
LEFT JOIN sales s ON s.sale_date = d.d
GROUP BY d.d
ORDER BY d.d;
This PostgreSQL example uses a date series; other databases use a calendar table.
35
Find users who logged in on 3 or more consecutive days.
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).
36
Calculate the time each user spent between consecutive events.
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.
Optimisers choose between nested loop joins, efficient when one side is small and the other has an index on the join key, hash joins, which build a hash table on the smaller input and suit large unsorted equality joins, and merge joins, which work on inputs sorted by the join key. Statistics, indexes and memory influence the choice, visible in the execution plan.
38
What is the difference between FULL OUTER JOIN and UNION of LEFT and RIGHT joins?
A FULL OUTER JOIN returns matched rows plus unmatched rows from both sides in one operation. MySQL does not support it, so it is emulated with a LEFT JOIN UNION a RIGHT JOIN, or LEFT JOIN UNION ALL a RIGHT JOIN filtered to unmatched rows. Using plain UNION removes duplicates but can also remove genuinely repeated rows, so the UNION ALL pattern is safer.
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.
40
A long-running report transaction is causing table bloat and blocking. What do you do?
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.
Frequently asked questions
What is asked in SQL interviews for 10+ years of experience?
At ten years and above, roles include principal engineer, data architect, head of data engineering or DBA manager. Interviews focus on platform strategy, team building, vendor and cost decisions, and aligning data systems with business goals. Technical depth is assessed through architecture reviews and trade-off discussions rather than writing queries.
How many questions should I practise before a SQL interview?
Work through all 40 questions here, then the company-wise sets for the employers you are targeting. Quality matters more than count: be able to explain why each answer is right.
Which topics matter most for 10+ years of experience?
Focus first on Platform strategy, Team leadership, Cost and vendor decisions, Architecture reviews.