SQL Interview Questions for Experienced Candidates (5 Years)

“The ROW_NUMBER version was correct, but it numbered all fifty million events to keep one per order, when the screen only showed a page of fifty orders. I added an index on order_id and created_at, then rewrote the query to start from the orders on the page and fetch the newest event for each with a LATERAL join and LIMIT 1. Each lookup became a short index read. SQL Server does the same with CROSS APPLY and TOP 1. For the busiest screen we went further and stored current_status on the orders table, updated in the same transaction that writes the event, so the list needs no events lookup at all. That added a write and a rule to maintain, but reads became trivial. I kept the ROW_NUMBER query for the nightly export, which reads everything anyway.”

“Inserts on our payments table had slowed, and it had fifteen indexes, several added in a hurry during old incidents. I pulled the index usage statistics, making sure they covered more than a month so month-end reports showed up. Four had never been used, and two were exact prefixes of wider indexes that could serve the same queries. I left unique indexes alone, because they enforce a rule even if no query reads them. Then I checked the replicas separately, since usage stats are kept per server, and one index that looked dead on the primary was busy for reports on a replica. For the rest, on MySQL I made them invisible first, so the optimizer ignored them but I could switch them back in seconds, and watched slow query logs for two weeks. Then I dropped them, and insert time came down noticeably.”

“I'd say let's spend twenty minutes finding out which queries are slow first, because indexing everything feels safe but usually isn't. I'd pull the slowest statements from the query log, run their plans and see what they really do. Often it's one or two queries, and a single composite index built for their filter and sort fixes both, where five single-column indexes might not help at all. I'd also point out the cost: that table is written to constantly, and every extra index makes each insert slower and uses disk, and later nobody dares drop them. If we're short on time, I'd add the one index the plan clearly asks for, check the demo query, and list the rest to review afterwards. That keeps the demo safe without leaving a mess behind.”

“Product search used LIKE with a wildcard on both sides, so the normal index on name was useless and every search scanned the table. First I checked what users actually typed: mostly whole words, sometimes part of a model number. I tried full-text search, which matches words and handles plurals, and it was fast, but it stopped matching fragments from the middle of a model number, which the support team relied on. On PostgreSQL I ended up with a trigram index on name and model number, which keeps wildcard LIKE working and makes it fast for terms of three or more characters. The cost was a large index and slower writes on that table, which were rare anyway. If we'd needed ranking, typo tolerance or filters by facet, I'd have argued for a proper search engine instead.”

“We moved seat booking to serializable because two people could otherwise both pass our checks and book the last seat. Right after, we saw a few serialization failures at busy times. That's the database doing its job: it aborts one transaction instead of letting an unsafe interleaving through. So I wrapped the booking in a retry helper that catches only the serialization failure error code and reruns the whole transaction from the start, including the reads, because the old reads are exactly what went stale. It tries a few times with a short random backoff, then shows the user a clear message. I also moved the confirmation email outside the transaction, so a retry could never send two. The cost is some wasted work at peak, and when we measured it, it was small.”

“Lock wait data showed nearly all the waiting was on one row: today's total, which every checkout updated, and each checkout held that lock until its whole transaction committed. Every update was correct; they just had to queue. I looked at two fixes. One was splitting the total into, say, sixteen slot rows, with each checkout updating a random slot and the report summing them. The other was removing the update from checkout entirely and computing the total from the orders table, with a summary refreshed every minute. We chose the second, because nobody needed the total to the second, and it took a write out of the most important transaction we had. Checkout at peak got smoother straight away. The trade-off was a total up to a minute old, which the finance team agreed to.”

“Our customer dashboard showed lifetime order count and total spend, computed from the orders table on every page load. Even with good indexes it was too slow for our biggest customers, who had hundreds of thousands of orders. I added a customer_totals table updated in the same transaction as each order and refund, so a crash halfway can't leave them out of step. The trade-off was extra write work and more code paths to remember, and an old import script that inserted orders directly did miss it at first. That's why I also added a nightly job that recomputes totals for a sample of customers and alerts on any mismatch. It caught that script within a day. The dashboard went from several seconds to instant, and I documented the table as derived so nobody treats it as the source.”

