SQL Interview Questions

Interview questions reuse a small set of patterns. Each answer below runs against one seed schema, shows its real output, and points out the edge case an interviewer is usually probing for. The site’s SQL interviews track goes deeper on each pattern.

-- seed.sql
CREATE TABLE departments (id INT PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE employees (
    id INT PRIMARY KEY, name TEXT NOT NULL, salary INT NOT NULL,
    department_id INT REFERENCES departments(id),
    manager_id INT REFERENCES employees(id), hire_date DATE NOT NULL
);
CREATE TABLE customers (id INT PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (
    id INT PRIMARY KEY, customer_id INT REFERENCES customers(id),  -- NULL = guest
    product_id INT NOT NULL, total NUMERIC(8, 2) NOT NULL, created_at DATE NOT NULL
);
CREATE TABLE logins (user_id INT NOT NULL, login_date DATE NOT NULL);
INSERT INTO departments VALUES (1, 'Eng'), (2, 'Sales'), (3, 'Legal');
INSERT INTO employees VALUES
    (1, 'Ana', 150000, 1, NULL, '2019-03-01'), (2, 'Ben', 120000, 1, 1, '2020-06-15'),
    (3, 'Cai', 120000, 1, 1, '2021-01-10'),    (4, 'Dee',  95000, 2, 1, '2021-07-01'),
    (5, 'Eli', 160000, 2, 4, '2022-02-01'),    (6, 'Fay',  70000, 2, 4, '2023-05-20'),
    (7, 'Ben',  60000, NULL, 4, '2024-01-05');
INSERT INTO customers VALUES (1, 'Ana'), (2, 'Ben'), (3, 'Cai'), (4, 'Dee');
INSERT INTO orders VALUES
    (1, 1, 10, 100.00, '2024-01-05'), (2, 1, 20,  50.00, '2024-02-10'),
    (3, 2, 10, 200.00, '2024-02-20'), (4, 1, 10,  80.00, '2024-03-02'),
    (5, 3, 20,  40.00, '2024-03-15'), (6, 3, 30, 120.00, '2024-03-28'),
    (7, NULL, 10, 30.00, '2024-03-30');
INSERT INTO logins VALUES
    (1, '2024-01-01'), (1, '2024-01-02'), (1, '2024-01-03'), (1, '2024-01-05'),
    (2, '2024-01-02'), (2, '2024-01-03');

$ psql -d sqldemo -f seed.sql
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 3
INSERT 0 7
INSERT 0 4
INSERT 0 7
INSERT 0 6

1. Find Duplicate Values

-- q01_duplicates.sql
SELECT name, count(*) AS occurrences
FROM employees
GROUP BY name
HAVING count(*) > 1;

$ psql -d sqldemo -f q01_duplicates.sql
 name | occurrences 
------+-------------
 Ben  |           2
(1 row)

Group by whatever defines “the same”, then keep groups with more than one row. HAVING, not WHERE, because the condition is on the group’s count.

2. The Nth Highest Salary

The tempting answer skips rows with OFFSET. Ask for the 4th highest salary.

-- q02_nth_salary.sql
SELECT salary AS offset_answer FROM employees ORDER BY salary DESC OFFSET 3 LIMIT 1;

SELECT DISTINCT salary AS dense_rank_answer
FROM (SELECT salary, dense_rank() OVER (ORDER BY salary DESC) AS rnk
      FROM employees) ranked
WHERE rnk = 4;

$ psql -d sqldemo -f q02_nth_salary.sql
 offset_answer 
---------------
        120000
(1 row)

 dense_rank_answer 
-------------------
             95000
(1 row)

Ben and Cai both earn 120,000, so the 4th row is still 120,000, but the 4th distinct salary is 95,000. DENSE_RANK gives tied salaries the same rank with no gaps, which is what “Nth highest salary” means. Always ask the interviewer how ties count.

3. Employees Earning More Than Their Manager

-- q03_more_than_manager.sql
SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.salary > m.salary;

$ psql -d sqldemo -f q03_more_than_manager.sql
 employee | salary | manager | manager_salary 
----------+--------+---------+----------------
 Eli      | 160000 | Dee     |          95000
(1 row)

A self-join gives each employee row its manager’s row side by side. The inner join also drops Ana, who has no manager, which is correct here: there is nobody to compare with.

4. Departments With No Employees

-- q04_empty_departments.sql
SELECT d.name
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
WHERE e.id IS NULL;

$ psql -d sqldemo -f q04_empty_departments.sql
 name  
-------
 Legal
(1 row)

The anti-join pattern: left join, then keep rows where the right side’s primary key is NULL. Test the key column, never a column that could be NULL in real data.

5. Customers Who Never Ordered: the NOT IN Trap

Order 7 was a guest checkout with no customer. Compare NOT IN with NOT EXISTS.

-- q05_never_ordered.sql
SELECT name AS not_in FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

SELECT c.name AS not_exists FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

$ psql -d sqldemo -f q05_never_ordered.sql
 not_in 
--------
(0 rows)

 not_exists 
------------
 Dee
(1 row)

NOT IN returned nothing. The guest order put a NULL in the list, and id <> NULL is unknown, so no customer can pass. NOT EXISTS correctly finds Dee. This is the most common “gotcha” in SQL interviews.

6. Delete Duplicates, Keeping One

Remove duplicate employee names, keeping the lowest id. The transaction lets us look and roll back.

-- q06_delete_duplicates.sql
BEGIN;
DELETE FROM employees e
USING employees keep
WHERE e.name = keep.name AND e.id > keep.id
RETURNING e.id, e.name;
ROLLBACK;

$ psql -d sqldemo -f q06_delete_duplicates.sql
BEGIN
 id | name 
----+------
  7 | Ben
(1 row)

DELETE 1
ROLLBACK

Each row is deleted if another row with the same name has a smaller id, so exactly one per name survives. Running destructive answers inside BEGIN ... ROLLBACK first is a habit interviewers notice.

7. Count NULLs per Column

-- q07_null_counts.sql
SELECT count(*) - count(department_id) AS null_department,
       count(*) - count(manager_id)    AS null_manager
FROM employees;

$ psql -d sqldemo -f q07_null_counts.sql
 null_department | null_manager 
-----------------+--------------
               1 |            1
(1 row)

count(*) counts rows and count(col) counts non-NULL values, so the difference is the number of NULLs. One pass over the table answers it for every column.

8. Most Recent Order per Customer

-- q08_latest_per_customer.sql
SELECT DISTINCT ON (customer_id) customer_id, id AS order_id, created_at
FROM orders
WHERE customer_id IS NOT NULL
ORDER BY customer_id, created_at DESC;

$ psql -d sqldemo -f q08_latest_per_customer.sql
 customer_id | order_id | created_at 
-------------+----------+------------
           1 |        4 | 2024-03-02
           2 |        3 | 2024-02-20
           3 |        6 | 2024-03-28
(3 rows)

DISTINCT ON keeps the first row of each customer_id group in ORDER BY order, so sorting by date descending keeps the newest. It is PostgreSQL-specific; the portable answer ranks rows with ROW_NUMBER() in a subquery and keeps rank 1, the same shape as the next question.

9. Top 2 Earners per Department

-- q09_top_two_per_dept.sql
SELECT department, name, salary
FROM (SELECT d.name AS department, e.name, e.salary,
             dense_rank() OVER (PARTITION BY d.id ORDER BY e.salary DESC) AS rnk
      FROM employees e JOIN departments d ON d.id = e.department_id) t
WHERE rnk <= 2
ORDER BY department, salary DESC, name;

$ psql -d sqldemo -f q09_top_two_per_dept.sql
 department | name | salary 
------------+------+--------
 Eng        | Ana  | 150000
 Eng        | Ben  | 120000
 Eng        | Cai  | 120000
 Sales      | Eli  | 160000
 Sales      | Dee  |  95000
(5 rows)

Eng returned three people because Ben and Cai tie for second. With ROW_NUMBER one of them would be dropped arbitrarily; with DENSE_RANK both stay. Which one is right depends on the question, so say which you chose and why.

10. Month-Over-Month Revenue Growth

-- q10_mom_growth.sql
SELECT month, revenue,
       lag(revenue) OVER (ORDER BY month) AS previous,
       round(100.0 * (revenue - lag(revenue) OVER (ORDER BY month))
             / lag(revenue) OVER (ORDER BY month), 1) AS growth_pct
FROM (SELECT date_trunc('month', created_at)::date AS month, sum(total) AS revenue
      FROM orders GROUP BY 1) m
ORDER BY month;

$ psql -d sqldemo -f q10_mom_growth.sql
   month    | revenue | previous | growth_pct 
------------+---------+----------+------------
 2024-01-01 |  100.00 |          |           
 2024-02-01 |  250.00 |   100.00 |      150.0
 2024-03-01 |  270.00 |   250.00 |        8.0
(3 rows)

Aggregate to one row per month first, then let LAG read the previous month. January has no previous month, so its growth is NULL rather than an error or a misleading 0.

11. Consecutive Login Streaks (Gaps and Islands)

-- q11_login_streaks.sql
SELECT user_id, min(login_date) AS streak_start, count(*) AS days
FROM (SELECT user_id, login_date,
             login_date - (row_number() OVER (PARTITION BY user_id
                                              ORDER BY login_date))::int AS grp
      FROM logins) t
GROUP BY user_id, grp
ORDER BY user_id, streak_start;

$ psql -d sqldemo -f q11_login_streaks.sql
 user_id | streak_start | days 
---------+--------------+------
       1 | 2024-01-01   |    3
       1 | 2024-01-05   |    1
       2 | 2024-01-02   |    2
(3 rows)

Subtracting a row number from each date gives the same value for every day in an unbroken run, because both climb by one together. A missed day breaks the pattern and starts a new group. User 1’s January 5 login is a separate one-day streak because January 4 is missing.

12. Pivot Rows Into Columns

-- q12_pivot.sql
SELECT c.name,
       COALESCE(sum(o.total) FILTER (WHERE o.created_at < '2024-02-01'), 0) AS jan,
       COALESCE(sum(o.total) FILTER (WHERE o.created_at >= '2024-02-01'
                                       AND o.created_at < '2024-03-01'), 0) AS feb,
       COALESCE(sum(o.total) FILTER (WHERE o.created_at >= '2024-03-01'), 0) AS mar
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name ORDER BY c.id;

$ psql -d sqldemo -f q12_pivot.sql
 name |  jan   |  feb   |  mar   
------+--------+--------+--------
 Ana  | 100.00 |  50.00 |  80.00
 Ben  |      0 | 200.00 |      0
 Cai  |      0 |      0 | 160.00
 Dee  |      0 |      0 |      0
(4 rows)

Conditional aggregation turns each month into a column. In databases without FILTER, write sum(CASE WHEN ... THEN o.total END) instead. The left join keeps Dee with zeros.

13. Median Salary

-- q13_median.sql
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY salary) AS median_cont,
       percentile_disc(0.5) WITHIN GROUP (ORDER BY salary) AS median_disc,
       round(avg(salary)) AS mean
FROM employees;

$ psql -d sqldemo -f q13_median.sql
 median_cont | median_disc |  mean  
-------------+-------------+--------
      120000 |      120000 | 110714
(1 row)

With seven salaries, the median is the 4th value, so both functions agree. With an even count, percentile_cont averages the two middle values and percentile_disc returns an actual salary. The mean lands below the median because the two lowest salaries sit further from the middle than the two highest do. Either tail moves the mean and leaves the median alone, which is why salaries are usually reported as medians.

14. Percentage of the Group Total

-- q14_pct_of_dept.sql
SELECT d.name AS department, e.name, e.salary,
       round(100.0 * e.salary / sum(e.salary) OVER (PARTITION BY d.id), 1) AS pct_of_dept
FROM employees e JOIN departments d ON d.id = e.department_id
ORDER BY d.name, e.salary DESC, e.name;

$ psql -d sqldemo -f q14_pct_of_dept.sql
 department | name | salary | pct_of_dept 
------------+------+--------+-------------
 Eng        | Ana  | 150000 |        38.5
 Eng        | Ben  | 120000 |        30.8
 Eng        | Cai  | 120000 |        30.8
 Sales      | Eli  | 160000 |        49.2
 Sales      | Dee  |  95000 |        29.2
 Sales      | Fay  |  70000 |        21.5
(6 rows)

A windowed sum puts the department total on every row without collapsing them. 100.0 forces numeric division; 100 * salary / total in integers would truncate every percentage.

15. Find Gaps in a Sequence

-- q15_gaps.sql
SELECT id + 1 AS gap_start, next_id - 1 AS gap_end
FROM (SELECT id, lead(id) OVER (ORDER BY id) AS next_id
      FROM (VALUES (1), (2), (3), (5), (6), (9)) AS t(id)) s
WHERE next_id - id > 1;

$ psql -d sqldemo -f q15_gaps.sql
 gap_start | gap_end 
-----------+---------
         4 |       4
         7 |       8
(2 rows)

LEAD pairs each id with the next one; any jump bigger than 1 is a gap. The result lists ranges, so a gap of several missing ids is one row, not several.

16. UNION vs UNION ALL

-- q16_union.sql
SELECT count(*) AS union_rows
FROM (SELECT name FROM customers UNION SELECT name FROM employees) u;
SELECT count(*) AS union_all_rows
FROM (SELECT name FROM customers UNION ALL SELECT name FROM employees) u;

$ psql -d sqldemo -f q16_union.sql
 union_rows 
------------
          6
(1 row)

 union_all_rows 
----------------
             11
(1 row)

UNION removes duplicates across the combined result, including the duplicate Ben inside employees itself. UNION ALL keeps all 11 rows and skips the deduplication work, so use it whenever duplicates are impossible or wanted.

17. Walk an Org Chart

-- q17_org_chart.sql
WITH RECURSIVE chain AS (
    SELECT id, name, manager_id, 0 AS depth FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, c.depth + 1
    FROM employees e JOIN chain c ON e.manager_id = c.id
)
SELECT repeat('  ', depth) || name AS org, depth FROM chain ORDER BY depth, id;

$ psql -d sqldemo -f q17_org_chart.sql
   org   | depth 
---------+-------
 Ana     |     0
   Ben   |     1
   Cai   |     1
   Dee   |     1
     Eli |     2
     Fay |     2
     Ben |     2
(7 rows)

The anchor starts at the top, and each recursive step adds the next layer of reports. Mention cycle protection (CYCLE in PostgreSQL 14+) if the data might contain a loop.

18. Bought Product A but Not Product B

-- q18_a_not_b.sql
SELECT c.name
FROM customers c
WHERE EXISTS     (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.product_id = 10)
  AND NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.product_id = 20);

