SQL interview questions: spot the bug

Question 1

This should total all orders placed in January. Finance says the number is too low. Why? (created_at is a TIMESTAMP.)

SELECT SUM(total_cents) AS january_revenue
FROM orders
WHERE created_at BETWEEN '2025-01-01' AND '2025-01-31';

Accepted Answer

Line 3 (Off-by-one): '2025-01-31' means midnight at the start of Jan 31, and BETWEEN is inclusive of exactly that instant. Every order placed later on Jan 31 is excluded.

Question 2

This PL/pgSQL function backs an admin search box. What's the vulnerability?

CREATE FUNCTION search_customers(p_name TEXT)
RETURNS SETOF customers AS $$
BEGIN
    RETURN QUERY EXECUTE
        'SELECT * FROM customers WHERE name ILIKE ''%' || p_name || '%''';
END;
$$ LANGUAGE plpgsql;

Accepted Answer

Line 5 (Security / injection): The parameter is concatenated into a dynamically executed string, so a name containing a quote can rewrite the query. Code inside the database is just as injectable as application code.

Question 3

This should list users who haven't verified their email (verified_at is NULL). Why does it always return zero rows?

SELECT id, email
FROM users
WHERE verified_at = NULL
ORDER BY created_at;

Accepted Answer

Line 3 (Null / undefined handling): Any comparison with NULL using = yields NULL (unknown), never TRUE, so no row passes the filter. NULL checks need IS NULL.

Question 4

This query feeds the "Top 10 players" leaderboard. Why is the leaderboard full of beginners?

SELECT player_id, MAX(score) AS best_score
FROM game_results
GROUP BY player_id
ORDER BY best_score ASC
LIMIT 10;

Accepted Answer

Line 4 (Logic inverted): ASC sorts from lowest to highest, so LIMIT 10 keeps the ten worst scores. A top-N query needs descending order.

Question 5

users has 30M rows and a B-tree index on email. This query takes 20 seconds. Why?

SELECT COUNT(*)
FROM users
WHERE email LIKE '%@gmail.com';

Accepted Answer

Line 3 (Performance trap): A B-tree index is ordered by the start of the value, so a pattern that starts with % can't use it. The database scans all 30M rows.

SQL interview questions: spot the bug

SQL gotchas interviewers love

5 SQL spot-the-bug questions

January revenue with BETWEEN

Dynamic SQL in a stored function

Filtering with = NULL

Leaderboard top 10

Leading-wildcard LIKE

Attempts distribution