SQL Interview Questions Guide (2026): 50+ Questions Answered

SQL interviews can be intimidating, but with the right preparation, you can confidently tackle any question. After conducting hundreds of technical interviews and coaching developers through the job search process, I've identified the patterns that separate successful candidates from the rest.

This guide covers the most common SQL interview questions, organized by difficulty and topic, with strategies to help you excel.

What Interviewers Really Want to See

Before diving into questions, understand what interviewers evaluate:

1. SQL Fundamentals: Solid understanding of SELECT, JOIN, WHERE, GROUP BY
2. Problem-Solving: Ability to break down complex requirements
3. Query Optimization: Knowledge of performance and best practices
4. Communication: Explaining your thought process clearly
5. Real-World Experience: Practical application of SQL concepts

Interview Question Categories

1. Basic SQL (Junior Level)

These questions test fundamental understanding:

Q1: Write a query to find all customers who made purchases in the last 30 days

SELECT DISTINCT c.customer_id, c.customer_name, c.email
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days';

What they're testing:

• JOIN syntax
• Date filtering
• DISTINCT usage

Pro tip: Mention that you'd add an index on order_date for performance.

Q2: Calculate the total revenue per product category

SELECT
    p.category,
    SUM(oi.quantity * oi.unit_price) as total_revenue
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.category
ORDER BY total_revenue DESC;

What they're testing:

• JOIN operations
• Aggregations (SUM)
• GROUP BY understanding

Follow-up: "How would you include categories with no sales?"

-- Use LEFT JOIN
SELECT
    p.category,
    COALESCE(SUM(oi.quantity * oi.unit_price), 0) as total_revenue
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.category
ORDER BY total_revenue DESC;

2. Intermediate SQL (Mid-Level)

These require deeper SQL knowledge:

Q3: Find the second highest salary in each department

-- Method 1: Using window functions (most efficient)
WITH ranked_salaries AS (
    SELECT
        department,
        employee_name,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) as salary_rank
    FROM employees
)
SELECT department, employee_name, salary
FROM ranked_salaries
WHERE salary_rank = 2;

-- Method 2: Using subquery (works everywhere)
SELECT
    e1.department,
    e1.employee_name,
    e1.salary
FROM employees e1
WHERE salary = (
    SELECT MAX(salary)
    FROM employees e2
    WHERE e2.department = e1.department
      AND e2.salary < (
          SELECT MAX(salary)
          FROM employees e3
          WHERE e3.department = e1.department
      )
);

What they're testing:

• Window functions (preferred) vs subqueries
• Understanding of RANK vs DENSE_RANK
• Problem-solving approaches

Important: Always ask clarifying questions:

• "What if there's a tie for second highest?"
• "What if a department has fewer than 2 employees?"

Q4: Calculate running totals and month-over-month growth

WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', order_date) as month,
        SUM(total_amount) as revenue
    FROM orders
    GROUP BY 1
)
SELECT
    month,
    revenue,
    -- Running total
    SUM(revenue) OVER (ORDER BY month) as cumulative_revenue,
    -- Previous month
    LAG(revenue) OVER (ORDER BY month) as prev_month_revenue,
    -- Month-over-month growth
    revenue - LAG(revenue) OVER (ORDER BY month) as mom_growth,
    -- Growth percentage
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month)) /
        NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100,
        2
    ) as mom_growth_pct
FROM monthly_revenue
ORDER BY month;

What they're testing:

• Window functions (SUM OVER, LAG)
• Date handling
• NULL handling (NULLIF for division by zero)
• CTEs for query organization

Q5: Find customers who haven't made a purchase in 6 months

-- Method 1: Using NOT EXISTS (efficient)
SELECT
    c.customer_id,
    c.customer_name,
    MAX(o.order_date) as last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o2
    WHERE o2.customer_id = c.customer_id
      AND o2.order_date >= CURRENT_DATE - INTERVAL '6 months'
)
GROUP BY c.customer_id, c.customer_name;