$ psql -d sqldemo -f q18_a_not_b.sql
 name 
------
 Ben
(1 row)

Ana bought both, Cai never bought product 10, so only Ben qualifies. EXISTS and NOT EXISTS read almost exactly like the question, which makes them easy to explain aloud.

19. Month-to-Month Retention

What share of February’s customers ordered again in March?

-- q19_retention.sql
WITH feb AS (SELECT DISTINCT customer_id FROM orders
             WHERE created_at >= '2024-02-01' AND created_at < '2024-03-01'),
     mar AS (SELECT DISTINCT customer_id FROM orders
             WHERE created_at >= '2024-03-01' AND created_at < '2024-04-01')
SELECT count(*) AS feb_customers,
       count(mar.customer_id) AS retained,
       round(100.0 * count(mar.customer_id) / count(*), 1) AS retention_pct
FROM feb LEFT JOIN mar ON mar.customer_id = feb.customer_id;

$ psql -d sqldemo -f q19_retention.sql
 feb_customers | retained | retention_pct 
---------------+----------+---------------
             2 |        1 |          50.0
(1 row)

Ana ordered in both months and Ben only in February, so retention is 50%. The left join keeps every February customer, and count(mar.customer_id) counts only those who matched.

20. Fill Missing Days With Zero

-- q20_date_series.sql
SELECT d::date AS day, COALESCE(sum(o.total), 0) AS revenue
FROM generate_series(DATE '2024-03-01', DATE '2024-03-05', INTERVAL '1 day') AS d
LEFT JOIN orders o ON o.created_at = d::date
GROUP BY d ORDER BY d;