“We used random UUIDs on an events table because several services created rows, and we didn't want ids that showed how many customers we had. For a year it was fine. As the table grew into hundreds of millions of rows, inserts slowed down. Each new random key lands somewhere in the middle of the index, so the database keeps touching pages all over it, and once the index no longer fits in memory, that means far more disk reads. On MySQL it hurt more, because the primary key decides the table's physical order and every secondary index stores a copy of it. For new tables we moved to time-ordered UUIDs, which begin with a timestamp, so new keys land near the end like a sequence would. I'd still use UUIDs where ids are created outside the database, just not random ones on a high-insert table.”

“Finance wanted a full history of changes to our invoices table. I compared three options. History written by the application knows the logged-in user, but it misses anything done outside the app, like a manual fix in a console. A trigger catches every change, which was the point, but it only sees the database user, and that was one shared service account. So I used a trigger that copies the old row, the new row and the time into a history table, and the app sets the real user's id as a setting at the start of each transaction, which the trigger reads. The costs were slightly slower writes and a history table that grows forever, so we agreed a retention period and partitioned the history by month. A change stream was more powerful, but it meant new infrastructure we didn't need yet.”

“A new report query forgot the tenant_id filter, and for about an hour one customer could see another's order list. After fixing the query and working out with the team who had to be told, I wanted a structural fix, because remembering the WHERE clause had already failed once. We were on PostgreSQL, so I turned on row-level security for the tenant tables. The app sets the current tenant at the start of each transaction, and a policy only lets through rows with that tenant_id, even when a query forgets. Our app's user owned the tables, and owners skip policies by default, so I forced the policy onto the owner too. I also put tenant_id into the foreign keys between tenant tables, so a row can't point at another tenant's parent. Then we added a test that logs in as one tenant and tries to read another's data through every endpoint. The cost was some care with connection pooling and a little planning overhead.”

“I'd ask to see the numbers first, because the slowness blamed on foreign keys is often a missing index on the child column, on engines that don't create one for you. Without it, deleting a parent row or changing its key has to scan the child table to check for references. The app checks it anyway sounds reassuring, but the app isn't the only writer. There are backfills, manual fixes, a second service and plain bugs, and any of them can leave orphan rows that quietly break reports months later. So my position is to keep foreign keys on the core tables, add the missing indexes and measure again. If one high-volume table, like a raw event log, still can't afford them after that, I'd accept dropping them there, with a nightly orphan check. That trade-off I can defend. Dropping all of them, I can't.”

“We started with a PostgreSQL enum type for order status because it was compact and self-documenting, and adding a value was easy with ALTER TYPE. The trouble came when we wanted to retire a status. You can't simply drop a value from an enum, so it meant creating a new type and converting the column, which on a big table is a real migration. Some reporting tools also struggled with the custom type. For the next service I used a small lookup table with a foreign key instead. A new status is just an insert, retiring one is a flag on the row, and the table can carry extra details, like a display label or whether the status is final. The cost is a join when you want the label, which is trivial. A CHECK constraint is fine for lists that truly never change.”

“On one project the product page took two seconds, and the first proposal was to cache it. I read the plan first: it was scanning a reviews table because an index was missing. Adding the index brought it down to a few milliseconds, and no cache was needed. On another screen, a category summary, the query was already well indexed but did real aggregation work, and thousands of users read the same answer while the data changed a few times an hour. There I agreed to a cache, with a short expiry and an explicit clear whenever a product in that category changed. We also handled the moment the cache is empty after a deploy, when every request would hit the database at once, so only one request rebuilds the entry while the others wait briefly. My rule: cache what's expensive and shared, never what's just broken.”

“A teammate wanted our discount rules moved into stored procedures, so the web app and the mobile backend would share one copy. I understood the goal, but I pushed to keep those rules in the application. That's where we have unit tests, code review, feature flags and a quick rollback, while a procedure change is a database deploy that's harder to test, version and debug. We solved the sharing problem with one pricing module that both apps call. Where I did choose a procedure was our nightly archive job, which moved closed invoices into an archive table and deleted them in batches. Running it next to the data saved thousands of round trips, and it's plain data housekeeping with no business decisions in it. So the rule we agreed was simple: heavy set-based data work can live in the database, in version-controlled migrations with tests, and business decisions stay in the app. The cost is two places to look, which that rule keeps small.”

“On PostgreSQL I built a materialized view for a sales dashboard that joined orders, items and products. The team agreed ten-minute-old numbers were fine, so a scheduled job refreshed it every ten minutes. The surprise came a few weeks later, when users sometimes saw the dashboard hang. A plain REFRESH MATERIALIZED VIEW locks the view against reads until it finishes, and the refresh had grown from seconds to over a minute as the data grew. I switched to REFRESH MATERIALIZED VIEW CONCURRENTLY, which lets reads carry on, but it needs a unique index on the view and does more work per refresh, so I added the index and checked the timing. I also added an alert for any refresh that runs longer than its interval, so refreshes can never pile up on each other.”

