30 SQL Interview Questions for Freshers & Analysts (2026)

SQL technical rounds are the standard screening checkpoint for any data analytics role. Whether you are interviewing at a fast-growing startup or a Fortune 500 company, interviewers evaluate not only your ability to write valid syntax, but also how you handle edge cases, NULL values, and query performance.

For deep dives on specific advanced topics, see our dedicated guides on SQL Window Functions, SQL CTEs, and SQL JOIN Fan-Out.

Part 1: SQL Interview Questions for Freshers & Junior Analysts (Q1–Q10)

1. What is SQL and why is it essential for data analysts?

Answer: SQL (Structured Query Language) is the standardized declarative language used to manage, manipulate, and query relational database management systems (RDBMS) and cloud data warehouses. It is essential because business transactional data (users, payments, inventory) lives in relational databases, and SQL is the direct tool used to extract and aggregate datasets for business intelligence.

2. What is the difference between WHERE and HAVING?

Answer: WHERE filters individual rows before any aggregation or grouping occurs. HAVING filters aggregated row groups after the GROUP BY clause is evaluated.

-- WHERE filters individual employee records prior to grouping
SELECT department, COUNT(*) AS emp_count
FROM employees
WHERE salary > 50000
GROUP BY department;
 
-- HAVING filters grouped departments having more than 5 members
SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

3. Explain the different types of SQL JOINs.

Answer:

• INNER JOIN: Returns only rows that have matching values in both tables.
• LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and matched rows from the right table. Unmatched right columns contain NULL.
• RIGHT JOIN: Returns all rows from the right table and matching rows from the left.
• FULL OUTER JOIN: Returns all rows when there is a match in either the left or right table.
• CROSS JOIN: Returns the Cartesian product (every row from the first table paired with every row of the second).

-- INNER JOIN: Only customers who placed an order
SELECT c.name, o.order_id, o.amount
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;
 
-- LEFT JOIN: All customers, even those with zero orders
SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

4. What is the difference between UNION and UNION ALL?

Answer: UNION merges the result sets of two queries and performs a distinct deduplication pass across all columns. UNION ALL merges the result sets while retaining all duplicates. UNION ALL is faster because it does not incur the sorting overhead required to deduplicate rows.

5. What is a PRIMARY KEY?

Answer: A PRIMARY KEY is a constraint that uniquely identifies each record in a database table. A table can only have one primary key constraint, and the column(s) cannot contain NULL values.

6. What is a FOREIGN KEY?

Answer: A FOREIGN KEY is a column (or group of columns) in one table that references the PRIMARY KEY of another table. It establishes referential integrity, ensuring that orphaned child records (e.g. an order with a non-existent customer_id) cannot be inserted into the database.

7. What does GROUP BY do, and what can you include in the SELECT list?

Answer: GROUP BY collapses multiple rows sharing identical grouping values into a single summary row per group. Every non-aggregated column in the SELECT statement must be explicitly included in the GROUP BY clause; otherwise, the database cannot determine which individual row value to return.

-- Valid query: department is grouped, salary is aggregated
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

8. What is the difference between DELETE, TRUNCATE, and DROP?

Answer:

• DELETE: A DML (Data Manipulation Language) command that removes specific rows matching a WHERE clause. It logs individual row deletions and can be rolled back within a transaction.
• TRUNCATE: A DDL command that deallocates entire data pages, instantly removing all rows. It is faster than DELETE but cannot filter rows with a WHERE condition.
• DROP: A DDL command that removes the entire table structure, indexes, constraints, and data permanently from the database catalog.

9. How do you perform pattern matching on strings in SQL?

Answer: Use the LIKE operator with wildcard characters:

• % matches zero or more characters.
• _ matches exactly one character.
• PostgreSQL also provides ILIKE for case-insensitive matching.

-- Customers whose names start with 'A'
SELECT * FROM customers WHERE name LIKE 'A%';
 
-- Product codes with exactly 4 characters ending in 'X'
SELECT * FROM products WHERE code LIKE '___X';
 