$ psql -d sqldemo -f q20_date_series.sql
    day     | revenue 
------------+---------
 2024-03-01 |       0
 2024-03-02 |   80.00
 2024-03-03 |       0
 2024-03-04 |       0
 2024-03-05 |       0
(5 rows)

Grouping orders alone would return only March 2, the one day with sales. Generating every date first and left-joining orders onto it produces the zero rows that charts and reports need.

Practice

1. Find the second most recent order for each customer who has at least two orders.

-- practice_second_order.sql
SELECT customer_id, id AS order_id, created_at
FROM (SELECT *, row_number() OVER (PARTITION BY customer_id
                                   ORDER BY created_at DESC) AS rn
      FROM orders WHERE customer_id IS NOT NULL) t
WHERE rn = 2 ORDER BY customer_id;

$ psql -d sqldemo -f practice_second_order.sql
 customer_id | order_id | created_at 
-------------+----------+------------
           1 |        2 | 2024-02-10
           3 |        5 | 2024-03-15
(2 rows)

2. Rewrite question 5 with a LEFT JOIN anti-join instead of NOT EXISTS.

-- practice_anti_join.sql
SELECT c.name
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

$ psql -d sqldemo -f practice_anti_join.sql
 name 
------
 Dee
(1 row)

3. List employees hired more recently than their manager.

-- practice_hired_after_manager.sql
SELECT e.name, e.hire_date, m.name AS manager, m.hire_date AS manager_hired
FROM employees e JOIN employees m ON m.id = e.manager_id
WHERE e.hire_date > m.hire_date
ORDER BY e.hire_date;

$ psql -d sqldemo -f practice_hired_after_manager.sql
 name | hire_date  | manager | manager_hired 
------+------------+---------+---------------
 Ben  | 2020-06-15 | Ana     | 2019-03-01
 Cai  | 2021-01-10 | Ana     | 2019-03-01
 Dee  | 2021-07-01 | Ana     | 2019-03-01
 Eli  | 2022-02-01 | Dee     | 2021-07-01
 Fay  | 2023-05-20 | Dee     | 2021-07-01
 Ben  | 2024-01-05 | Dee     | 2021-07-01
(6 rows)