Use these for interviews it includes table creation + query questions.
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:
- Break into steps
- Use CTE
- Use ROW_NUMBER()
- Explain logic clearly
Srinimf 
Start Discussion