SQL / Query Optimization Interview Questions 2026

Design a database schema for an e-commerce platform that handles flash sales. During a flash sale, 10,000 users try to buy the last 100 units simultaneously. Your schema must prevent overselling. Show the schema and the critical query.

Explain the CAP theorem with a real database example for each combination. Which pair does a standard RDBMS choose?

Your PostgreSQL database has a table with 200 million records. A nightly batch job runs UPDATE on 5 million rows. The next morning, queries that were taking 50ms now take 8 seconds. What happened and how do you fix it?

Your PostgreSQL query plan shows 'Seq Scan' on a small 1000-row table despite having an index. Why might Postgres choose not to use the index?

You have a query that takes 45 seconds on a table with 50 million rows. EXPLAIN shows a full table scan. The WHERE clause filters on user_id and created_at. There's already an index on user_id. What do you do?

Write a SQL query to detect customers who haven't made a purchase in the last 30 days but were active in the 30 days before that (churned customers).

Explain database connection pool sizing. A service has 4 CPU cores and connects to PostgreSQL. What's the optimal pool size?

You have a users table with 100M rows. You need to add a full-text search on the bio column (free text). Show how to implement this in PostgreSQL.

Explain PostgreSQL's MVCC (Multi-Version Concurrency Control). How does it enable concurrent reads and writes without blocking?

Your database has a column with type VARCHAR(255) for email addresses. The column is indexed. Would changing it to VARCHAR(100) improve performance? What about TEXT?

Design a schema for a multi-level referral system where tracking chains of 10+ levels is required, and you need to query 'who referred this user?' and 'who did this user refer?' efficiently.

You run EXPLAIN on a query and see 'Using index condition' vs 'Using where'. What's the difference and which is better?

Explain the difference between OLTP and OLAP databases. Why can't you use the same database for both? What are the architectural alternatives?

Explain how database indexes can SLOW DOWN queries. When would you remove an index?

You're designing a database for an online exam system. 10,000 students take an exam simultaneously. Each student answers 50 questions. Design the schema and identify the hot spots.

Explain ACID properties with a real banking transaction example. Which property does READ COMMITTED isolation level sacrifice?

Your query has LEFT JOIN to a large table but most rows in the result have NULL for the joined table columns. EXPLAIN shows full scan of the joined table. How do you optimize?

You need to move 50 million rows from table A to table B (archive) and delete them from A. How do you do this without locking the production table?

A developer creates an index on every column 'to make queries faster.' Why is this wrong and what's the actual impact?

Explain window functions with an example. How is RANK() different from DENSE_RANK() and ROW_NUMBER()?

Your application runs this query every second: SELECT count(*) FROM orders WHERE status='processing'. The count is displayed on a dashboard. The orders table has 100M rows. How do you make this fast?

Explain the difference between UNION and UNION ALL. In what scenario does UNION ALL perform significantly better?

You have a slowly changing dimension in a data warehouse. Employee department changes over time and you need to query 'what department was this employee in on date X?' Design the table.

Write a query to find the second highest salary from an employees table. What if there are multiple employees with the highest salary?

A query that joins 4 tables and runs fine on a 1M-row test database takes 40 seconds in production on 500M rows. Walk through your approach to diagnosing and fixing it.

Write a query to find hosts whose average review rating dropped by more than 0.5 stars comparing their most recent 10 reviews to their prior 10 reviews.

Write a query to find, for each department, the employee with the second-highest salary, using window functions instead of a correlated subquery.

A data analyst complains that a report that runs daily takes 8 hours but used to take 30 minutes, starting 2 weeks ago. Nothing changed in the report query. What do you investigate?

Write a query to find customers who placed an order in each of the last 3 consecutive months, using an Orders table with customer_id and order_date.

Explain the difference between INNER JOIN, LEFT JOIN, and FULL OUTER JOIN with a simple example of Orders and Customers tables.

Write a query to find the top 3 skills most commonly shared between pairs of connected users, given a UserSkills table and a Connections table.

A SELECT query filtering on a non-indexed column takes 8 seconds on a 10 million row table. Would adding an index always help? What's the trade-off of adding too many indexes?

