40 questions with answers for candidates with 7 years of experience — 20 medium, 20 hard.
What interviewers expect: 7 years experience
Seven-year candidates are evaluated for lead or architect roles. Discussions cover database technology choices, high availability, disaster recovery, security and compliance, cost management on cloud warehouses, and data governance. You should present examples of large migrations or redesigns you led and their measurable impact.
This set is weighted towards Indexes and Performance (10), Database Design (6), SQL Basics (5), Transactions and Advanced SQL (5).
Data architecture High availability Security and compliance Migration leadership
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
Compare this month's and last month's sales per product using a full outer join.
SELECT COALESCE(c.product_id, p.product_id) AS product_id,
COALESCE(c.sales, 0) AS this_month,
COALESCE(p.sales, 0) AS last_month
FROM cur_month c
FULL OUTER JOIN prev_month p
ON c.product_id = p.product_id;
Products sold in only one of the months still appear, with zero for the other month.
02
What is the difference between an index scan and an index seek?
An index seek navigates the B-tree directly to the matching keys, reading only the needed rows, which is efficient for selective filters. An index scan reads the whole index or a large range of it. A table scan reads every row. Scans are not always bad; for queries returning a large share of rows, a scan can be cheaper than many seeks.
03
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.
04
A search on name LIKE '%sharma%' is very slow on a 20 million row table. What can you do?
A leading wildcard cannot use a normal B-tree index, so the database scans the table. Options are full-text indexing, trigram indexes in PostgreSQL, a reversed or computed column if the pattern is a suffix, or a search engine for heavy text search. Also limit the result, filter on other indexed columns first, and check whether users really need contains search.
05
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.
06
Tell me about a time you used advanced SQL to solve a business problem.
Situation: the product team wanted to know where users dropped off in the onboarding flow. Task: build a funnel from raw event logs. Action: I used window functions to order events per user, LEAD to find the next step and time taken, and grouped the results into a funnel with conversion rates. Result: we found a 40 percent drop at KYC upload, and the redesign improved completion by 15 percent.
07
How would you design data archiving for a table growing by 10 million rows a month?
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.
08
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.
09
Tell me about a time you significantly improved query performance.
Situation: a daily settlement report took 45 minutes and often missed its deadline. Task: bring it under ten minutes. Action: I studied the execution plan, replaced a non-SARGable date filter, added a composite covering index, pre-aggregated a large join in a temp table and updated statistics. Result: runtime dropped to six minutes, and the finance team received reports on time every day.
10
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.
11
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.
12
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.
13
How do you handle disagreement with developers about schema changes?
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.
14
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.
15
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.
16
A query with many OR conditions across different columns is slow. How can you optimise it?
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.
17
What is the difference between OLTP and OLAP databases?
OLTP systems handle many short transactions such as orders and payments, with normalised schemas, row-based storage and focus on fast inserts and updates. OLAP systems support analysis over large history, using denormalised star or snowflake schemas, columnar storage and heavy aggregations. A bank's core system is OLTP, while its data warehouse used for monthly reports is OLAP.
18
A business user needs a dashboard on a complex 6-table join that runs slowly. Would you use a view or a materialised view?
A normal view only simplifies the query; it still runs the full join every time, so performance does not improve. If the data can be a few minutes or hours old, a materialised view or summary table refreshed on schedule gives fast reads. If real-time data is required, tune the underlying query with indexes or build an incrementally maintained table.
19
A query that ran in 1 second now takes 40 seconds after a data load. What do you check?
Compare the current execution plan with the old one. A big data load often makes statistics stale, causing poor row estimates and a different join or scan choice, so update statistics first. Check for index fragmentation, missing indexes after the load, blocking from other sessions, and whether the data volume crossed a threshold. Test again after each change.
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.
21
Find employees who earn more than their department average.
SELECT e.emp_id, e.name, e.department_id, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department_id = e.department_id
);
This is a correlated subquery. A window version uses AVG(salary) OVER (PARTITION BY department_id) in a CTE and filters in the outer query.
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.
23
What is parameter sniffing and how do you handle it?
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.
24
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.
25
Your running total jumps by large amounts on some dates instead of row by row. Why?
With ORDER BY and no explicit frame, the default frame is RANGE UNBOUNDED PRECEDING, which treats all rows with the same order value as peers and includes them together. When several rows share the same date, the running total adds them all at once. Specify ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, or add a unique tiebreaker to ORDER BY.
A star schema has a central fact table with measures such as sales amount and foreign keys to denormalised dimension tables like date, product, store and customer. A snowflake schema normalises dimensions further, such as product to category to department. Star schemas are simpler and faster for BI queries, while snowflake schemas save space and reduce redundancy.
27
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.
28
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.
LIKE matches string patterns. The percent sign matches any sequence of characters and the underscore matches exactly one character. name LIKE 'Ra%' finds names starting with Ra, and code LIKE 'A_1' matches A01 or AB1. Leading wildcards such as LIKE '%kumar' prevent index use and are slow on large tables. Case sensitivity depends on the database collation.
30
Two systems store customer IDs differently, one as INT and one as VARCHAR with leading zeros. How do you join them?
Convert consistently, for example CAST(b.cust_code AS INT) = a.customer_id, but understand this prevents index use on b.cust_code and fails on non-numeric values. Better options are a computed or persisted column with the converted value and an index, or a mapping table built during ETL. Also validate unmatched rows to catch formatting issues such as spaces.
31
A recursive CTE on a category tree runs forever. What is the likely cause and fix?
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.
32
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.
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.
34
What is table partitioning and how does it help performance?
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.
35
Your application logs frequent deadlock errors at peak hours. How do you investigate and reduce them?
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.
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 materialised view stores the result of a query physically, unlike a normal view. Reads are fast because data is precomputed, which suits heavy aggregations for dashboards. The trade-off is staleness, so it must be refreshed on schedule, on commit or incrementally depending on the database. PostgreSQL uses REFRESH MATERIALIZED VIEW, and SQL Server offers indexed views with similar benefits.
38
How do you get the top 3 rows in different databases?
MySQL and PostgreSQL:
SELECT * FROM products ORDER BY price DESC LIMIT 3;
SQL Server:
SELECT TOP 3 * FROM products ORDER BY price DESC;
Standard SQL:
SELECT * FROM products ORDER BY price DESC
FETCH FIRST 3 ROWS ONLY;
Always use ORDER BY, otherwise the top rows are not guaranteed.
Denormalisation intentionally adds redundancy, such as storing customer name in an orders reporting table, to reduce joins and speed up reads. It is common in data warehouses, reporting tables and high-read applications. The cost is extra storage and the need to keep copies consistent through ETL, triggers or application logic. Use it when read performance matters more than write simplicity.
A SARGable (Search ARGument able) predicate lets the optimiser use an index seek. Wrapping an indexed column in a function, such as WHERE YEAR(order_date) = 2025 or WHERE LOWER(email) = 'x', usually prevents index use. Rewrite as WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01'. Leading wildcards and implicit type conversions also make predicates non-SARGable.
Frequently asked questions
What is asked in SQL interviews for 7 years of experience?
Seven-year candidates are evaluated for lead or architect roles. Discussions cover database technology choices, high availability, disaster recovery, security and compliance, cost management on cloud warehouses, and data governance. You should present examples of large migrations or redesigns you led and their measurable impact.
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 7 years of experience?
Focus first on Data architecture, High availability, Security and compliance, Migration leadership.