SQL Interview Preparation
The top 35 SQL interview questions with real query solutions and explanations.
SQL interviews test a fairly consistent set of patterns. Interviewers aren’t trying to trick you — they want to see that you can model a problem in relational terms, choose the right tool (JOIN vs subquery vs window function), and handle edge cases like NULLs and ties. The 35 questions below cover the patterns that come up most often in data engineering, backend, and analyst interviews. Each answer uses a realistic schema so the queries run directly in PostgreSQL.
Reference Schema
Understanding the schema before writing any query is good interview habit — state what tables you’re working with and the relationships between them before you start typing.
CREATE TABLE departments (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
salary NUMERIC(10,2),
department_id INT REFERENCES departments(id),
manager_id INT REFERENCES employees(id),
hire_date DATE
);
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INT REFERENCES customers(id),
product_id INT,
total NUMERIC(10,2),
created_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE logins (
user_id INT,
login_date DATE
);
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT,
category_id INT,
price NUMERIC(10,2)
);
CREATE TABLE sales (
id SERIAL PRIMARY KEY,
product_id INT REFERENCES products(id),
quantity INT,
sale_date DATE
);1. Find Duplicate Rows
Find all names in employees that appear more than once. The key insight: GROUP BY the columns that define uniqueness, then use HAVING to filter for groups with more than one row.
SELECT name, COUNT(*) AS occurrences
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;2. Find the Second (Nth) Highest Salary
Return the second-highest distinct salary. The DENSE_RANK approach handles ties correctly and generalizes to any N.
-- LIMIT/OFFSET approach: simple but only works for small N
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
OFFSET 1 LIMIT 1;
-- DENSE_RANK approach: handles ties correctly, works for any N
SELECT salary
FROM (
SELECT salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) ranked
WHERE rnk = 2
LIMIT 1;3. Employees Earning More Than Their Manager
A self-join is the natural tool here — join the table to itself with different aliases to compare a row against a related row in the same table.
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;4. Find All Departments With No Employees
The LEFT JOIN + IS NULL pattern efficiently finds rows with no matching row in another table — more readable than NOT IN and safer when NULLs are involved.
SELECT d.id, d.name
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
WHERE e.id IS NULL;5. Running Total (Cumulative Sum)
Running totals require a window function with ORDER BY — GROUP BY collapses rows, but window functions keep all rows visible while adding a cumulative value.
SELECT name,
department_id,
salary,
SUM(salary) OVER (
PARTITION BY department_id
ORDER BY hire_date
) AS running_total
FROM employees;6. Find Customers Who Never Placed an Order
Two equivalent approaches — use NOT EXISTS for large tables since it short-circuits at the first match.
-- LEFT JOIN approach — readable
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;
-- NOT EXISTS — often faster on large tables
SELECT id, name
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);7. Delete Duplicate Rows Keeping One
Keep the row with the lowest id per unique group and delete all others. The subquery identifies the canonical row to keep.
DELETE FROM employees
WHERE id NOT IN (
SELECT MIN(id)
FROM employees
GROUP BY name, department_id
);8. Count NULL Values in Each Column
The FILTER clause makes conditional aggregation clean and readable — one pass over the table produces all the counts.
SELECT
COUNT(*) FILTER (WHERE name IS NULL) AS null_name,
COUNT(*) FILTER (WHERE salary IS NULL) AS null_salary,
COUNT(*) FILTER (WHERE department_id IS NULL) AS null_dept,
COUNT(*) FILTER (WHERE hire_date IS NULL) AS null_hire_date
FROM employees;9. Most Recent Record Per User/Group
Two approaches: PostgreSQL’s DISTINCT ON is concise and efficient; the window function version is portable across databases.
-- DISTINCT ON (PostgreSQL-specific): one row per customer_id, ordered by created_at DESC
SELECT DISTINCT ON (customer_id)
id, customer_id, total, created_at
FROM orders
ORDER BY customer_id, created_at DESC;
-- Window function alternative: portable across databases
SELECT id, customer_id, total, created_at
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
FROM orders
) t
WHERE rn = 1;10. Top 3 Earners Per Department
The top-N per group pattern: rank within the partition, filter in an outer query. Use DENSE_RANK so tied salaries share the same rank and no position is skipped.
SELECT name, department_id, salary
FROM (
SELECT name,
department_id,
salary,
DENSE_RANK() OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS rnk
FROM employees
) ranked
WHERE rnk <= 3;11. Month-Over-Month Revenue Growth (LAG)
LAG accesses the previous row’s value without a self-join. NULLIF guards against division-by-zero when the previous month had zero revenue.
WITH monthly AS (
SELECT DATE_TRUNC('month', created_at) AS month,
SUM(total) AS revenue
FROM orders
GROUP BY 1
)
SELECT month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
ROUND(
100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
2
) AS pct_change
FROM monthly
ORDER BY month;12. Find Consecutive Login Days
The key insight: subtracting the row number from a date produces the same constant for consecutive dates, which groups them together.
WITH ranked AS (
SELECT user_id,
login_date,
login_date - (ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY login_date
) * INTERVAL '1 day') AS grp
FROM (SELECT DISTINCT user_id, login_date FROM logins) d
),
streaks AS (
SELECT user_id, grp, COUNT(*) AS streak_len
FROM ranked
GROUP BY user_id, grp
)
SELECT DISTINCT user_id
FROM streaks
WHERE streak_len >= 3;13. Pivot Data Without CROSSTAB (Conditional Aggregation)
The FILTER clause replaces multiple subqueries or CASE WHEN expressions, producing one column per category in a single pass.
SELECT
EXTRACT(YEAR FROM s.sale_date)::INT AS yr,
SUM(s.quantity) FILTER (WHERE p.category_id = 1) AS cat_1,
SUM(s.quantity) FILTER (WHERE p.category_id = 2) AS cat_2,
SUM(s.quantity) FILTER (WHERE p.category_id = 3) AS cat_3
FROM sales s
JOIN products p ON p.id = s.product_id
GROUP BY yr
ORDER BY yr;14. Find the Median Salary
PERCENTILE_CONT(0.5) is the cleanest way to compute a median — no sorting or row-numbering required.
-- Built-in ordered-set aggregate
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees;
-- PERCENTILE_CONT interpolates; PERCENTILE_DISC returns the nearest actual value15. Percentage of Total Within a Group
A window function computes the department total alongside each row, making the per-employee percentage straightforward.
SELECT name,
department_id,
salary,
ROUND(
100.0 * salary / SUM(salary) OVER (PARTITION BY department_id),
2
) AS pct_of_dept
FROM employees;16. Customers Active in Consecutive Months
Self-join the monthly activity table on the condition that one month is exactly one month after the other.
WITH monthly AS (
SELECT DISTINCT
customer_id,
DATE_TRUNC('month', created_at)::DATE AS order_month
FROM orders
)
SELECT DISTINCT a.customer_id
FROM monthly a
JOIN monthly b
ON a.customer_id = b.customer_id
AND b.order_month = a.order_month + INTERVAL '1 month';17. Find Gaps in a Sequence of IDs
LEAD gives you the next id in sequence. A gap exists wherever the difference between consecutive ids is greater than 1.
SELECT id + 1 AS gap_start,
next_id - 1 AS gap_end
FROM (
SELECT id,
LEAD(id) OVER (ORDER BY id) AS next_id
FROM orders
) t
WHERE next_id - id > 1;18. Self Join to Find Manager-Employee Pairs
SELECT m.name AS manager,
e.name AS direct_report,
e.salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
ORDER BY m.name, e.name;19. String Concatenation and Splitting
-- Build a display label with string concatenation
SELECT id,
name || ' (Dept ' || COALESCE(department_id::TEXT, 'N/A') || ')' AS label
FROM employees;
-- Split a comma-separated string into individual rows with UNNEST + STRING_TO_ARRAY
SELECT id, TRIM(tag) AS tag
FROM employees,
UNNEST(STRING_TO_ARRAY('engineering,backend,senior', ',')) AS tag;20. Date Arithmetic (Age, Days Between, First/Last Day of Month)
SELECT name,
hire_date,
DATE_PART('year', AGE(hire_date))::INT AS years_employed,
CURRENT_DATE - hire_date AS days_employed,
DATE_TRUNC('month', hire_date)::DATE AS first_day_of_month,
(DATE_TRUNC('month', hire_date)
+ INTERVAL '1 month - 1 day')::DATE AS last_day_of_month
FROM employees;21. CASE WHEN for Bucketing / Categorization
CASE WHEN is SQL’s if/else. It’s available anywhere an expression is valid — SELECT, WHERE, ORDER BY, and inside aggregate functions.
SELECT name,
salary,
CASE
WHEN salary < 50000 THEN 'Junior'
WHEN salary BETWEEN 50000 AND 99999 THEN 'Mid-level'
WHEN salary >= 100000 THEN 'Senior'
ELSE 'Unknown'
END AS salary_band
FROM employees;22. Difference Between UNION and UNION ALL
UNION removes duplicates (requires a dedup pass — slower). UNION ALL keeps all rows. Default to UNION ALL and wrap with SELECT DISTINCT only when deduplication is genuinely required.
-- UNION removes duplicates (slower)
SELECT customer_id FROM orders WHERE total > 500
UNION
SELECT customer_id FROM orders WHERE created_at > NOW() - INTERVAL '7 days';
-- UNION ALL keeps all rows including duplicates (faster — no dedup pass)
SELECT customer_id FROM orders WHERE total > 500
UNION ALL
SELECT customer_id FROM orders WHERE created_at > NOW() - INTERVAL '7 days';23. Employees Hired in the Last 30 Days
SELECT id, name, hire_date
FROM employees
WHERE hire_date >= CURRENT_DATE - INTERVAL '30 days';24. Recursive CTE for Org Chart Traversal
Recursive CTEs solve hierarchical queries that flat JOINs can’t — the depth of the hierarchy doesn’t need to be known in advance.
WITH RECURSIVE org AS (
-- Anchor: the root manager
SELECT id, name, manager_id, 0 AS depth
FROM employees
WHERE id = 1
UNION ALL
-- Recursive step: add direct reports of each found node
SELECT e.id, e.name, e.manager_id, org.depth + 1
FROM employees e
JOIN org ON e.manager_id = org.id
)
SELECT id, name, depth
FROM org
ORDER BY depth, name;25. Moving Average Over Last N Rows
The frame clause ROWS BETWEEN 2 PRECEDING AND CURRENT ROW gives exactly 3 rows (2 prior + current).
SELECT created_at::DATE AS day,
SUM(total) AS daily_revenue,
AVG(SUM(total)) OVER (
ORDER BY created_at::DATE
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3d
FROM orders
GROUP BY created_at::DATE
ORDER BY day;26. Rank Products by Sales Within Category
SELECT p.name,
p.category_id,
SUM(s.quantity) AS total_units,
RANK() OVER (
PARTITION BY p.category_id
ORDER BY SUM(s.quantity) DESC
) AS sales_rank
FROM products p
JOIN sales s ON s.product_id = p.id
GROUP BY p.id, p.name, p.category_id
ORDER BY p.category_id, sales_rank;27. Orders Placed on Weekends
EXTRACT(DOW ...) returns the day of week as an integer: 0 = Sunday, 6 = Saturday.
SELECT id, customer_id, total, created_at
FROM orders
WHERE EXTRACT(DOW FROM created_at) IN (0, 6);28. Compare Two Tables for Differences (EXCEPT)
EXCEPT returns rows present in the first query but not the second — useful for finding rows that should match between tables but don’t.
-- Rows present in the backup but missing from the live table
SELECT id, customer_id, total FROM orders_backup
EXCEPT
SELECT id, customer_id, total FROM orders;29. Transpose Rows to Columns Using crosstab
crosstab requires the tablefunc extension which ships with PostgreSQL. For dynamic column lists, generate the query string at runtime.
CREATE EXTENSION IF NOT EXISTS tablefunc;
SELECT *
FROM CROSSTAB(
$$
SELECT p.category_id::TEXT,
p.name,
SUM(s.quantity)::INT
FROM products p
JOIN sales s ON s.product_id = p.id
GROUP BY p.category_id, p.name
ORDER BY 1, 2
$$,
$$ VALUES ('Widget'), ('Gadget'), ('Doohickey') $$
) AS ct (
category_id TEXT,
"Widget" INT,
"Gadget" INT,
"Doohickey" INT
);30. Users Who Bought Product A but Not Product B
EXCEPT is the cleanest way to express set subtraction — customers in set A minus customers also in set B.
SELECT DISTINCT customer_id
FROM orders
WHERE product_id = 1 -- product A
EXCEPT
SELECT DISTINCT customer_id
FROM orders
WHERE product_id = 2; -- product B31. Calculate Retention Rate
Month-1 retention: the share of month-0 customers who also ordered in month 1. Cohort analysis is a common analyst interview topic.
WITH cohort AS (
SELECT customer_id,
DATE_TRUNC('month', MIN(created_at)) AS cohort_month
FROM orders
GROUP BY customer_id
),
activity AS (
SELECT DISTINCT
customer_id,
DATE_TRUNC('month', created_at) AS active_month
FROM orders
)
SELECT c.cohort_month,
COUNT(DISTINCT c.customer_id) AS cohort_size,
COUNT(DISTINCT a.customer_id) AS retained,
ROUND(
100.0 * COUNT(DISTINCT a.customer_id) / COUNT(DISTINCT c.customer_id), 2
) AS retention_pct
FROM cohort c
LEFT JOIN activity a
ON a.customer_id = c.customer_id
AND a.active_month = c.cohort_month + INTERVAL '1 month'
GROUP BY c.cohort_month
ORDER BY c.cohort_month;32. Find the Mode (Most Common Value)
-- Built-in ordered-set aggregate
SELECT MODE() WITHIN GROUP (ORDER BY salary) AS modal_salary
FROM employees;
-- Manual approach — useful when you need to see all tied modes
SELECT salary, COUNT(*) AS freq
FROM employees
GROUP BY salary
ORDER BY freq DESC
LIMIT 1;33. Generate a Date Series
GENERATE_SERIES with a LEFT JOIN ensures every date appears in the result even when no orders exist for that day.
SELECT d::DATE AS day,
COALESCE(SUM(o.total), 0) AS revenue
FROM GENERATE_SERIES(
'2024-01-01'::DATE,
'2024-12-31'::DATE,
'1 day'::INTERVAL
) AS d
LEFT JOIN orders o ON o.created_at::DATE = d::DATE
GROUP BY d
ORDER BY d;34. Detect Data Skew Across Partitions
A skew_ratio well above 1.0 for certain keys means those partitions will be hotspots — important to know before choosing a partition key.
SELECT customer_id,
COUNT(*) AS row_count,
ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS pct_of_total,
ROUND(COUNT(*) * 1.0 / AVG(COUNT(*)) OVER (), 2) AS skew_ratio
FROM orders
GROUP BY customer_id
ORDER BY row_count DESC;35. Optimizing a Slow GROUP BY Query
Push filters into WHERE instead of HAVING whenever they don’t depend on aggregate results — HAVING runs after aggregation, so moving the filter to WHERE reduces the number of rows that need to be aggregated.
-- Slow: HAVING filters AFTER aggregating all rows
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING department_id IN (1, 2, 3);
-- Fast: push the filter to WHERE so fewer rows are aggregated
SELECT department_id, AVG(salary)
FROM employees
WHERE department_id IN (1, 2, 3) -- filter first, then aggregate
GROUP BY department_id;
-- Add a covering index so the planner can avoid a sequential scan
CREATE INDEX idx_emp_dept_salary ON employees (department_id, salary);
-- Verify the improvement with EXPLAIN
EXPLAIN (ANALYZE, BUFFERS)
SELECT department_id, AVG(salary)
FROM employees
WHERE department_id IN (1, 2, 3)
GROUP BY department_id;Key Patterns to Remember
- Top-N per group —
ROW_NUMBER()orDENSE_RANK()withPARTITION BY, then filter in an outer query. - Consecutive sequences — subtract
ROW_NUMBER()from the date or integer value; consecutive values produce the same group key. - Missing rows —
LEFT JOIN ... WHERE right.id IS NULLorNOT EXISTS. - NULL safety — use
IS NULL/IS NOT NULL,COALESCE, andNULLIF; aggregates ignore NULLs automatically. - Running totals —
SUM(...) OVER (ORDER BY ...), addPARTITION BYto reset per group. - Date math —
INTERVAL,DATE_TRUNC,EXTRACT,AGE, andGENERATE_SERIEScover most cases. - Self joins — always add
a.id < b.id(or<>) to avoid duplicate pairs and self-matches.