SQL is a must-have skill for Data Engineers, Backend Developers, and Data Analysts. Here are the key queries asked in interviews.
Basic Queries
1. Find Duplicates
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;2. Second Highest Value
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);Or with a window function:
SELECT salary FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rk
FROM employees
) t WHERE rk = 2;3. Nth Record
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn
FROM employees
) t WHERE rn = 5;JOIN Tasks
4. Employees Without a Department
SELECT e.name FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WHERE d.id IS NULL;5. Departments Without Employees
SELECT d.name FROM departments d
LEFT JOIN employees e ON d.id = e.dept_id
WHERE e.id IS NULL;Aggregation
6. Top 3 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;7. Running Total
SELECT date, amount,
SUM(amount) OVER (ORDER BY date) AS running_total
FROM orders;Date Operations
8. Users Active 3 Consecutive Days
SELECT DISTINCT user_id 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
HAVING COUNT(*) >= 3;Interview Tips
- Always clarify: are there NULLs, are values unique, which SQL dialect
- Start with a simple solution, then optimize
- Think out loud — interviewers care about your thought process
- Know the difference between WHERE and HAVING, RANK and DENSE_RANK