A junior developer accidentally ran DROP TABLE orders in production. The table has 50M rows and no recent backup. The last backup is from 3 days ago. What do you do?

Write a query to find customers who have placed orders in every month of the current year, given an Orders table with customer_id and order_date.

Write a query to find, for each city, the percentage of rides that were cancelled within 2 minutes of being booked, given a Rides table with city, booked_at, and cancelled_at.

What problem does database normalization (1NF, 2NF, 3NF) actually solve? Give an example of an update anomaly in an unnormalized table.

In PostgreSQL, what is the difference between SERIALIZABLE and READ COMMITTED isolation levels for a transaction that reads then updates an account balance?

How do you optimize a slow COUNT(*) query on a table with 100 million rows?

How would you find out why a specific SQL query is slow before making any changes to it?

Explain the difference between RANK(), DENSE_RANK(), and ROW_NUMBER() window functions.

What is the difference between a PostgreSQL VIEW and a MATERIALIZED VIEW? When would a materialized view actually hurt you?

What is the difference between WHERE and HAVING clauses? Can you use aggregate functions in WHERE?

Your OLTP database is being used for reporting queries that scan millions of rows, causing locks that block transactional queries. You cannot add more hardware immediately. What do you do today?

You notice a SQL query uses an index, but adding ORDER BY makes it 10x slower. Explain why and how to fix it.

Explain what happens step by step when you run: BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- crash here. And why this matters for your application design.

What is the difference between optimistic and pessimistic locking in databases?

A developer writes SELECT * FROM users WHERE email LIKE '%@gmail.com'. It's slow despite an index on email. Why? How would you make this fast while keeping the same search capability?

Explain what happens during a VACUUM operation in PostgreSQL. Why is it necessary?

You're asked to delete 10 million old log records from a 50M row table. A simple DELETE WHERE created_at < '2024-01-01' locks the table for hours. How do you do this safely in production?

What is a materialized view and when would you use one over a regular view?

Your database read replicas are always 30 seconds behind the primary. During this lag, users see stale data after updating their profile. How do you handle read-after-write consistency without routing all reads to primary?

Write a SQL query to detect duplicate rows in a table and delete all but one (keeping the one with the lowest ID).

You need to find duplicate records in a table of 10 million rows based on email, phone, and name (any two matching means duplicate). Write the SQL and explain the performance considerations.

Explain database partitioning vs sharding. When would you use each?

A stored procedure runs fine in isolation but causes deadlocks when called concurrently by 50 threads. The procedure does: UPDATE accounts, then UPDATE transactions. Another procedure does the same in reverse order. What's happening and how do you fix it?

What is a covering index? Show how adding one column to an index can change a query from a disk read to a memory read.

You have a MySQL query with GROUP BY that was fast on 1 million rows and is now slow on 50 million. EXPLAIN shows 'Using filesort' and 'Using temporary'. What does this mean and how do you fix it?

What are database transactions and ACID properties? Give a real example where violating each property causes data corruption.

Design a schema to support a multi-tenant SaaS application where each tenant has its own isolated data. Compare row-level isolation, schema-per-tenant, and database-per-tenant. When would you choose each?

Explain the difference between INNER JOIN, LEFT JOIN, and FULL OUTER JOIN. When does the choice affect the result count?

Your application has this query pattern: SELECT * FROM orders WHERE status='pending' running every 5 seconds. Table has 50M rows, 99.9% of orders are 'completed'. An index on status exists. EXPLAIN shows it's still doing a table scan. Why?

Write a SQL query to find the second highest salary from an Employees table without using LIMIT/TOP or subqueries.

A developer adds a NOT NULL constraint to a column in a production table with 100 million rows. The ALTER TABLE runs for 6 hours and locks the table. How should this have been done?

What is the N+1 query problem? How do you detect it in production without reading every query?

You run EXPLAIN ANALYZE on a query joining 4 tables. It shows 'Hash Join' with 'rows=1 actual rows=50000'. What does this tell you and what's your fix?

You have a table with 500 million rows. A query runs in 0.1s in development (10K rows) but 45 seconds in production. Same index exists on both. Why?