Srinimf

Quick SQL Interview Questions for Data Engineers (Little Tricky)

Srini

This post covers tricky SQL Interview Questions designed specifically for data engineers. Whether you are preparing for your next technical round or brushing up on advanced concepts, you will find practical, interview-ready problems here — complete with table definitions and step-by-step SQL answers. These questions go beyond the basics and test skills that hiring teams at data-focused companies actually care about, including window functions, CTEs, duplicate handling, and gap detection. If you want to learn more about the author’s background, visit the About Srini page, or explore the Books page for recommended reading on SQL and data engineering. For deeper context on data integration concepts referenced in some of these problems

1. Find 2nd Highest Salary

Table:

CREATE TABLE employees (
    emp_id INT,
    emp_name VARCHAR(50),
    salary INT
);INSERT INTO employees VALUES
(1,'John',5000),
(2,'Mary',7000),
(3,'David',7000),
(4,'Sam',4000);

Question:

Find the 2nd highest salary.

Answer:

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

2. Find Duplicate Records

Table:

CREATE TABLE customers (
    id INT,
    email VARCHAR(100)
);INSERT INTO customers VALUES
(1,'a@gmail.com'),
(2,'b@gmail.com'),
(3,'a@gmail.com');

Question:

Find duplicate emails.

Answer:

SELECT email, COUNT(*)
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

3. Latest Record Per Customer

Table:

CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    amount INT,
    order_date DATE
);INSERT INTO orders VALUES
(1,101,500,'2024-01-01'),
(2,101,700,'2024-02-01'),
(3,102,300,'2024-01-10');

Question:

Get latest order for each customer.

Answer:

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY order_date DESC) rn
    FROM orders
) t
WHERE rn = 1;

4. Running Total

Question:

Show cumulative sales.

Answer:

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

5. Customers With No Orders

Tables:

CREATE TABLE customers (
    customer_id INT,
    name VARCHAR(50)
);CREATE TABLE orders (
    order_id INT,
    customer_id INT
);

Question:

Find customers who never placed orders.

Answer:

SELECT c.*
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

6. Remove Duplicate Rows Keep Latest

Table:

CREATE TABLE emp_logs (
    emp_id INT,
    salary INT,
    updated_at DATE
);

Question:

Keep latest row per employee.

Answer:

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER(PARTITION BY emp_id ORDER BY updated_at DESC) rn
    FROM emp_logs
) t
WHERE rn = 1;

7. Find Consecutive Login Days

Table:

CREATE TABLE logins (
    user_id INT,
    login_date DATE
);

Question:

Users logged in 3 consecutive days.

Trick Concept:

Use ROW_NUMBER() + date difference grouping.

Table:
CREATE TABLE logins (
    user_id INT,
    login_date DATE
);

INSERT INTO logins VALUES
(101,'2024-01-01'),
(101,'2024-01-02'),
(101,'2024-01-03'),
(102,'2024-01-01'),
(102,'2024-01-03'),
(102,'2024-01-04');
Question:

Find users who logged in for 3 consecutive days.

Logic:

Use ROW_NUMBER() and subtract row number from date. Consecutive dates fall into same group.

Answer:
WITH cte AS (
    SELECT user_id,
           login_date,
           ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY login_date) rn
    FROM logins
),
grp AS (
    SELECT user_id,
           login_date,
           DATEADD(day, -rn, login_date) grp_date
    FROM cte
)
SELECT user_id
FROM grp
GROUP BY user_id, grp_date
HAVING COUNT(*) >= 3;
PostgreSQL Version:
WITH cte AS (
    SELECT user_id,
           login_date,
           ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY login_date) rn
    FROM logins
),
grp AS (
    SELECT user_id,
           login_date,
           login_date - rn * INTERVAL '1 day' grp_date
    FROM cte
)
SELECT user_id
FROM grp
GROUP BY user_id, grp_date
HAVING COUNT(*) >= 3;

8. Swap Values Without Temp Table

Table:

CREATE TABLE seats (
    id INT,
    student VARCHAR(50)
);

Swap adjacent students.

CREATE TABLE seats (
    id INT,
    student VARCHAR(50)
);

INSERT INTO seats VALUES
(1,'John'),
(2,'Mary'),
(3,'David'),
(4,'Sam'),
(5,'Tom');
Question:

Swap adjacent students.

Expected Output:
id	student
1	Mary
2	John
3	Sam
4	David
5	Tom

(Last odd row remains same)

Answer:
SELECT id,
       CASE
           WHEN id % 2 = 1 AND id = (SELECT MAX(id) FROM seats)
                THEN student
           WHEN id % 2 = 1
                THEN LEAD(student) OVER(ORDER BY id)
           ELSE LAG(student) OVER(ORDER BY id)
       END AS student
FROM seats
ORDER BY id;

9. Highest Salary Department Wise

Table:

CREATE TABLE emp (
    emp_id INT,
    dept VARCHAR(50),
    salary INT
);

Answer:

SELECT *
FROM (
    SELECT *,
           DENSE_RANK() OVER(PARTITION BY dept ORDER BY salary DESC) rn
    FROM emp
) t
WHERE rn = 1;

10. Detect Gaps in IDs

Table:

CREATE TABLE transactions (
    txn_id INT
);

Find missing IDs.

SELECT t1.txn_id + 1 AS missing_id
FROM transactions t1
LEFT JOIN transactions t2
ON t1.txn_id + 1 = t2.txn_id
WHERE t2.txn_id IS NULL;

Most Asked Concepts in Data Engineer SQL Interviews

  • Window Functions
  • CTE
  • Joins
  • Duplicate Removal
  • Incremental Load Logic
  • Latest Record Logic
  • Running Totals
  • Ranking
  • Gap Detection
  • SCD Type 2

Pro Tip for Interview

If stuck:

  1. Break into steps
  2. Use CTE
  3. Use ROW_NUMBER()
  4. Explain logic clearly

To deepen your understanding of the window functions used throughout these SQL interview questions, refer to the official PostgreSQL documentation on window functions and the Microsoft Learn reference for ROW_NUMBER() — both are authoritative resources that explain the syntax, behaviour, and edge cases you may be asked about in an interview. If you have questions or want to suggest additional problems, feel free to reach out via the Contact us page.

, , ,

Related Posts