SQL Interview Questions for 3 Years Experience (2 to 4 Years)

“The root problem was that nothing in the table said one sku in one warehouse is one row, so a second run just inserted again. First I added a unique constraint on sku and warehouse_id, after cleaning out the duplicates. Then I changed the job to load the file into a staging table and upsert from it. In PostgreSQL that's INSERT ... ON CONFLICT DO UPDATE, MySQL has ON DUPLICATE KEY UPDATE, and SQL Server or Oracle use MERGE. Now a re-run just writes the same values again. One thing that caught me: if the file itself has the same key twice, PostgreSQL refuses to update one row twice in a single statement, so I de-duplicate the staging rows first.”

“I always write the SELECT first, with the exact WHERE clause the UPDATE will use, and I look at the count and a sample of rows. If the count surprises me, I stop there. Then I copy the rows I'm about to touch into a small backup table, so undoing it is one statement, not a restore. I run the UPDATE inside a transaction and check the reported row count matches the SELECT before I commit. For migrations, I run them on a copy with production-sized data, because something that takes a second on my laptop can lock a big table for minutes. And someone else reads the script before it runs; they've caught a missing condition for me more than once.”

“A single DELETE of forty million rows is one huge transaction. It holds locks the whole time, writes a mountain of log, can leave replicas far behind, and in some engines cancelling it halfway means a long rollback. So I wrote a small script that deletes ten thousand of the oldest rows per transaction, commits, sleeps briefly, and repeats until a batch deletes nothing. There was an index on created_at, so each batch found its rows quickly. I ran it in quiet hours and watched replica lag, and because every batch commits on its own, stopping it was harmless. PostgreSQL has no DELETE with LIMIT, so I pick ids in a subquery; in MySQL, DELETE ... LIMIT works directly. Afterwards the file didn't shrink; vacuum just made the space reusable for new rows.”

“I'd help, but not by typing an UPDATE from the chat message. First I'd check if the admin tool or an API can do it, because changing a plan in the app usually does more than flip one column: it might reset limits, write an audit record or clear a cache. A raw UPDATE skips all that and leaves the account in a strange state. If there really is no other way, I'd ask for a ticket so there's a record, run a SELECT to confirm I have the right customer and see the current values, change only that row by its id inside a transaction, check it affected one row, and have a teammate look at it. Then I'd note it, because three of these in a month means we need a button in the admin tool.”

“A composite index is sorted like a phone book: by the first column, then the second inside that. With created_at first, the database has to walk every entry in the date range and check status on each one, so if most rows in that month aren't pending, it reads a lot for nothing. When I flipped it to status then created_at, all the pending rows sit together and are already in date order, so the range becomes one short, tight scan, and it can even return them in date order without a sort. The rule I use now is equality columns first, then the range or sort column. I also checked the other queries on that table, because an index led by status won't help a query that only filters on created_at.”

“The column is text but the literal was a number, so MySQL converted each row's phone_number to a number before comparing. That has two effects. The index is sorted as text, so it can't be used for a numeric comparison, and the query scanned the whole table. And '05551234' becomes the number 5551234, so rows that weren't the same string matched. The fix was just quoting the value, so it's text against text, exact match and index used. In our case the value came from code that passed an integer, so the real fix was in the data layer. Other engines behave differently: PostgreSQL refuses to compare text with an integer at all, which is annoying but safer. I now check the same thing on join columns, where an id stored as text on one side and a number on the other causes the same scan.”

“We were on PostgreSQL, where text comparison is case-sensitive by default, so the unique constraint saw two different strings. In MySQL with its usual case-insensitive collation this wouldn't have happened, which is why it surprised a teammate. First I found and merged the existing duplicates, since a new unique index would fail on them. Then I added a unique index on LOWER(email) and changed the login lookup to compare LOWER(email) with the lowercased input. A plain WHERE LOWER(email) = ... can't use a normal index on email, but it can use an index built on exactly that expression. We also started lowercasing emails when they're saved, so the stored data is clean. Another option in PostgreSQL is the citext type, but the expression index needed no extension.”

