SQL Fundamentals for Interviews

SQL is the most reliably tested backend skill. The questions are usually small business puzzles: top-N per group, running totals, finding duplicates, gaps, second-highest values, "customers who bought X but not Y". This chapter teaches the building blocks and the patterns behind those puzzles. Every query below has been run against a real database engine (SQLite, which supports window functions and common table expressions); small dialect differences from PostgreSQL, MySQL and SQL Server are noted where they matter.

1. The sample schema

Customers, orders and order lines for a small shop.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT, joined DATE);
CREATE TABLE orders    (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id),
                        placed_on DATE NOT NULL, status TEXT NOT NULL);
CREATE TABLE order_items (order_id INTEGER NOT NULL REFERENCES orders(id), sku TEXT NOT NULL,
                          qty INTEGER NOT NULL, unit_price REAL NOT NULL, PRIMARY KEY (order_id, sku));

INSERT INTO customers VALUES
 (1,'Asha','Pune','2024-01-10'),(2,'Ravi','Pune','2024-02-01'),(3,'Meera','Delhi','2024-02-15'),
 (4,'Zoya','Delhi','2024-03-01'),(5,'Kabir','Chennai','2024-03-20');
INSERT INTO orders VALUES
 (1,1,'2024-04-01','paid'),(2,1,'2024-04-05','paid'),(3,2,'2024-04-07','paid'),(4,3,'2024-04-09','cancelled'),
 (5,3,'2024-04-12','paid'),(6,4,'2024-04-15','paid'),(7,1,'2024-05-02','paid');
INSERT INTO order_items VALUES
 (1,'A',2,100),(1,'B',1,250),(2,'A',1,100),(3,'C',4,50),(4,'B',2,250),
 (5,'A',3,100),(5,'C',1,50),(6,'B',1,250),(7,'C',2,50);

2. The logical order of a query

You write SELECT first, but the database processes clauses in this order, which explains most "why can't I use this alias here?" errors:

1. FROM and JOIN (build the row set)
2. WHERE (filter rows)
3. GROUP BY (form groups)
4. HAVING (filter groups)
5. SELECT (compute columns, including aliases and window functions)
6. DISTINCT
7. ORDER BY
8. LIMIT / OFFSET

So WHERE cannot use an aggregate (use HAVING), and WHERE cannot reference a SELECT alias in most dialects.

3. Filtering, aggregation and grouping

-- revenue per customer for paid orders, highest first
SELECT c.name, SUM(oi.qty * oi.unit_price) AS revenue, COUNT(DISTINCT o.id) AS orders
FROM customers c
JOIN orders o       ON o.customer_id = c.id AND o.status = 'paid'
JOIN order_items oi ON oi.order_id = o.id
GROUP BY c.id, c.name
HAVING SUM(oi.qty * oi.unit_price) >= 150
ORDER BY revenue DESC;

assert q("""SELECT c.name, SUM(oi.qty*oi.unit_price) AS revenue, COUNT(DISTINCT o.id)
            FROM customers c JOIN orders o ON o.customer_id=c.id AND o.status='paid'
            JOIN order_items oi ON oi.order_id=o.id
            GROUP BY c.id, c.name HAVING SUM(oi.qty*oi.unit_price) >= 150 ORDER BY 2 DESC""") == \
    [("Asha", 650.0, 3), ("Meera", 350.0, 1), ("Zoya", 250.0, 1), ("Ravi", 200.0, 1)]

Notes:

• COUNT(*) counts rows; COUNT(col) counts non-null values; COUNT(DISTINCT col) counts unique non-null values.
• Aggregates ignore NULL (except COUNT(*)). AVG over a column with NULLs divides by the non-null count.
• Every selected column must be in the GROUP BY or inside an aggregate (standard SQL; some MySQL modes relax this, which hides bugs).

4. NULL and three-valued logic

NULL means unknown. Comparisons with NULL yield unknown, not true or false, and WHERE keeps only rows that are true.

