SQL Interview Questions for Data Analysts
SQL is the non-negotiable hiring filter for Data Analysts globally. Technical interviewers evaluate whether you can write clean, performant queries on real transactional databases. Review these core questions with query examples, underlying logic, and common pitfalls.
What is the fundamental difference between WHERE and HAVING in SQL?
WHERE filters individual row records before any grouping or aggregation occurs. HAVING filters grouped rows after the GROUP BY clause has calculated summary values (such as SUM, COUNT, or AVG).
-- WHERE filters uncancelled orders; HAVING filters customers with >3 orders
SELECT customer_id, COUNT(order_id) AS total_orders, SUM(amount) AS total_spend
FROM orders
WHERE status != 'cancelled'
GROUP BY customer_id
HAVING COUNT(order_id) > 3;What is the operational difference between INNER JOIN and LEFT JOIN, and how are NULLs handled?
An INNER JOIN returns only records that have matching keys in both tables. A LEFT JOIN returns all rows from the left table regardless of matches, filling columns from the right table with NULL when no match exists.
-- LEFT JOIN ensures customers with zero purchases are still returned
SELECT c.customer_id, c.customer_name, o.order_id, COALESCE(o.amount, 0) AS amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;Explain the difference between ROW_NUMBER(), RANK(), and DENSE_RANK().
ROW_NUMBER assigns unique sequential integers (1, 2, 3...) regardless of tied values. RANK assigns identical rank numbers to ties and skips subsequent numbers (e.g., 1, 2, 2, 4). DENSE_RANK assigns identical numbers to ties without skipping subsequent ranks (e.g., 1, 2, 2, 3).
SELECT employee_id, department_id, salary,
ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY salary DESC) AS row_num,
RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS dense_rnk
FROM employees;How do you write a query to find the N-th highest salary (e.g., 2nd highest) in an organization?
The modern, standard ANSI SQL solution uses a Common Table Expression (CTE) with DENSE_RANK() partitioned across the company or department, followed by filtering where dense_rank = N.
WITH ranked_salaries AS (
SELECT employee_id, salary, department_id,
DENSE_RANK() OVER(ORDER BY salary DESC) AS rank_pos
FROM employees
)
SELECT employee_id, salary
FROM ranked_salaries
WHERE rank_pos = 2;What is a Common Table Expression (CTE) and why is it preferred over nested subqueries?
A Common Table Expression (WITH clause) defines a temporary, named result set that exists only for the duration of the query. CTEs break complex, nested multi-level subqueries into readable, step-by-step modular blocks that are significantly easier to debug and maintain.
WITH monthly_sales AS (
SELECT DATE_TRUNC('month', order_date) AS sales_month, SUM(amount) AS revenue
FROM orders
GROUP BY 1
),
growth_calc AS (
SELECT sales_month, revenue,
LAG(revenue) OVER(ORDER BY sales_month) AS prior_month_rev
FROM monthly_sales
)
SELECT sales_month, revenue,
ROUND(((revenue - prior_month_rev) / prior_month_rev) * 100, 2) AS mom_growth_pct
FROM growth_calc;How do you compute a running total (cumulative sum) in SQL?
A running total is computed by applying the SUM() aggregate function with an OVER() clause ordered chronologically by date or order ID without a GROUP BY collapse.
SELECT order_date, amount,
SUM(amount) OVER(ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_revenue
FROM orders;How do you detect and delete duplicate records from a table in SQL?
Duplicate records are identified by partitioning by the unique identifying columns and assigning a ROW_NUMBER(). Any row where row_num > 1 represents a duplicate.
WITH numbered_duplicates AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY email, first_name, last_name ORDER BY created_at) AS row_num
FROM customers
)
DELETE FROM customers
WHERE customer_id IN (
SELECT customer_id FROM numbered_duplicates WHERE row_num > 1
);How do you perform conditional aggregation in a single query using CASE WHEN?
Conditional aggregation embeds a CASE WHEN statement inside SUM() or COUNT() functions. This allows computing active vs churned users or regional sales side-by-side without writing multiple subqueries.
SELECT DATE_TRUNC('month', order_date) AS order_month,
COUNT(order_id) AS total_orders,
SUM(CASE WHEN status = 'delivered' THEN amount ELSE 0 END) AS delivered_revenue,
SUM(CASE WHEN status = 'returned' THEN amount ELSE 0 END) AS returned_amount
FROM orders
GROUP BY 1
ORDER BY 1;How do LAG() and LEAD() functions work in period-over-period comparisons?
LAG() accesses data from a previous row at a specified physical offset, while LEAD() accesses data from a subsequent row. They are the standard tools for calculating Month-over-Month (MoM) and Year-over-Year (YoY) variances.
SELECT order_date, daily_revenue,
LAG(daily_revenue, 1) OVER(ORDER BY order_date) AS prior_day_revenue,
daily_revenue - LAG(daily_revenue, 1) OVER(ORDER BY order_date) AS daily_change
FROM daily_sales_summary;How do you calculate customer cohort retention in SQL?
First, identify each user's first purchase month (Cohort Month) using MIN(order_date). Second, join subsequent orders to calculate Month-0, Month-1, and Month-3 repeat purchase activity.
WITH first_purchase AS (
SELECT customer_id, DATE_TRUNC('month', MIN(order_date)) AS cohort_month
FROM orders
GROUP BY customer_id
),
subsequent_orders AS (
SELECT o.customer_id, fp.cohort_month,
DATE_TRUNC('month', o.order_date) AS order_month
FROM orders o
JOIN first_purchase fp ON o.customer_id = fp.customer_id
)
SELECT cohort_month, order_month,
COUNT(DISTINCT customer_id) AS retained_customers
FROM subsequent_orders
GROUP BY 1, 2
ORDER BY 1, 2;Practice Live Technical Assessments with SSSAM Faculty
Reading questions is the first step. Writing queries under timed screen-share tests with live mentor reviews builds genuine interview confidence.