-- Case-insensitive email domain search (PostgreSQL)
SELECT * FROM users WHERE email ILIKE '%@gmail.com';

10. What is the difference between CHAR and VARCHAR?

Answer: CHAR(n) is a fixed-length data type that pads unused storage with trailing spaces up to length $n$. VARCHAR(n) is a variable-length data type that stores only the actual characters inserted plus a 1–2 byte length header. Use CHAR for fixed-length codes (e.g., ISO country codes 'US', 'IN') and VARCHAR for general text.

Part 2: Intermediate Interview Questions (Q11–Q20)

11. Write a query to find the second highest salary from an employees table.

Answer: Use DENSE_RANK() or a subquery with MAX():

-- Method 1: Using DENSE_RANK() (Handles ties cleanly)
WITH ranked_salaries AS (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT DISTINCT salary
FROM ranked_salaries
WHERE rnk = 2;
 
-- Method 2: Subquery with MAX()
SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

12. What are SQL Window Functions and how do they differ from GROUP BY?

Answer: Window functions perform calculations across a defined subset of rows (a "window") related to the current row without collapsing the result set into a single summary row. Common window functions include ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), and running aggregates (SUM() OVER(...)).

-- Compute running revenue total per department over time
SELECT
  department,
  sale_date,
  revenue,
  SUM(revenue) OVER(
    PARTITION BY department
    ORDER BY sale_date
  ) AS running_dept_revenue
FROM sales;

13. What is a Common Table Expression (CTE) and when should you use one?

Answer: A CTE is a temporary named result set defined using the WITH clause that exists only during the execution of a query. CTEs improve query modularity, readability, and can be referenced multiple times or used recursively (e.g. organizational hierarchy trees).

WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', order_date) AS order_month,
    SUM(amount) AS total_revenue
  FROM orders
  GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
  order_month,
  total_revenue,
  LAG(total_revenue) OVER (ORDER BY order_month) AS previous_month_revenue,
  total_revenue - LAG(total_revenue) OVER (ORDER BY order_month) AS mom_diff
FROM monthly_revenue;

14. How do you handle NULL values in calculations and filtering?

Answer:

• In SQL, NULL = NULL evaluates to UNKNOWN (falsy). Always use IS NULL or IS NOT NULL.
• Use COALESCE(col, default_value) to substitute a fallback value for NULL expressions.
• Use NULLIF(val1, val2) to prevent division-by-zero errors.

-- Safe revenue per user calculation preventing division by zero
SELECT
  user_id,
  COALESCE(total_spent / NULLIF(total_orders, 0), 0) AS avg_order_value
FROM user_summary;

15. Write a query to find duplicate records in a table.

Answer: Use GROUP BY combined with HAVING COUNT(*) > 1:

SELECT email, COUNT(*) AS duplicate_count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

16. In what logical order does a SQL query execute?

Answer: A SQL query executes in the following logical sequence:

1. FROM and JOIN (Build base dataset)
2. WHERE (Filter raw rows)
3. GROUP BY (Aggregate groups)
4. HAVING (Filter aggregated groups)
5. SELECT (Evaluate projection expressions and window functions)
6. DISTINCT (Remove duplicate rows)
7. ORDER BY (Sort output)
8. LIMIT / OFFSET (Slice result size)

Note: This explains why column aliases defined in SELECT cannot be referenced in WHERE clauses.

17. Why does a WHERE clause on a right table convert a LEFT JOIN into an INNER JOIN?

Answer: When rows in the left table have no match in the right table, the right columns are populated with NULL. If the WHERE clause tests WHERE right_table.status = 'active', NULL = 'active' evaluates to UNKNOWN, causing unmatched rows to be dropped.

-- Silently behaves as INNER JOIN:
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.status = 'completed';
 
-- Correct: preserves customers with zero completed orders
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o
  ON c.id = o.customer_id AND o.status = 'completed';

18. What is the difference between IN and EXISTS?

Answer:

• IN evaluates whether a value matches any element in an explicit list or subquery column. If a subquery returns a NULL, NOT IN will evaluate to false for all rows.
• EXISTS tests whether a correlated subquery returns at least one matching row and short-circuits as soon as a match is found. NOT EXISTS handles NULL values safely.

-- Safe NULL handling using NOT EXISTS
SELECT c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

19. How do you calculate a 7-day moving average in SQL?

Answer: Use the ROWS BETWEEN frame specification within an AVG() window function:

SELECT
  sale_date,
  daily_revenue,
  AVG(daily_revenue) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily_metrics;

20. Write a query to find the top 3 highest-spending customers in each country.

Answer: Partition by country and rank by spending using DENSE_RANK():

WITH ranked_customers AS (
  SELECT
    country,
    customer_id,
    SUM(total_spend) AS spend,
    DENSE_RANK() OVER (
      PARTITION BY country
      ORDER BY SUM(total_spend) DESC
    ) AS rnk
  FROM transactions
  GROUP BY country, customer_id
)
SELECT country, customer_id, spend, rnk
FROM ranked_customers
WHERE rnk <= 3
ORDER BY country, rnk;

Part 3: Advanced & Senior Interview Questions (Q21–Q30)

21. What is the exact difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?

Answer:

• ROW_NUMBER() assigns sequential integer ranks regardless of ties (e.g. 1, 2, 3, 4).
• RANK() assigns identical ranks to tied rows, but skips subsequent rank numbers (e.g. 1, 2, 2, 4).
• DENSE_RANK() assigns identical ranks to tied rows without skipping numbers (e.g. 1, 2, 2, 3).

SELECT
  employee_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;

22. Write a query to calculate Month-over-Month (MoM) revenue growth percentage.

Answer: Use the LAG() window function to retrieve the previous month's revenue:

WITH monthly_sales AS (
  SELECT
    DATE_TRUNC('month', order_date) AS sales_month,
    SUM(amount) AS revenue
  FROM orders
  GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
  sales_month,
  revenue,
  LAG(revenue) OVER (ORDER BY sales_month) AS prev_month_revenue,
  ROUND(
    (revenue - LAG(revenue) OVER (ORDER BY sales_month)) * 100.0 /
    NULLIF(LAG(revenue) OVER (ORDER BY sales_month), 0),
    2
  ) AS mom_growth_pct
FROM monthly_sales
ORDER BY sales_month;

23. Write a query to find users who logged in on 3 consecutive days.

Answer: Use LAG() with offsets of 1 and 2 partitioned by user:

WITH login_history AS (
  SELECT DISTINCT user_id, CAST(login_time AS DATE) AS login_date
  FROM user_logins
),
sequenced_logins AS (
  SELECT
    user_id,
    login_date,
    LAG(login_date, 1) OVER (PARTITION BY user_id ORDER BY login_date) AS prev_1,
    LAG(login_date, 2) OVER (PARTITION BY user_id ORDER BY login_date) AS prev_2
  FROM login_history
)
SELECT DISTINCT user_id
FROM sequenced_logins
WHERE login_date = prev_1 + INTERVAL '1 day'
  AND prev_1 = prev_2 + INTERVAL '1 day';

24. What are the key strategies for SQL query performance optimization?

Answer:

1. Indexing: Add B-tree indexes on join foreign keys and frequent WHERE equality/range columns.
2. Avoid SELECT *: Retrieve only the necessary columns to reduce I/O and network serialization.
3. Prevent functions on indexed columns: Writing WHERE YEAR(created_at) = 2026 invalidates index seeks; use WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01' instead.
4. Use appropriate JOIN ordering & CTE materialization: In cloud warehouses, filter high-cardinality tables before performing broad joins.
5. Inspect EXPLAIN ANALYZE: Check for unexpected sequential scans and disk spill operations.

25. How do you pivot row data into columnar summary reports?

Answer: Use conditional aggregation with CASE statements:

SELECT
  product_id,
  SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 1 THEN amount ELSE 0 END) AS q1_jan,
  SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 2 THEN amount ELSE 0 END) AS q1_feb,
  SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 3 THEN amount ELSE 0 END) AS q1_mar
