Interview Prep
Interview Questions on SQL Query — JOINs, Subqueries, Window Functions, and Real Problems
SQL query writing is tested in every data analyst, backend developer, and QA interview. Interviewers do not ask you to define SQL — they give you a table and ask you to write the query. Here are the actual SQL problems they give, with solutions.
SQL query writing is a hands-on skill. Interviewers give you a problem and expect a working query — not a textbook definition.
SQL Query Interviews — What to Expect
SQL query interviews are practical. You are given a table schema and asked to write queries that solve specific business problems. This is different from DBMS theory interviews — here, the interviewer wants working SQL, not definitions.
The difficulty depends on the role: freshers get basic SELECT, WHERE, and GROUP BY. Data analysts get JOINs, subqueries, and window functions. Backend developers get query optimization and complex aggregations. But the format is always the same — here is a table, write the query.
This guide covers the actual SQL query problems asked in Indian interviews — from basic to advanced, with multiple solution approaches for each.
SQL interviews are open-book in spirit — they do not care if you memorize syntax. They care if you can solve the problem. Think in sets, not loops.
Basic SQL Queries
Q1: Find employees who earn more than the average salary.
-- Table: employees (id, name, salary, dept_id) SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); -- With department context: SELECT e.name, e.salary, d.dept_name FROM employees e JOIN departments d ON e.dept_id = d.id WHERE e.salary > (SELECT AVG(salary) FROM employees);
Q2: Find duplicate records in a table.
-- Find duplicate emails SELECT email, COUNT(*) as count FROM users GROUP BY email HAVING COUNT(*) > 1; -- Find and show all duplicate rows with details SELECT * FROM users WHERE email IN ( SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1 ); -- Delete duplicates (keep one): DELETE FROM users WHERE id NOT IN ( SELECT MIN(id) FROM users GROUP BY email );
Q3: Find departments with no employees.
-- Method 1: LEFT JOIN + NULL check SELECT d.dept_name FROM departments d LEFT JOIN employees e ON d.id = e.dept_id WHERE e.id IS NULL; -- Method 2: NOT IN SELECT dept_name FROM departments WHERE id NOT IN ( SELECT DISTINCT dept_id FROM employees WHERE dept_id IS NOT NULL ); -- Method 3: NOT EXISTS (often fastest) SELECT dept_name FROM departments d WHERE NOT EXISTS ( SELECT 1 FROM employees e WHERE e.dept_id = d.id );
Intermediate SQL Queries
Q4: Find the Nth highest salary (generalized).
-- Using DENSE_RANK (handles ties correctly)
SELECT salary FROM (
SELECT salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees
) ranked
WHERE rank = N; -- replace N with desired position
-- For 3rd highest: WHERE rank = 3
-- For top 3 salaries: WHERE rank <= 3
-- Using LIMIT/OFFSET (MySQL):
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET N-1; -- for Nth highest
-- Using correlated subquery:
SELECT DISTINCT salary
FROM employees e1
WHERE N-1 = (
SELECT COUNT(DISTINCT salary)
FROM employees e2
WHERE e2.salary > e1.salary
);Q5: Find employees who earn more than their manager.
-- Table: employees (id, name, salary, manager_id)
-- Self JOIN approach:
SELECT e.name AS employee, e.salary AS emp_salary,
m.name AS manager, m.salary AS mgr_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;
-- This is a classic self-join question.
-- The key insight: join the table with itself
-- using the manager_id relationship.Q6: Find consecutive login days for each user.
-- Table: logins (user_id, login_date)
-- Find users who logged in 3+ consecutive days
WITH numbered AS (
SELECT user_id, login_date,
login_date - INTERVAL ROW_NUMBER()
OVER (PARTITION BY user_id ORDER BY login_date) DAY
AS grp
FROM (SELECT DISTINCT user_id, login_date FROM logins) t
),
streaks AS (
SELECT user_id, MIN(login_date) AS start_date,
MAX(login_date) AS end_date, COUNT(*) AS streak
FROM numbered
GROUP BY user_id, grp
)
SELECT * FROM streaks WHERE streak >= 3;
-- This is an advanced pattern called "gaps and islands"
-- Common in data analyst interviews at product companies.
SQL query interviews test problem-solving, not memorization. Practice writing queries for real business scenarios.
Window Functions
Window functions are the dividing line between junior and senior SQL skills. If you know ROW_NUMBER, RANK, DENSE_RANK, LAG, and LEAD — you can solve 90% of advanced SQL interview questions.
Q7: ROW_NUMBER vs RANK vs DENSE_RANK
-- Data: salaries = 100, 100, 90, 80
ROW_NUMBER: 1, 2, 3, 4 (always unique, no ties)
RANK: 1, 1, 3, 4 (ties get same rank, skips next)
DENSE_RANK: 1, 1, 2, 3 (ties get same rank, no skip)
-- Example: rank employees by salary within department
SELECT name, dept_id, salary,
ROW_NUMBER() OVER (PARTITION BY dept_id
ORDER BY salary DESC) AS row_num,
RANK() OVER (PARTITION BY dept_id
ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER (PARTITION BY dept_id
ORDER BY salary DESC) AS dense_rank
FROM employees;
-- When to use which:
-- ROW_NUMBER: pagination, deduplication
-- RANK: competition ranking (1st, 1st, 3rd)
-- DENSE_RANK: Nth highest salary (no gaps)Q8: Calculate running total and month-over-month growth.
-- Running total:
SELECT order_date, amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;
-- Month-over-month growth:
SELECT month, revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_month,
ROUND(
(revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0
/ LAG(revenue) OVER (ORDER BY month), 2
) AS growth_pct
FROM monthly_revenue;
-- LAG(column, N) = value N rows BEFORE current row
-- LEAD(column, N) = value N rows AFTER current rowHow to Prepare for SQL Query Interviews
Step 1: Master the Basics (Week 1)
SELECT, WHERE, GROUP BY, HAVING, ORDER BY, JOINs (all types). Practice on LeetCode SQL or HackerRank SQL. Solve 5 problems daily.
Step 2: Subqueries and CTEs (Week 2)
Correlated subqueries, EXISTS/NOT EXISTS, WITH clause (CTEs). Practice the “second highest salary” problem using all 3 methods until you can write them from memory.
Step 3: Window Functions (Week 3)
ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM OVER, running totals. These separate junior from senior SQL skills. Practice ranking and gap-detection problems.
Step 4: Real Business Problems (Week 4)
Practice problems like: find customers who ordered every month, calculate retention rate, find consecutive events, pivot data. These are the actual problems asked at product companies.