-- Method 2: Using HAVING
SELECT
    c.customer_id,
    c.customer_name,
    MAX(o.order_date) as last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING MAX(o.order_date) < CURRENT_DATE - INTERVAL '6 months'
   OR MAX(o.order_date) IS NULL;

What they're testing:

• NOT EXISTS vs other approaches
• NULL handling for customers with no orders
• Date arithmetic

3. Advanced SQL (Senior Level)

These test expert-level skills:

Q6: Identify gaps in a sequence

-- Find missing order IDs in sequence
WITH order_sequence AS (
    SELECT
        order_id,
        LEAD(order_id) OVER (ORDER BY order_id) as next_order_id
    FROM orders
)
SELECT
    order_id as gap_start,
    next_order_id as gap_end,
    next_order_id - order_id - 1 as gap_size
FROM order_sequence
WHERE next_order_id - order_id > 1;

What they're testing:

• LEAD function
• Sequence analysis
• Creative problem-solving

Q7: Pivoting data (rows to columns)

-- Transform monthly sales data from rows to columns
SELECT
    product_id,
    SUM(CASE WHEN month = 1 THEN sales ELSE 0 END) as jan_sales,
    SUM(CASE WHEN month = 2 THEN sales ELSE 0 END) as feb_sales,
    SUM(CASE WHEN month = 3 THEN sales ELSE 0 END) as mar_sales,
    SUM(CASE WHEN month = 4 THEN sales ELSE 0 END) as apr_sales,
    -- ... and so on for remaining months
    SUM(sales) as total_annual_sales
FROM monthly_sales
WHERE year = 2026
GROUP BY product_id;

-- PostgreSQL: Using CROSSTAB (cleaner for many columns)
SELECT * FROM CROSSTAB(
    'SELECT product_id, month, sales FROM monthly_sales WHERE year = 2026',
    'SELECT DISTINCT month FROM monthly_sales ORDER BY 1'
) AS ct(
    product_id INT,
    jan NUMERIC,
    feb NUMERIC,
    mar NUMERIC
    -- ... etc
);

What they're testing:

• CASE statements with aggregation
• Data transformation
• Knowledge of database-specific features

Q8: Recursive CTE for hierarchical data

-- Get employee hierarchy (employees and all their subordinates)
WITH RECURSIVE employee_hierarchy AS (
    -- Anchor: Start with the manager
    SELECT
        employee_id,
        employee_name,
        manager_id,
        1 as level,
        ARRAY[employee_id] as path
    FROM employees
    WHERE employee_id = 100  -- Starting manager

    UNION ALL

    -- Recursive: Get subordinates
    SELECT
        e.employee_id,
        e.employee_name,
        e.manager_id,
        eh.level + 1,
        eh.path || e.employee_id
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
    WHERE NOT e.employee_id = ANY(eh.path)  -- Prevent cycles
)
SELECT
    REPEAT('  ', level - 1) || employee_name as employee_hierarchy,
    level,
    path
FROM employee_hierarchy
ORDER BY path;

What they're testing:

• Recursive CTE understanding
• Hierarchical data handling
• Cycle prevention
• Complex query structure

Common Optimization Questions

Q9: "How would you optimize this slow query?"

-- SLOW QUERY:
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE DATE(o.order_date) = '2026-01-15'
  AND c.status = 'active';

Optimization steps to mention:

1. Don't use functions on indexed columns:

-- Bad: DATE(order_date) prevents index usage
WHERE DATE(order_date) = '2026-01-15'

-- Good: Use range
WHERE order_date >= '2026-01-15'
  AND order_date < '2026-01-16'

2. Select only needed columns:

-- Bad: SELECT *
-- Good: SELECT order_id, customer_name, order_total

3. Check indexes exist:

CREATE INDEX idx_orders_date ON orders(order_date);
CREATE INDEX idx_customers_status ON customers(status);

4. Consider query execution plan:

EXPLAIN ANALYZE
SELECT ...;

What they're testing:

• Index knowledge
• Query optimization principles
• Ability to read execution plans
• Practical performance debugging

System Design SQL Interview Questions

Senior-level interviews often include schema design questions. These test whether you understand data modeling, not just query syntax.

Q10: Design a schema for an e-commerce order system

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    email       TEXT UNIQUE NOT NULL,
    name        TEXT NOT NULL,
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE products (
    product_id  SERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    category    TEXT,
    price       NUMERIC(10, 2) NOT NULL,
    stock_qty   INTEGER DEFAULT 0
);

CREATE TABLE orders (
    order_id    SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    status      TEXT CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
    total       NUMERIC(10, 2),
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE order_items (
    item_id     SERIAL PRIMARY KEY,
    order_id    INTEGER REFERENCES orders(order_id),
    product_id  INTEGER REFERENCES products(product_id),
    quantity    INTEGER NOT NULL,
    unit_price  NUMERIC(10, 2) NOT NULL
);

-- Indexes for common query patterns
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_created  ON orders(created_at);
CREATE INDEX idx_items_order     ON order_items(order_id);

What interviewers look for: foreign keys for referential integrity, appropriate data types, indexes on join columns, and whether you think about consistency (e.g., should total be stored or computed from line items?).

Q11: When would you denormalize a schema?

Normalization removes redundancy. Denormalization deliberately reintroduces it for performance. Good answers include:

• Read-heavy reporting tables where a JOIN across 5 tables runs thousands of times per minute
• Analytics/warehouse schemas — star schema with fact tables + dimension tables is standard in data warehousing
• Caching aggregations — a total_orders column on customers, updated by a trigger or background job

Always frame it as: "I start normalized, profile the hot paths, then selectively denormalize — never upfront."

---

Behavioral Interview Strategies

How to Approach Any SQL Question:

1. Clarify Requirements:

   • "Should this include deleted orders?"
   • "How should we handle NULL values?"
   • "What's the expected data volume?"

2. Think Aloud:

   • "I'm thinking we need a JOIN here because..."
   • "Let me start with a simple query and then optimize..."

3. Start Simple:

   • Write basic query first
   • Add complexity incrementally
   • Test mental edge cases

4. Consider Performance:

   • "For large datasets, I'd add an index on..."
   • "This subquery might be slow; we could use a CTE instead..."

5. Ask for Feedback:

   • "Does this meet your requirements?"
   • "Would you like me to optimize this further?"

Database-Specific Questions

Research the company's database before your interview. Here are the patterns most likely to come up.

PostgreSQL

JSONB querying — common in roles using event-driven or API-backed schemas:

-- Filter products by a JSON attribute
SELECT name, specs->>'color' AS color
FROM products
WHERE specs @> '{"material": "cotton"}';

Array operations:

-- Orders that include a specific product in an array column
SELECT order_id FROM orders
WHERE 12 = ANY(product_ids);

Full-text search:

SELECT title, body
FROM articles
WHERE to_tsvector('english', title || ' ' || body)
      @@ to_tsquery('sql & optimization');

MySQL

GROUP_CONCAT (MySQL's STRING_AGG equivalent):

SELECT product_id, GROUP_CONCAT(tag ORDER BY tag SEPARATOR ', ') AS tags
FROM product_tags
GROUP BY product_id;

Upsert with ON DUPLICATE KEY:

INSERT INTO daily_stats (date, page_views)
VALUES ('2026-06-20', 100)
ON DUPLICATE KEY UPDATE page_views = page_views + VALUES(page_views);

SQL Server (T-SQL)

TOP instead of LIMIT:

SELECT TOP 10 customer_id, name FROM customers ORDER BY created_at DESC;

STRING_AGG (SQL Server 2017+):

SELECT order_id, STRING_AGG(product_name, ', ') AS products
FROM order_items
GROUP BY order_id;

Red Flags to Avoid

Common mistakes that hurt candidates:

1. SELECT * in production queries
2. No WHERE clause on large table DELETE/UPDATE
3. Nested subqueries when JOINs would work
4. Not considering NULL values
5. Forgetting to add indexes
6. Using cursors when set-based operations work
7. Not testing edge cases

Practice Resources

To prepare effectively:

1. LeetCode SQL: 50+ SQL problems (free)
2. HackerRank SQL: Structured learning path
3. SQLZoo: Interactive tutorials
4. Mode SQL Tutorial: Real business scenarios
5. Real Databases: Practice on actual data
6. 10 Essential SQL Queries Every Developer Should Know — the core patterns with performance notes and interview breakdowns

Interview Day Tips

Before the Interview:

• Review company's tech stack
• Practice on their specific database
• Prepare questions about their data architecture

During the Interview:

• Write clear, formatted SQL
• Use meaningful aliases (avoid a, b, c)
• Comment complex logic
• Think out loud
• Ask clarifying questions

After Writing the Query:

• Walk through with sample data
• Mention potential optimizations
• Discuss edge cases
• Ask if they want you to explain any part

Complete Preparation Path

Week 1-2: Fundamentals

• SELECT, WHERE, JOIN, GROUP BY
• Aggregate functions
• Subqueries
• Basic optimization

Week 3-4: Intermediate

• Window functions
• CTEs
• CASE statements
• String and date functions

Week 5-6: Advanced

• Recursive CTEs
• Query optimization
• Execution plans
• Database-specific features

Week 7-8: Practice

• LeetCode Medium/Hard problems
• Mock interviews
• Review common patterns

Frequently Asked Questions

How do I prepare for a SQL technical interview in 2026?

The most effective preparation follows four phases: (1) master core syntax — SELECT, JOINs, GROUP BY, subqueries; (2) learn window functions and CTEs, which appear in almost every mid-to-senior interview; (3) understand query optimization and how indexes affect execution plans; (4) practice on real schemas, not toy examples. LeetCode SQL Medium difficulty is a reliable benchmark — if you can solve those consistently, you're ready for most interviews. Budget 4–6 weeks for thorough preparation.

What SQL questions are asked in data engineer interviews?

Data engineer SQL interviews focus on: window functions (ROW_NUMBER, RANK, LAG/LEAD), CTEs for multi-step transformations, aggregation over time windows (month-over-month, rolling 7-day), deduplication patterns, and schema design for analytical pipelines. You'll often be given a dataset and asked to compute a business metric from it. PostgreSQL and BigQuery/Snowflake syntax differences may come up if the role uses cloud data warehouses.

How do I prepare for a SQL interview with no experience?

Start with the core syntax: SELECT, WHERE, JOINs, GROUP BY, HAVING. Once those are automatic, add subqueries, then CTEs, then window functions. The most common mistake is jumping to window functions before GROUP BY is fully internalized. For practice, LeetCode SQL Easy is a good starting point; move to Medium once basics feel solid. Most junior-level interviews test only basics and one or two intermediate patterns.

What is the hardest SQL interview question?

Recursive CTEs for hierarchical data (org charts, category trees) and multi-step window function problems are consistently the hardest. "Find the median salary per department," "sessionization" (grouping user events into sessions based on time gaps), and "island and gaps" problems (find consecutive date sequences) trip up even experienced candidates. These appear in senior and staff engineer interviews, not junior ones.

---

Your Next Steps

Ready to ace your SQL interview?

1. Master the fundamentals: SQL Crash Course covers everything from basics to expert level with 350+ examples
2. Practice 140+ interview questions with detailed solutions in the SQL Interview Questions Prep guide
3. Learn query optimization with real-world techniques
4. Get hands-on practice with included e-commerce database

Don't let SQL interviews intimidate you. With structured practice and the right resources, you'll confidently tackle any question thrown your way.

---

Have SQL interview questions you're struggling with? Get in touch for personalized guidance, or share your interview experiences in the comments!