SQL Interview Questions for Data Analysts
India 2026 — 30 Q&A from Beginner to Advanced

Use WHERE with AND to combine conditions. Always filter before sorting.

SELECT employee_id, name, salary
FROM employees
WHERE department = 'Sales'
  AND salary > 50000
ORDER BY salary DESC;

WHERE filters rows before aggregation — it operates on individual rows. HAVING filters groups after GROUP BY has been applied — it operates on aggregated values. You cannot use aggregate functions (SUM, COUNT, AVG) in a WHERE clause.

-- WHERE: filters rows before grouping
SELECT department, COUNT(*) AS emp_count
FROM employees
WHERE status = 'Active'        -- filters individual rows first
GROUP BY department
HAVING COUNT(*) > 5;           -- filters groups after aggregation

Use date functions to extract month and year, or use BETWEEN with date literals. BETWEEN is inclusive on both ends.

-- Option 1: date functions
SELECT * FROM orders
WHERE MONTH(order_date) = 3
  AND YEAR(order_date) = 2025;

-- Option 2: BETWEEN (more portable)
SELECT * FROM orders
WHERE order_date BETWEEN '2025-03-01' AND '2025-03-31';

DISTINCT removes duplicate rows from the result set. Use it when you need unique values — e.g., list of unique cities where customers are located. Avoid using SELECT DISTINCT as a lazy fix for bad JOINs producing duplicates — fix the JOIN instead.

SELECT DISTINCT city FROM customers;
-- Returns one row per unique city

% matches any sequence of characters. _ matches exactly one character.

SELECT product_name FROM products
WHERE product_name LIKE 'Pro%';

-- Ends with 'Plus': WHERE product_name LIKE '%Plus'
-- Contains 'Pro': WHERE product_name LIKE '%Pro%'

INNER JOIN returns rows where there is a match in BOTH tables. LEFT JOIN returns all rows from the left table and matched rows from the right (NULLs where no match). RIGHT JOIN is the mirror. FULL OUTER JOIN returns all rows from both tables — NULLs where there is no match on either side.

-- Customers and their orders (some customers have no orders)
SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
-- Customers with no orders appear with NULL order_id and amount

This is a classic LEFT JOIN + IS NULL pattern — one of the most commonly asked JOIN questions in Indian interviews.

SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

A self join joins a table to itself by aliasing it with two different names. Used for hierarchical data where a foreign key references the same table's primary key.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;
-- LEFT JOIN ensures employees with no manager (CEO) also appear

Use LEFT JOIN with IS NULL, or use EXCEPT (NOT IN with a subquery also works but is slower on large tables).

-- Method 1: LEFT JOIN + IS NULL (recommended)
SELECT DISTINCT s24.product_id
FROM sales s24
LEFT JOIN sales s25
  ON s24.product_id = s25.product_id
  AND YEAR(s25.sale_date) = 2025
WHERE YEAR(s24.sale_date) = 2024
  AND s25.product_id IS NULL;

-- Method 2: EXCEPT
SELECT product_id FROM sales WHERE YEAR(sale_date) = 2024
EXCEPT
SELECT product_id FROM sales WHERE YEAR(sale_date) = 2025;

A standard multi-table JOIN with aggregation — tests whether the candidate can combine JOIN and GROUP BY correctly.

SELECT c.category_name,
       p.product_name,
       SUM(s.amount) AS total_sales
FROM sales s
JOIN products p ON s.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
GROUP BY c.category_name, p.product_name
ORDER BY total_sales DESC;

Aggregate first, then use ORDER BY + LIMIT (or TOP in SQL Server). Do not use a subquery here — it is slower and unnecessary.

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC
LIMIT 1;          -- use TOP 1 in SQL Server

COUNT in GROUP BY with HAVING — the HAVING filter runs after aggregation.

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 3
ORDER BY order_count DESC;

Classic interview question — multiple valid approaches. The subquery approach works on all databases. In SQL Server, use OFFSET-FETCH. With window functions, use DENSE_RANK.