“OFFSET doesn't jump anywhere. For page 2,000 the database finds a hundred thousand rows in order and discards them just to hand back fifty, so every page is slower than the last. I switched to keyset pagination: the API returns the created_at and id of the last row, and the next request asks for rows strictly before that pair, ordered the same way. With an index on created_at and id, each page is a short index read, however deep you go. The id matters because many orders share a timestamp, and without a unique tie-breaker you skip or repeat rows at page edges. The trade-off is you can't jump straight to page 400, but for an infinite scroll nobody does. It also stops rows shifting between pages when new orders arrive.”

“Every value goes in as a bound parameter. The SQL has placeholders, and the driver sends the values separately, so even if someone types a quote and a DROP TABLE into the search box, it's just text being compared. I never build the WHERE clause by joining strings, even on internal tools, because internal tools get exposed too. The catch is the sort column. You can't bind a column name, only a value, so on one list screen I mapped the sort options the frontend sends to a small fixed set of real column names, and anything else falls back to the default. The same goes for ASC or DESC. When I use an ORM, I watch for raw query helpers, because that's where string building sneaks back in.”

“That's the classic N+1. The ORM loaded the fifty orders in one query, then the template touched order.customer.name, and each touch fired its own lazy query. Each one was fast, but fifty round trips added up, and it got worse as the page size grew. I fixed it by telling the ORM to eager load the customer, which turned it into one join. Where a join would duplicate a lot of data, the other option is two queries: the orders, then all their customers in one IN list. I found it by turning on SQL logging in dev and counting. Afterwards I added a test that fails if that endpoint runs more than a couple of queries, because the same thing tends to come back when someone adds a new field to the page.”

“If users are failing, I roll back first and investigate after. Then I look at the database's own list of sessions: pg_stat_activity in PostgreSQL, SHOW PROCESSLIST in MySQL. I group them by application and state. If there's a pile of sessions sitting 'idle in transaction', the new code opened a transaction and never committed or closed, usually on an error path that skips the cleanup. Last time that's exactly what it was: an early return inside a try block. If they're all active and slow, it's a query problem instead. I also check the maths: pool size times the number of app instances has to stay under the server's connection limit, and that deploy had doubled the instances. I fixed the leak and set an idle-in-transaction timeout.”

“A deadlock is two transactions waiting on each other. Ours reserved stock line by line in the order the customer added items. So one order locked product 42 then wanted 17, while another locked 17 then wanted 42, and neither could move. The database spots the cycle, picks one as the victim and rolls it back with an error, which is what we saw in the logs. The fix was simple once I saw it: sort the order lines by product id before updating, so every transaction takes locks in the same order and a cycle can't form. I also moved a slow call to the pricing service out of the transaction so locks were held for less time, and added a small retry for the rare victim that still happens.”

“The code read like_count, added one in the app, and saved the result. Both requests read ten, both wrote eleven, and one like vanished. It's a lost update, and default isolation doesn't stop it because each statement on its own is fine. The simplest fix is to not read at all: UPDATE posts SET like_count = like_count + 1. The row lock makes the second update wait, and it then adds to the new value. Where the new value genuinely has to be worked out in the app, like editing a post, I use a version column: update only if the version is still the one I read, bump it, and if zero rows changed, someone got there first, so I reload and retry or tell the user.”

“I group by the day and put the condition inside the aggregate. COUNT star gives all orders placed that day. For shipped, SUM of CASE WHEN status is shipped THEN 1 ELSE 0 counts only those rows, and the same for cancelled. It's one scan of the table, and the columns always line up, which wasn't true of the old version of this report that ran three queries and merged them in code. PostgreSQL also has COUNT(*) FILTER (WHERE status = 'shipped'), which reads nicer, but CASE works everywhere. One thing I'd point out is what the numbers mean: shipped here is 'placed that day and shipped by now', not 'shipped that day'. That's a different query on shipped_at, and I've seen people mix the two up.”

“This is a gaps-and-islands problem. I first take distinct user and date, because someone who logs in three times a day would break the trick. Then I number each user's days in order with ROW_NUMBER and subtract that number from the date. For consecutive days both go up by one, so the result stays the same: the 3rd minus 1, the 4th minus 2 and the 5th minus 3 all give the 2nd. A gap makes the result jump. So that value labels each streak. I group by user and label to get each streak's length, then take the max per user. Date arithmetic differs by engine: in PostgreSQL date minus integer works, in SQL Server I'd use DATEADD with the negative row number.”