FROM sales
GROUP BY product_id;

26. Write a query to calculate month-1 user retention by signup cohort.

Answer: Group signups into cohorts by month, then left join against user activity occurring in month + 1:

WITH cohorts AS (
  SELECT user_id, DATE_TRUNC('month', signup_date) AS cohort_month
  FROM users
),
activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', activity_date) AS active_month
  FROM user_events
)
SELECT
  c.cohort_month,
  COUNT(DISTINCT c.user_id) AS total_cohort_users,
  COUNT(DISTINCT a.user_id) AS retained_m1_users,
  ROUND(
    100.0 * COUNT(DISTINCT a.user_id) / NULLIF(COUNT(DISTINCT c.user_id), 0),
    1
  ) AS m1_retention_rate
FROM cohorts c
LEFT JOIN activity a
  ON c.user_id = a.user_id
 AND a.active_month = c.cohort_month + INTERVAL '1 month'
GROUP BY c.cohort_month
ORDER BY c.cohort_month;

27. How do you deduplicate a table, retaining only the most recent row per key?

Answer: Use ROW_NUMBER() partitioned by unique key and ordered by updated timestamp descending:

WITH ranked_records AS (
  SELECT
    id,
    ROW_NUMBER() OVER (
      PARTITION BY email
      ORDER BY updated_at DESC, id DESC
    ) AS rn
  FROM customers
)
DELETE FROM customers
WHERE id IN (
  SELECT id FROM ranked_records WHERE rn > 1
);

28. How do you calculate the median value of a column in SQL?

Answer: In PostgreSQL, Snowflake, and BigQuery, use PERCENTILE_CONT(0.5):

-- PostgreSQL & modern warehouses
SELECT
  department,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees
GROUP BY department;

29. What does EXPLAIN vs EXPLAIN ANALYZE tell you?

Answer:

• EXPLAIN generates the optimizer's estimated execution plan, showing predicted index scans, join algorithms (Nested Loop, Hash Join, Merge Join), and estimated cost units without executing the query.
• EXPLAIN ANALYZE runs the query in real-time and prints the actual execution timings, actual rows returned, and buffer hits alongside the estimates. A wide divergence between estimated and actual row counts indicates stale database statistics.

30. What is the difference between a clustered and non-clustered index?

Answer:

• Clustered Index: Determines the physical storage order of data rows on disk. A table can possess only one clustered index (typically the Primary Key).
• Non-Clustered Index: A separate B-tree data structure that contains index key values paired with row pointers (Tuple IDs) back to the actual data pages. A single table can support multiple non-clustered indexes.

Company-Specific SQL Interview Guides

Practicing SQL for specific tech companies and enterprise hiring loops? Explore our curated company breakdown guides with actual technical questions, SQL case studies, and interview tips:

• Amazon Data Analyst Interview Questions — Bar raiser SQL scenarios, window function aggregations, and business metrics.
• Google Data Analyst Interview Questions — Complex BigQuery CTEs, analytical reasoning, and product metrics.
• Walmart Data Analyst Interview Questions — Retail supply chain SQL queries, inventory turnover, and sales trends.
• JPMorgan Data Analyst Interview Questions — Financial transactions analysis, risk analytics SQL, and date-range calculations.
• Uber Data Analyst Interview Questions — Surge pricing logic, geospatial ride metrics, and cohort retention.
• Flipkart Data Analyst Interview Questions — E-commerce conversion funnels, GMV calculations, and order fulfillment SQL.
• Swiggy Data Analyst Interview Questions — Delivery turnaround tracking, rider allocation, and food delivery analytics.
• Zomato Data Analyst Interview Questions — Restaurant partner KPIs, customer discount impact, and active user trends.
• Razorpay Data Analyst Interview Questions — Fintech payment gateway success rates, dispute settlement queries, and fraud anomaly detection.
• Zepto Data Analyst Interview Questions — 10-minute quick-commerce order batches, dark store utilization, and delivery cohort analysis.