assert q("SELECT NULL = NULL") == [(None,)]            # unknown, not true
assert q("SELECT NULL IS NULL") == [(1,)]
assert q("SELECT 1 WHERE NULL = NULL") == []           # unknown rows are filtered out
assert q("SELECT COALESCE(NULL, NULL, 'x')") == [("x",)]
q("CREATE TABLE t(v INTEGER)"); q("INSERT INTO t VALUES (1),(2),(NULL)")
assert q("SELECT COUNT(*), COUNT(v), AVG(v) FROM t") == [(3, 2, 1.5)]      # AVG divides by 2, not 3
assert q("SELECT COUNT(*) FROM t WHERE v <> 1") == [(1,)]                  # the NULL row is neither equal nor unequal to 1

The famous trap: x NOT IN (SELECT y ...) returns no rows if the subquery contains a NULL, because x <> NULL is unknown. Prefer NOT EXISTS.

q("CREATE TABLE a(x INTEGER)"); q("CREATE TABLE b(y INTEGER)")
q("INSERT INTO a VALUES (1),(2)"); q("INSERT INTO b VALUES (1),(NULL)")
assert q("SELECT x FROM a WHERE x NOT IN (SELECT y FROM b)") == []                       # surprising: empty
assert q("SELECT x FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.y = a.x)") == [(2,)]  # the intended answer

5. Joins

-- customers with NO paid orders: LEFT JOIN, then keep the non-matches
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'
WHERE o.id IS NULL;

assert q("""SELECT c.name FROM customers c LEFT JOIN orders o
            ON o.customer_id=c.id AND o.status='paid' WHERE o.id IS NULL""") == [("Kabir",)]

Where you put a condition matters in outer joins. A condition on the right table in the ON clause keeps unmatched left rows; the same condition in WHERE removes them (turning the left join into an inner join).

in_on = q("SELECT COUNT(*) FROM customers c LEFT JOIN orders o ON o.customer_id=c.id AND o.status='paid'")
in_where = q("SELECT COUNT(*) FROM customers c LEFT JOIN orders o ON o.customer_id=c.id WHERE o.status='paid'")
assert in_on == [(7,)] and in_where == [(6,)]          # Kabir (no orders) disappears in the second form

Join fan-out: joining one-to-many tables multiplies rows, so a SUM over the "one" side double counts. Aggregate in a subquery first, or sum on the "many" side.

6. Subqueries, CTEs and set operations

A CTE (WITH ... AS) names an intermediate result and makes queries readable. A correlated subquery references the outer row and runs conceptually once per row.

-- customers whose total spend is above the average customer spend
WITH spend AS (
  SELECT o.customer_id, SUM(oi.qty * oi.unit_price) AS total
  FROM orders o JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status = 'paid'
  GROUP BY o.customer_id
)
SELECT c.name, s.total
FROM spend s JOIN customers c ON c.id = s.customer_id
WHERE s.total > (SELECT AVG(total) FROM spend)
ORDER BY s.total DESC;

assert q("""WITH spend AS (SELECT o.customer_id, SUM(oi.qty*oi.unit_price) total FROM orders o
              JOIN order_items oi ON oi.order_id=o.id WHERE o.status='paid' GROUP BY o.customer_id)
            SELECT c.name, s.total FROM spend s JOIN customers c ON c.id=s.customer_id
            WHERE s.total > (SELECT AVG(total) FROM spend) ORDER BY s.total DESC""") == [("Asha", 650.0)]

Set operations: UNION (distinct), UNION ALL (keeps duplicates, faster), INTERSECT, EXCEPT (MINUS in Oracle). Column counts and types must align.

7. Window functions

A window function computes over a set of rows related to the current row without collapsing them (unlike GROUP BY). Syntax: function() OVER (PARTITION BY ... ORDER BY ... frame).

Top-N per group (the most common interview pattern)

