Interview Prep Guide
SQL Interview Questions and Answers for Freshers to Experienced Candidates
Prepare for SQL interviews with core query questions, joins, aggregation, indexes, window functions, and practical query rounds.
Basic SQL Interview Questions
What is the difference between WHERE and HAVING?
WHERE filters rows before grouping, while HAVING filters grouped results after aggregation.
What are the main types of joins?
The most common joins are INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN. They differ in how unmatched rows are handled.
What is the difference between DELETE, TRUNCATE, and DROP?
DELETE removes selected rows, TRUNCATE clears table data more directly, and DROP removes the table structure itself.
What is the difference between a Primary Key and a Unique Key?
A Primary Key uniquely identifies a row and forbids NULL values, while a Unique Key also ensures uniqueness but can allow one (or more, depending on the DB) NULL value.
What is a Foreign Key and why is it used?
A Foreign Key is a column that links two tables together by referencing the Primary Key of another table, ensuring referential integrity.
Medium SQL Interview Questions
How does GROUP BY work with aggregate functions?
GROUP BY creates groups of rows, and aggregate functions such as COUNT, SUM, AVG, MAX, or MIN are then calculated per group.
What are window functions and why are they useful?
Window functions calculate values across a related set of rows without collapsing them into one grouped row, which makes ranking, running totals, and partition-based comparisons easier.
What is the difference between a subquery and a CTE?
Both help break complex queries into steps, but a CTE gives a named intermediate result that often improves readability and can sometimes be reused more clearly in the main query.
What are ACID properties in a database?
ACID stands for Atomicity, Consistency, Isolation, and Durability, which are the key properties ensuring reliable database transactions.
What is the difference between an Inner Join and a Cross Join?
An Inner Join returns matched rows based on a condition, while a Cross Join returns the Cartesian product (every combination of rows) of both tables.
Advanced SQL Interview Questions
What is an index and when does it help?
An index is a data structure that helps the database find rows faster, especially for selective filters, joins, and ordered lookups.
How would you debug a slow SQL query?
Start by understanding the query plan, checking filters and joins, reviewing indexes, validating row counts, and rewriting inefficient logic where needed.
How would you choose columns for a composite index?
Choose columns based on real filter, join, and sort patterns, usually starting with the most consistently useful leading conditions rather than guessing.
What is Database Normalization and why is it used?
Normalization is the process of organizing data to reduce redundancy and improve data integrity, usually up to the Third Normal Form (3NF).
What is a View in SQL and when should you use one?
A View is a virtual table based on the result set of an SQL statement. It does not store data itself but provides a reusable way to query complex logic.
Scenario-Based SQL Interview Questions
How would you investigate a report query that became slow after the dataset grew from thousands of rows to millions?
Start by understanding the query plan, filters, joins, sorting, and indexing strategy before changing SQL syntax blindly.
SQL Coding Round
Find the top customer per region by revenue
This type of problem usually requires aggregation first, then a ranking step with a window function, and finally filtering to rank one. Strong candidates explain the shape of intermediate results instead of writing one messy query immediately.
Find the second highest salary in an employees table
Common ways include using OFFSET 1 LIMIT 1 after sorting, or a subquery with MAX() . The most robust way is using DENSE_RANK() to handle cases where multiple people share the top salary. Interviewers watch for how you handle the "no second salary" edge case.
Write a reporting query with joins, grouping, and a clear way to explain performance trade-offs
Interviewers usually care about correctness first and performance second. Strong candidates explain why the join order, grouping, and filter placement produce the expected business result before talking about optimization.
Calculate a three-row moving average within each department
Given employees(employee_id, department_id, hire_date, salary) , preserve every employee and average the current salary with the previous two hires in the same department. SELECT employee_id, department_id, hire_date, salary, AVG(salary) OVER ( PARTITION BY department_id ORDER BY hire_date, employee_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_avg_salary FROM employees; The explicit ROWS frame and the employee ID tie-breaker make the result predictable. At the start of a department, SQL averages only the rows available in the frame.
Calculate each customer’s running order total
Given orders(order_id, customer_id, order_date, amount) , show every order with the amount spent by that customer up to that order. SELECT order_id, customer_id, order_date, amount, SUM(amount) OVER ( PARTITION BY customer_id ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total FROM orders; Use a unique tie-breaker in the window ordering; otherwise multiple orders at the same timestamp can make the running sequence nondeterministic.
Return the top three products by total revenue
Given orders(order_id, product_id, quantity) and products(product_id, product_name, price) , calculate revenue before ranking products. WITH product_revenue AS ( SELECT p.product_id, p.product_name, SUM(o.quantity * p.price) AS total_revenue FROM orders o JOIN products p ON p.product_id = o.product_id GROUP BY p.product_id, p.product_name ), ranked AS ( SELECT *, DENSE_RANK() OVER (ORDER BY total_revenue DESC) AS revenue_rank FROM product_revenue ) SELECT product_id, product_name, total_revenue FROM ranked WHERE revenue_rank <= 3 ORDER BY total_revenue DESC, product_id; DENSE_RANK includes tied products. Use ROW_NUMBER instead when the requirement is exactly three rows.
Find students scoring above their department average
Given students(student_id, student_name, department_id, marks) , compare each student with peers without collapsing the rows. WITH scored AS ( SELECT student_id, student_name, department_id, marks, AVG(marks) OVER (PARTITION BY department_id) AS department_avg FROM students WHERE marks IS NOT NULL ) SELECT student_id, student_name, department_id, marks, department_avg FROM scored WHERE marks > department_avg; Filtering NULL marks before computing the window makes the population explicit and avoids presenting students with missing scores as comparable observations.
Calculate month-over-month revenue change and growth percentage
Aggregate transactions to month level first, then compare adjacent available months. WITH monthly AS ( SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue FROM orders GROUP BY DATE_TRUNC('month', order_date) ), compared AS ( SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS previous_revenue FROM monthly ) SELECT month, revenue, previous_revenue, revenue - previous_revenue AS revenue_change, 100.0 * (revenue - previous_revenue) / NULLIF(previous_revenue, 0) AS growth_pct FROM compared ORDER BY month; Call out that LAG compares available rows, not necessarily consecutive calendar months. A calendar table is required when missing months must appear.
Keep only the latest version of each business record
Given customer_events(event_id, customer_id, event_type, updated_at, payload) , retain one latest row per customer and event type. WITH ranked AS ( SELECT e.*, ROW_NUMBER() OVER ( PARTITION BY customer_id, event_type ORDER BY updated_at DESC, event_id DESC ) AS rn FROM customer_events e ) SELECT event_id, customer_id, event_type, updated_at, payload FROM ranked WHERE rn = 1; The secondary order on a unique ID prevents unstable results when two versions share the same timestamp.
Find departments with more than five active employees
Filter individual inactive employees before grouping, then filter the grouped counts with HAVING . SELECT department_id, COUNT(*) AS active_employee_count FROM employees WHERE employment_status = 'active' GROUP BY department_id HAVING COUNT(*) > 5 ORDER BY active_employee_count DESC; WHERE defines which rows participate; HAVING applies the threshold after aggregation.
Find the highest-spending customer in a calendar year
Use a date range instead of applying a year function to every indexed timestamp, and use ranking when ties should be retained. WITH spending AS ( SELECT customer_id, SUM(amount) AS total_spent FROM orders WHERE order_date >= DATE '2025-01-01' AND order_date < DATE '2026-01-01' GROUP BY customer_id ), ranked AS ( SELECT *, DENSE_RANK() OVER (ORDER BY total_spent DESC) AS spend_rank FROM spending ) SELECT customer_id, total_spent FROM ranked WHERE spend_rank = 1; The half-open date range remains safe for timestamps and is generally friendlier to an index on order_date .
Find customers with purchases in three consecutive months
Reduce purchases to one row per customer and month, then subtract a row number from each month index so consecutive months share an island key. WITH customer_months AS ( SELECT DISTINCT customer_id, DATE_TRUNC('month', order_date) AS month FROM orders ), numbered AS ( SELECT customer_id, month, (EXTRACT(YEAR FROM month) * 12 + EXTRACT(MONTH FROM month)) - ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY month) AS island_id FROM customer_months ) SELECT customer_id, MIN(month) AS streak_start, MAX(month) AS streak_end, COUNT(*) AS consecutive_months FROM numbered GROUP BY customer_id, island_id HAVING COUNT(*) >= 3; The initial DISTINCT prevents several purchases in one month from inflating the streak length.
Synchronize a target table from incremental source changes
Stage a bounded batch with one latest change per key, then merge it transactionally. Syntax varies by warehouse; this ANSI-style outline shows the decision flow. MERGE INTO target_customer AS t USING staged_customer_changes AS s ON t.customer_id = s.customer_id WHEN MATCHED AND s.operation = 'D' THEN DELETE WHEN MATCHED THEN UPDATE SET name = s.name, email = s.email, updated_at = s.updated_at WHEN NOT MATCHED AND s.operation <> 'D' THEN INSERT (customer_id, name, email, updated_at) VALUES (s.customer_id, s.name, s.email, s.updated_at); Advance the source watermark only after the merge commits. The staging step must deterministically resolve multiple events for the same key.
Build a rolling three-month active-user metric
Many engines do not support COUNT(DISTINCT user_id) inside an ordered window. Generate each activity month into its reporting window, then count distinct users at the final grain. WITH activity_months AS ( SELECT DISTINCT user_id, product_id, DATE_TRUNC('month', activity_date) AS activity_month FROM user_activity ), expanded AS ( SELECT user_id, product_id, activity_month AS report_month FROM activity_months UNION ALL SELECT user_id, product_id, activity_month + INTERVAL '1 month' FROM activity_months UNION ALL SELECT user_id, product_id, activity_month + INTERVAL '2 months' FROM activity_months ) SELECT report_month, product_id, COUNT(DISTINCT user_id) AS rolling_3m_users FROM expanded GROUP BY report_month, product_id ORDER BY report_month, product_id; For production-scale warehouses, use a date spine or engine-specific array/date expansion and constrain the requested reporting range.
Implement a Type 2 slowly changing customer dimension
For changed attributes, expire the current dimension row and insert a new current version in one transaction. UPDATE dim_customer d SET valid_to = :batch_time, is_current = FALSE FROM staged_customer s WHERE d.customer_id = s.customer_id AND d.is_current = TRUE AND (d.name, d.segment) IS DISTINCT FROM (s.name, s.segment); INSERT INTO dim_customer (customer_id, name, segment, valid_from, valid_to, is_current) SELECT s.customer_id, s.name, s.segment, :batch_time, NULL, TRUE FROM staged_customer s LEFT JOIN dim_customer d ON d.customer_id = s.customer_id AND d.is_current = TRUE WHERE d.customer_id IS NULL OR (d.name, d.segment) IS DISTINCT FROM (s.name, s.segment); Real implementations should enforce at most one current row per business key and make the batch identifier retry-safe.