-- Approach 1: Subquery (universal)
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Approach 2: DENSE_RANK (preferred — handles ties correctly)
SELECT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk = 2;

Group by the duplicate column and count — any group with COUNT > 1 is a duplicate.

SELECT email, COUNT(*) AS occurrences
FROM customers
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY occurrences DESC;

-- To see the full rows for duplicates:
SELECT * FROM customers
WHERE email IN (
  SELECT email FROM customers
  GROUP BY email HAVING COUNT(*) > 1
);

"Top N per group" is one of the most asked advanced SQL patterns in India. Use ROW_NUMBER() or RANK() with PARTITION BY category.

SELECT category_id, product_id, total_sales, rnk
FROM (
  SELECT category_id,
         product_id,
         SUM(amount) AS total_sales,
         RANK() OVER (
           PARTITION BY category_id
           ORDER BY SUM(amount) DESC
         ) AS rnk
  FROM sales
  GROUP BY category_id, product_id
) ranked
WHERE rnk <= 3
ORDER BY category_id, rnk;

All three assign a sequential number within a window, but differ on how ties are handled. ROW_NUMBER gives unique numbers even to ties (arbitrary order for ties). RANK skips numbers after ties (1,2,2,4). DENSE_RANK never skips (1,2,2,3).

SELECT name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
  RANK()       OVER (ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;
-- For two employees both earning 80000:
-- row_num: 1,2 | rnk: 1,1 (next=3) | dense_rnk: 1,1 (next=2)

SUM() OVER with ORDER BY creates a cumulative (running) total — very common in financial and e-commerce analytics.

SELECT sale_date,
       amount,
       SUM(amount) OVER (
         ORDER BY sale_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM daily_sales
ORDER BY sale_date;

LAG() returns the value from a previous row in the partition. LEAD() does the same for the next row. Essential for period-over-period comparisons.

SELECT month,
       revenue,
       LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
       revenue - LAG(revenue, 1) OVER (ORDER BY month) AS mom_change
FROM monthly_sales;

NTILE(n) divides rows into n equal buckets. Use NTILE(4) for quartiles.

SELECT customer_id,
       total_spend,
       NTILE(4) OVER (ORDER BY total_spend) AS quartile
FROM (
  SELECT customer_id, SUM(amount) AS total_spend
  FROM orders
  GROUP BY customer_id
) spend_summary;

ROW_NUMBER() with PARTITION BY customer and ORDER BY date DESC assigns 1 to the newest record per customer. Filter for row_num = 1.

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY created_at DESC
    ) AS rn
  FROM customers_raw
)
SELECT * FROM ranked WHERE rn = 1;

Use ROWS BETWEEN to define a rolling window of the current row plus 2 preceding rows.