“created_at is a timestamp, so the end value '2026-03-31' is read as the very start of the 31st, midnight. BETWEEN includes that exact instant and nothing after it, so the whole last day disappeared. The fix I use everywhere now is a half-open range: created_at on or after the first of March, and strictly before the first of April. It covers every moment of the month, it doesn't care whether the column has milliseconds, and it still uses the index on created_at. The quick fix people reach for is ending at 23:59:59, but that misses anything in the final fraction of a second. Casting the column to a date also works, but it can stop the index being used on a big table.”

“We stored everything as timestamptz, which is right, but the report took the date of the raw value, so it grouped by the UTC day. An order at nine in the evening in New York is the next day in UTC, so it jumped a day. The fix was to convert to the customer's timezone first and then take the date. In PostgreSQL that's created_at AT TIME ZONE with the zone name. For the filter I kept the column bare and wrote the month's start and end in the customer's zone, so the index on created_at still applied. I used the named zone rather than a fixed offset like minus five, because offsets change with daylight saving, and on those days a fixed offset gets an hour wrong.”

“GROUP BY can only group rows that exist, so a day with no signups simply isn't in the result. You need a list of every day to start from. In PostgreSQL I generate it with generate_series. Other engines use a recursive CTE, and a lot of teams just keep a calendar table with one row per date, which is handy for other reports too. I count signups per day in one step, then LEFT JOIN those counts onto the full list of days, and COALESCE turns the missing counts into zero. I aggregate before the join on purpose: it keeps the date filter on the users table, where the index can use it, and the join stays small. The frontend team had been filling the gaps in code, and this let them delete that.”

“The unique constraint on email doesn't know about deleted_at, so the old soft-deleted row still blocks the new signup. In PostgreSQL I replaced it with a partial unique index on email where deleted_at is null, so only live accounts have to be unique and any number of deleted ones can share an email. SQL Server has the same idea as a filtered index. My first idea was a unique constraint on email plus deleted_at, but that's broken: live rows all have NULL there, and NULLs don't count as equal in a unique constraint, so it would let two live accounts share an email. MySQL has no partial indexes, so there you'd use a generated column or move deleted rows to an archive table. Soft deletes also mean every query needs the deleted_at filter, so we read through a view.”

“We stored incoming webhook bodies in a PostgreSQL jsonb column, because every sender had a different shape and we didn't want a migration each time one changed. Querying was fine: payload ->> 'event_type' pulls a field out as text, and you cast it when you need a number. We even added an expression index when the support screen started filtering on event type. But over time event_type was in every query, a few senders spelled it wrong and no constraint caught it, and we wanted to join it to a table of known event types. So I moved it to a real column, backfilled it from the JSON, and added a NOT NULL and an index. My rule now: JSON for data you store and show, real columns for data you search on.”

“At my last company I built appointment reminders. I added a reminders table with the appointment id, a send_at time, a status and a sent_at. The job picked up due rows every minute, so I indexed status and send_at in that order, and I put a unique constraint on appointment id plus reminder type, so a retry could never send the same reminder twice. That constraint saved us when the job crashed mid-run once. What I'd change: I stored send_at already converted to UTC but didn't keep the clinic's timezone on the reminder. When a clinic changed its timezone setting, we couldn't recompute pending reminders without joining back through three tables. Now I store the zone next to anything scheduled in local time.”

“I wrote a migration adding an orders.customer_id column with a foreign key to customers. It passed tests. My reviewer asked one question: what happens when we delete a customer? In PostgreSQL a foreign key doesn't create an index on the referencing column, so every customer delete would scan the whole orders table to check for children, and our joins from customers to orders would scan it too. MySQL's InnoDB adds that index for you, which is partly why I'd never noticed. I added the index in the same migration. The habit I kept is a checklist when I add a table or column: does every foreign key have an index, what does a delete do, and which query is this column for.”

“I said a day for backfilling a new country_code column from a free-text address field on about thirty million rows. I'd tested my UPDATE on staging, which had fifty thousand rows, and it was instant. In production it took four days. The update had to run in batches to avoid locking the table, a trigger on that table fired for every row, and about one row in twenty had addresses my parsing didn't handle, which I only found when the batches started failing. None of that was visible on a small, clean copy. Now, before I estimate, I run a SELECT that counts rows that don't match the pattern I expect, and I time one real batch on production-sized data and multiply, then add a buffer.”