-- the most expensive paid order per customer
WITH order_totals AS (
  SELECT o.id AS order_id, o.customer_id, SUM(oi.qty * oi.unit_price) AS total
  FROM orders o JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status = 'paid'
  GROUP BY o.id, o.customer_id
), ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC, order_id) AS rn
  FROM order_totals
)
SELECT customer_id, order_id, total FROM ranked WHERE rn = 1 ORDER BY customer_id;

assert q("""WITH ot AS (SELECT o.id order_id, o.customer_id, SUM(oi.qty*oi.unit_price) total FROM orders o
              JOIN order_items oi ON oi.order_id=o.id WHERE o.status='paid' GROUP BY o.id, o.customer_id),
            r AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC, order_id) rn FROM ot)
            SELECT customer_id, order_id, total FROM r WHERE rn=1 ORDER BY customer_id""") == \
    [(1, 1, 450.0), (2, 3, 200.0), (3, 5, 350.0), (4, 6, 250.0)]

Use ROW_NUMBER for exactly one row per group, RANK or DENSE_RANK when ties should all be included.

q("CREATE TABLE scores(name TEXT, pts INTEGER)")
q("INSERT INTO scores VALUES ('a',90),('b',90),('c',80),('d',70)")
rows = q("SELECT name, ROW_NUMBER() OVER (ORDER BY pts DESC, name), RANK() OVER (ORDER BY pts DESC), DENSE_RANK() OVER (ORDER BY pts DESC) FROM scores ORDER BY 2")
assert rows == [("a", 1, 1, 1), ("b", 2, 1, 1), ("c", 3, 3, 2), ("d", 4, 4, 3)]

Running totals and change from the previous row

SELECT placed_on,
       COUNT(*) AS orders_that_day,
       SUM(COUNT(*)) OVER (ORDER BY placed_on) AS running_orders,
       LAG(COUNT(*)) OVER (ORDER BY placed_on) AS previous_day_orders
FROM orders
GROUP BY placed_on
ORDER BY placed_on;

rows = q("""SELECT placed_on, COUNT(*), SUM(COUNT(*)) OVER (ORDER BY placed_on), LAG(COUNT(*)) OVER (ORDER BY placed_on)
            FROM orders GROUP BY placed_on ORDER BY placed_on""")
assert rows[0] == ("2024-04-01", 1, 1, None) and rows[-1] == ("2024-05-02", 1, 7, 1)

8. Classic puzzles

Second highest salary (without a window function)

q("CREATE TABLE emp(id INTEGER, dept TEXT, salary INTEGER)")
q("INSERT INTO emp VALUES (1,'eng',100),(2,'eng',200),(3,'eng',200),(4,'ops',90),(5,'ops',80)")
second = q("SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp)")
assert second == [(100,)]                                   # duplicates of the top do not count as second
nth = q("SELECT DISTINCT salary FROM emp ORDER BY salary DESC LIMIT 1 OFFSET 1")
assert nth == [(100,)]                                      # OFFSET n-1 for the nth highest distinct value

Duplicates

q("CREATE TABLE users(id INTEGER, email TEXT)")
q("INSERT INTO users VALUES (1,'a@x'),(2,'b@x'),(3,'a@x'),(4,'a@x')")
assert q("SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1") == [("a@x", 3)]
# delete duplicates, keeping the lowest id
q("DELETE FROM users WHERE id NOT IN (SELECT MIN(id) FROM users GROUP BY email)")
assert q("SELECT id FROM users ORDER BY id") == [(1,), (2,)]

Employees earning more than their department average

rows = q("""SELECT id FROM emp e WHERE salary > (SELECT AVG(salary) FROM emp WHERE dept = e.dept) ORDER BY id""")
assert rows == [(2,), (3,), (4,)]                         # 200, 200 in eng (average 166.7) and 90 in ops (average 85)

Consecutive days or gaps (islands)

For consecutive-run problems, subtract a row number from the date: rows in one consecutive run share the same difference.

