SQL Interview Questions Every Developer Should Know

SQL Is Still Everywhere (and It's Not Going Away)

Every couple of years someone declares SQL dead. And every couple of years, that person is wrong. SQL is embedded into almost every company's stack — whether it's Postgres, MySQL, BigQuery, Snowflake, or even SQLite running on your phone. If you write software that touches data (so... all software), you'll face SQL questions in interviews.

When I was interviewing for a backend role at a mid-size SaaS company, I expected system design and coding rounds. What I didn't expect was a full 45-minute round dedicated to writing SQL queries on a whiteboard. I hadn't reviewed SQL in months. It didn't go well. Don't be me.

The Two Formats You'll Encounter

SQL interviews typically come in two flavors:

"Write a query" format. They give you a schema (or draw it on the whiteboard) and ask you to write queries of increasing complexity. This is the more common format and it's hands-on. You need to produce working SQL.

"Explain the concept" format. More conversational — "what's the difference between INNER JOIN and LEFT JOIN?" or "when would you use an index?" This shows up in phone screens or as part of a broader technical discussion.

You need to be ready for both.

The Basics That Always Come Up

JOINs

If there's one SQL concept interviewers love, it's JOINs. Know these cold:

• INNER JOIN — returns only rows with matches in both tables
• LEFT JOIN — all rows from left table, matched rows from right (NULL if no match)
• RIGHT JOIN — opposite of LEFT JOIN (rarely used in practice)
• FULL OUTER JOIN — all rows from both tables (NULLs where no match)
• CROSS JOIN — cartesian product of both tables

The question that trips people up: "What's the difference between WHERE and ON in a JOIN?" In an INNER JOIN, they're functionally equivalent. But with LEFT JOINs, filtering in ON vs. WHERE produces different results because WHERE filters after the join, removing unmatched rows you wanted to keep.

GROUP BY and HAVING

GROUP BY aggregates rows. HAVING filters groups (like WHERE, but for aggregated data). Classic example:

-- Find customers who placed more than 5 orders
SELECT customer_id, COUNT(*) as order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5
ORDER BY order_count DESC;

A common mistake: using WHERE instead of HAVING for aggregate conditions. WHERE filters individual rows before grouping. HAVING filters after.

Subqueries

Subqueries show up constantly, in the SELECT, FROM, and WHERE clauses:

-- Find employees earning above their department average
SELECT e.name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
);

Know the difference between correlated and non-correlated subqueries. The one above is correlated — it references the outer query. Non-correlated subqueries run once and return a static result.

Intermediate Concepts Interviewers Love

Window Functions

If you're interviewing at a data-focused company, window functions are practically guaranteed. They let you compute values across a set of rows related to the current row without collapsing the result set.

-- Rank employees by salary within each department
SELECT
    name,
    department_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as dept_rank
FROM employees;

Key window functions to know: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER(), AVG() OVER().

The difference between RANK and DENSE_RANK catches people: RANK skips numbers after ties (1, 1, 3), DENSE_RANK doesn't (1, 1, 2).

Common Table Expressions (CTEs)

CTEs make complex queries readable. Interviewers love seeing them because they show you write maintainable SQL:

-- Find the top 3 products by revenue in each category
WITH product_revenue AS (
    SELECT
        p.category,
        p.product_name,
        SUM(o.quantity * o.unit_price) as total_revenue
    FROM products p
    JOIN order_items o ON p.product_id = o.product_id
    GROUP BY p.category, p.product_name
),
ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_revenue DESC) as rn
    FROM product_revenue
)
SELECT category, product_name, total_revenue
FROM ranked
WHERE rn <= 3;

Real-World Scenarios They'll Throw at You

Find Duplicates

SELECT email, COUNT(*) as cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Running Totals

SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) as running_total
FROM orders;

Top N Per Group

This comes up so often it's almost a cliche at this point:

-- Top 2 highest-paid employees per department
SELECT * FROM (
    SELECT
        name, department_id, salary,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as rn
    FROM employees
) ranked
WHERE rn <= 2;

Self Join: Find Pairs

-- Find employees who earn more than their manager
SELECT e.name as employee, m.name as manager
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

Performance Questions

Senior-level SQL interviews will go beyond writing queries. You'll need to talk about performance.

Indexes. Know what they are (B-tree structures that speed up lookups), when to use them (columns in WHERE, JOIN, ORDER BY), and when they hurt (tables with heavy writes, columns with low cardinality like boolean fields).

EXPLAIN / EXPLAIN ANALYZE. Interviewers might show you a slow query and ask you to optimize it. Reading an execution plan is a valuable skill. Look for sequential scans on large tables (should probably be an index scan), nested loop joins on big datasets (might need a hash join), and high row estimates vs. actuals.

Query optimization tips interviewers appreciate:

• Avoid SELECT * — only fetch columns you need
• Use EXISTS instead of IN for subqueries when the subquery returns many rows
• Be careful with functions in WHERE clauses — WHERE YEAR(created_at) = 2025 can't use an index, but WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01' can
• Denormalize selectively for read-heavy workloads

Practice Resources That Actually Help

Reading SQL theory only gets you so far. You need to write queries.

• LeetCode SQL — 50+ problems organized by difficulty. The "Department Highest Salary" and "Consecutive Numbers" problems are interview classics.
• HackerRank SQL — great for fundamentals if you're rusty on basics
• StrataScratch — real interview questions from specific companies, which is nice for targeted prep
• SQLZoo — interactive tutorials if you need to learn from scratch

A solid approach: do 2-3 SQL problems per day for two weeks. Start with basic JOINs and GROUP BY, then move to window functions and CTEs. By the end of two weeks, you'll handle most interview SQL questions confidently.

If you want to practice SQL questions in an interview setting with follow-up questions and time pressure, Craqly's AI copilot can simulate a technical round focused on databases and walk you through optimal solutions. Way better than just grinding problems in isolation.