SQL Query Writing interview questions

Joins, window functions, aggregation, and the analytical queries asked in a shared editor.

4 questions in Databases & SQL.

Databases & SQLA 22-minute read on how the topic fits together, before the questions.Database fundamentals58 short answers on one page, for revising rather than studying.

medium4

mediumCodingDesign

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.

6 minmid, senior

mediumCodingConcept

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.

4 minentry, mid, senior

mediumCodingConcept

Find me every customer with no order in the last thirty days, then tell me which ways of writing that get NULLs wrong.

Use NOT EXISTS for customers with no recent order because it expresses an anti-join and avoids NULL traps. NOT IN can return no rows if the subquery contains NULL, and LEFT JOIN becomes wrong if filters are placed in WHERE. Use this SQL answer to show the decision, trade-off, and evidence rather than a memorised definition. It also connects null semantics to the point an interviewer is testing.

4 minentry, mid, senior

mediumCodingConcept

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.

3 minentry, mid, senior