Top 3 Sql queries Interview Questions
Browse 3 real-world technical and behavioral interview questions about Sql queries. Review scenarios, edge cases, and architectural best practices.
You need a work queue with priorities and you are told to build it in Postgres, not Kafka. What does the dequeue statement have to do, and what happens when ten workers run it at once?
The dequeue has to pick the highest-priority ready row, lock it, and mark it claimed in one statement, using FOR UPDATE SKIP LOCKED so ten workers each take a different row instead of queueing behind the same one. The lock only lasts as long as the transaction, so the claim must be written or the transaction held.
Adding a second LEFT JOIN doubled the revenue figure on a report. Explain what happened and write the correct query.
SQL join duplicates cause double counting when two one-to-many joins multiply rows before aggregation. Aggregate each child table to one row per parent first, then join those results back to the parent. Use this sql queries answer to show the decision, trade-off, and evidence rather than a memorised definition.
Write a query returning the second-highest salary in each department, then tell me what it does when two people tie for the top.
To get the second highest salary per department in SQL, rank employees inside each department with DENSE_RANK and filter rank two. DENSE_RANK handles ties where ROW_NUMBER or a global MAX gives the wrong result. Use this window functions answer to show the decision, trade-off, and evidence rather than a memorised definition. It also connects sql queries to the point an interviewer is testing.