“I led moving our billing module out of a shared database into its own PostgreSQL database with a cleaned-up schema. We did a bulk copy, then kept the new side in step with a change stream while both ran. A successful copy job proves nothing, so I built checks. For each table we compared row counts, then checksums over chunks of primary key ranges, so a mismatch pointed to a few thousand rows rather than the whole table. Then we compared what the business cares about: invoice totals per month and per currency on both sides, and the output of our ten main billing reports, diffed line by line. That found a real bug, where timestamps without a time zone were shifted by a server's zone setting on the way in. Once that was fixed and the differences stayed at zero, we switched reads, then writes, and kept the old database updated for a while as our way back.”

“The procedure opened a cursor over the day's orders, and for each one looked up the customer's tier, worked out loyalty points and updated the order. A few hundred thousand rows, each with its own lookup and update, took about an hour. I wrote down what the loop really did, then replaced it with one UPDATE joined to the customers table, with a CASE for the tier rules. Before switching, I restored the same snapshot twice, ran the old procedure on one copy and the new statement on the other, and compared the results with EXCEPT in both directions. That found one difference: the loop skipped customers with no tier, and my version gave them the default. I matched the old behaviour and confirmed it with the business owner. The new version runs in seconds.”

“I approved a migration that added an index to our orders table on PostgreSQL. It looked harmless, but it used a plain CREATE INDEX, which blocks writes to the table until the build finishes, and on that table the build took several minutes. Checkouts started timing out almost at once. I cancelled the migration, which released the lock, and checkouts recovered within a minute. Then we built the index again with CREATE INDEX CONCURRENTLY, which is slower but doesn't block writes. In the review afterwards, I owned that I'd approved it without asking how it behaved on our biggest table. We changed two things: a check in CI that flags locking statements like a plain CREATE INDEX on large tables, and a lock timeout on every migration, so one stuck waiting for a lock fails fast instead of queueing traffic behind it.”

“After an incident where one bad search query ran for minutes and tied up the connection pool, I set timeouts in layers. Web requests got a statement timeout of a few seconds, set on that service's database user, well above our slowest normal query. Batch jobs got their own user with a much longer limit. The first time it fired for real, a customer with a huge account opened their history page and the query was cancelled at the limit. The good news was the rest of the site stayed healthy. The bad news was that our code retried it three times, tripling the load. I removed retries for timeouts, returned a friendly message, and logged the query and parameters. That log pointed to a missing index for large accounts, which we fixed. A timeout is a safety net, and every one that fires is a bug report.”

“I read it in three passes. First, is it right: I look for joins that can multiply rows, NOT IN against a column that might hold NULL, and date filters that are off by one at the boundary. Second, will it hold up on real data. I ask for the query plan against a copy with production-sized data, not a laptop with fifty rows, and I look for queries with no limit on tables that keep growing. Third, for migrations, I ask what locks it takes, how long it runs on the largest table, and whether the old version of the app still works while the deploy is half done. I write comments as questions with a reason, like this will scan orders, can we filter on the indexed column, so the author learns the why and not just the fix.”

“One developer I mentored kept shipping queries that were fine on the test database and slow in production. The pattern was clear: our test data had a few hundred rows, and they'd never read a query plan. Rather than fix their next pull request myself, I booked an hour, and we took one of their slow queries, ran it on a masked copy of production data and read the plan together. They spotted the full scan themselves and wrote the index. After that, I asked them to paste a plan into any pull request that touched a big table. I also pushed for a refreshed, masked dataset for the whole team, because the real problem wasn't only them. Within a couple of months, their pull requests needed far fewer comments, and they started pointing the same things out to others.”

“I'd start by asking what decision the report drives, because every few minutes is often a guess. Then I'd run the query on a copy and show the cost in plain terms: it scans the orders table, takes several seconds, and at peak it would compete with checkout for the same disk and memory. I wouldn't just say no. I'd offer options with their trade-offs: run it on a read replica, which is slightly behind but safe, or keep a summary table refreshed every fifteen minutes, which is fast to read, or send it to the analytics store if there is one. When this came up before, the manager picked the summary table, because fifteen minutes was plenty once we talked it through. I'd write the decision down so nobody points a new dashboard at the primary later.”