q("CREATE TABLE logins(day INTEGER)")
q("INSERT INTO logins VALUES (1),(2),(3),(7),(8),(10)")
runs = q("""SELECT MIN(day), MAX(day), COUNT(*) FROM
              (SELECT day, day - ROW_NUMBER() OVER (ORDER BY day) AS grp FROM logins)
            GROUP BY grp ORDER BY 1""")
assert runs == [(1, 3, 3), (7, 8, 2), (10, 10, 1)]

Customers who bought A but not B

q("CREATE TABLE purchases(cust INTEGER, sku TEXT)")
q("INSERT INTO purchases VALUES (1,'A'),(1,'B'),(2,'A'),(3,'B')")
only_a = q("""SELECT DISTINCT cust FROM purchases p WHERE sku='A'
              AND NOT EXISTS (SELECT 1 FROM purchases WHERE cust=p.cust AND sku='B')""")
assert only_a == [(2,)]
both = q("SELECT cust FROM purchases WHERE sku IN ('A','B') GROUP BY cust HAVING COUNT(DISTINCT sku) = 2")
assert both == [(1,)]

9. DML and DDL essentials

• INSERT ... SELECT, multi-row inserts, upsert (INSERT ... ON CONFLICT DO UPDATE in PostgreSQL and SQLite, ON DUPLICATE KEY UPDATE in MySQL).
• UPDATE ... FROM/join forms differ by dialect; always include a WHERE and check the row count.
• DELETE versus TRUNCATE (TRUNCATE is faster, not row-by-row logged in some engines, and may not be transactional everywhere).
• Constraints keep data correct: PRIMARY KEY, UNIQUE, NOT NULL, CHECK, FOREIGN KEY. Prefer constraints in the database over relying on application code alone.

q("CREATE TABLE inv(sku TEXT PRIMARY KEY, qty INTEGER NOT NULL CHECK (qty >= 0))")
q("INSERT INTO inv VALUES ('A', 5)")
q("INSERT INTO inv VALUES ('A', 3) ON CONFLICT(sku) DO UPDATE SET qty = qty + excluded.qty")     # upsert
assert q("SELECT qty FROM inv WHERE sku='A'") == [(8,)]
try:
    q("UPDATE inv SET qty = qty - 10 WHERE sku='A'")
    raise AssertionError("the CHECK constraint should have rejected this")
except Exception as e:
    assert "CHECK" in str(e)                              # the database refuses to go negative

10. Writing safe queries

• Parameterise. Never build SQL by concatenating user input (SQL injection). Use placeholders and let the driver bind values.
• Select only needed columns; avoid SELECT * in application code.
• Always bound result sets with LIMIT or pagination.
• Check the plan (EXPLAIN) for anything that runs on big tables (next chapter).

evil = "x' OR '1'='1"
unsafe = db.execute("SELECT COUNT(*) FROM customers WHERE name = '" + evil + "'").fetchone()[0]
safe = db.execute("SELECT COUNT(*) FROM customers WHERE name = ?", (evil,)).fetchone()[0]
assert unsafe == 5                    # the injected condition matched every row
assert safe == 0                      # bound as data, it matches nothing

11. Common mistakes

• NOT IN with a nullable subquery.
• Putting right-table filters in WHERE of a left join.
• Fan-out double counting after joining one-to-many tables.
• COUNT(*) versus COUNT(col) confusion, and averages over NULLs.
• Using RANK when exactly one row per group is needed.
• UNION when UNION ALL is correct and cheaper.
• Integer division and implicit type casts.
• Missing tie-breakers in ORDER BY (non-deterministic pagination and ROW_NUMBER).
• String-built SQL.

12. Practice questions

1. Find customers with no orders in the last 90 days.
2. Return the top three products by revenue within each category.
3. Compute a 7-day moving average of daily sales.
4. Find the nth highest salary, handling ties.
5. List users who logged in on three or more consecutive days.
6. Explain the difference between WHERE and HAVING, and between RANK and DENSE_RANK.
7. Why can x NOT IN (subquery) return nothing, and what is the fix?
8. Delete duplicate rows keeping the earliest.