SELECT sale_date,
       amount,
       AVG(amount) OVER (
         ORDER BY sale_date
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS moving_avg_3day
FROM daily_sales;

A CTE (Common Table Expression) is a named temporary result set defined with WITH. It is identical in performance to a subquery in most databases, but far more readable for multi-step queries. CTEs can also be recursive (for hierarchical data). Prefer CTEs over nested subqueries when the query has more than 2 steps.

WITH high_value_customers AS (
  SELECT customer_id, SUM(amount) AS total_spend
  FROM orders
  GROUP BY customer_id
  HAVING SUM(amount) > 100000
)
SELECT c.name, h.total_spend
FROM customers c
JOIN high_value_customers h ON c.customer_id = h.customer_id;

Requires a subquery or CTE to first compute the national average, then compare city averages against it.

WITH city_avg AS (
  SELECT city,
         AVG(amount) AS avg_order_value
  FROM orders o
  JOIN customers c ON o.customer_id = c.customer_id
  GROUP BY city
),
national_avg AS (
  SELECT AVG(amount) AS nat_avg FROM orders
)
SELECT ca.city, ca.avg_order_value
FROM city_avg ca, national_avg na
WHERE ca.avg_order_value > na.nat_avg
ORDER BY ca.avg_order_value DESC;

Recursive CTEs have two parts: the anchor (base case) and the recursive member joined to the CTE itself. Used for tree structures like org charts, category trees, and bill of materials.

WITH RECURSIVE org_chart AS (
  -- Anchor: top-level employees (no manager)
  SELECT employee_id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: join employees to their manager row
  SELECT e.employee_id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT level, name FROM org_chart
ORDER BY level, name;

Count distinct months per customer and compare to 12 (all months of 2024).

SELECT customer_id
FROM orders
WHERE YEAR(order_date) = 2024
GROUP BY customer_id
HAVING COUNT(DISTINCT MONTH(order_date)) = 12;

NULL represents an unknown value. Any comparison with NULL using = returns NULL (not TRUE or FALSE) — NULL = NULL is NULL. Use IS NULL and IS NOT NULL to check for NULLs. In aggregate functions, NULLs are ignored (SUM, AVG, COUNT(*) counts NULLs but COUNT(column) does not). In JOINs, NULLs never match.

-- WRONG: WHERE manager_id = NULL   → returns nothing
-- RIGHT:
SELECT * FROM employees WHERE manager_id IS NULL;

-- COALESCE replaces NULL with a default value
SELECT name, COALESCE(phone, 'No phone') AS phone
FROM customers;

DELETE removes specific rows (filterable with WHERE), is logged and can be rolled back. TRUNCATE removes all rows instantly, is minimally logged, cannot be rolled back in most databases, and resets identity columns. DROP removes the entire table including its structure. Use DELETE for selective removal, TRUNCATE to empty a table entirely, DROP only when removing the table permanently.

(1) Use EXPLAIN / EXPLAIN ANALYZE to see the query plan and identify full table scans. (2) Check if the columns in WHERE, JOIN ON, and ORDER BY have indexes — add them if not. (3) Avoid using functions on indexed columns in WHERE (e.g., WHERE YEAR(date) = 2024 cannot use an index on date — use a range filter instead). (4) Replace correlated subqueries with JOINs. (5) Use CTEs or temp tables to materialise expensive intermediate results. (6) Avoid SELECT * — retrieve only the columns you need.

-- Slow: function on indexed column prevents index use
WHERE YEAR(order_date) = 2025

-- Fast: range filter uses the index
WHERE order_date >= '2025-01-01'
  AND order_date <  '2026-01-01'

Pivoting uses conditional aggregation — SUM with CASE WHEN for each month. Most databases do not have a native PIVOT operator; this pattern works everywhere.

SELECT product_id,
  SUM(CASE WHEN MONTH(sale_date) = 1  THEN amount ELSE 0 END) AS Jan,
  SUM(CASE WHEN MONTH(sale_date) = 2  THEN amount ELSE 0 END) AS Feb,
  SUM(CASE WHEN MONTH(sale_date) = 3  THEN amount ELSE 0 END) AS Mar,
  -- ... repeat for all 12 months
  SUM(CASE WHEN MONTH(sale_date) = 12 THEN amount ELSE 0 END) AS Dec
FROM sales
WHERE YEAR(sale_date) = 2025
GROUP BY product_id;

Use LAG() to get the previous event timestamp, compute the gap, then use a CTE to assign session IDs. Classic e-commerce / product analytics question.

WITH event_gaps AS (
  SELECT user_id, event_time,
    LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_time,
    DATEDIFF(MINUTE,
      LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time),
      event_time
    ) AS gap_minutes
  FROM events
),
session_flags AS (
  SELECT *,
    CASE WHEN gap_minutes > 30 OR gap_minutes IS NULL THEN 1 ELSE 0 END AS new_session
  FROM event_gaps
)
SELECT user_id, event_time, SUM(new_session) OVER (
  PARTITION BY user_id ORDER BY event_time
) AS session_id
FROM session_flags;