🎯 SQL Interview Questions (Data Engineering Focus)

This section contains the most commonly asked SQL patterns in data engineering interviews.

Focus here is not syntax — but thinking patterns.

---

🧠 Mental Model

Most SQL interview questions test:

• Data relationships
• Edge cases
• Aggregation logic
• Ranking logic
• Deduplication patterns

👉 Think: “How does data transform step by step?”

---

📌 1. Top N per Group (VERY COMMON)

Find top 3 highest salaries per department.

SELECT *
FROM ( SELECT name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees ) t
WHERE rn <= 3;

---

📌 2. Second Highest Salary

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

---

📌 3. Remove Duplicates (Keep Latest)

SELECT *
FROM ( SELECT *,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
FROM users ) t
WHERE rn = 1;

---

📌 4. Find Employees with No Orders

SELECT u.name
FROM users u
LEFT JOIN orders o
ON u.user_id = o.user_id
WHERE o.user_id IS NULL;

---

📌 5. Running Total

SELECT date, amount,
SUM(amount) OVER (ORDER BY date) AS running_total
FROM sales;

---

📌 6. Find Duplicate Records

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

---

📌 7. Customers with More Than 3 Orders

SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 3;

---

📌 8. Rank Without Gaps

SELECT name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees;

---

📌 9. Difference Between Two Dates

SELECT user_id,
DATEDIFF(day, MIN(login_date), MAX(login_date)) AS active_days
FROM user_logins
GROUP BY user_id;

---

📌 10. Find First Order per User

SELECT *
FROM ( SELECT *,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date ASC) AS rn
FROM orders ) t
WHERE rn = 1;

---

🧠 COMMON PATTERNS IN INTERVIEWS

Most questions fall into these categories:

1. Ranking

• ROW_NUMBER
• RANK
• DENSE_RANK

---

2. Aggregation

• GROUP BY
• HAVING

---

3. Joins

• LEFT JOIN for missing data detection
• INNER JOIN for matching data

---

4. Deduplication

• ROW_NUMBER + PARTITION BY

---

⚠️ COMMON MISTAKES

• Using GROUP BY incorrectly
• Forgetting PARTITION BY in window functions
• Using WHERE instead of HAVING
• Not handling NULLs in joins
• Writing subqueries when window functions are simpler

---

🧠 INTERVIEW STRATEGY

When solving SQL problems:

1. Identify entities (tables)
2. Identify relationships (joins)
3. Identify transformation (aggregation/window)
4. Identify final output shape

---

🚀 SUMMARY

• SQL interviews test logic, not syntax
• Window functions are key
• Deduplication is very common
• Joins + grouping = most questions

---

🔥 FINAL TIP

If you can master:

• Joins
• Window functions
• GROUP BY

👉 You can solve 90% of SQL interview questions

---