SQL Interview Questions for Data Analyst Role¶

Fundamentals & Basic Queries¶

[ ] What's the difference between WHERE and HAVING clauses?
[ ] What's the difference between DELETE, TRUNCATE, and DROP commands?
[ ] What's the difference between UNION and UNION ALL?
[ ] How do you handle NULL values in aggregate functions like COUNT, SUM, or AVG?
[ ] How do you detect and delete duplicate rows in SQL?
[ ] How do you write an UPDATE query to swap the values in a column (e.g., swapping 'Male' and 'Female' genders)?
[ ] What's the difference between CHAR, VARCHAR, and TEXT data types in terms of storage and performance?
[ ] How do you filter dates correctly when the column includes timestamps (e.g., WHERE DATE(order_date) = '2025-01-01' vs. range queries)?
[ ] How do you safely write an INSERT statement that avoids duplicate key violations without breaking the whole batch?
[ ] What's the difference between IN and EXISTS in terms of performance and result behavior?

[ ] What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN?
[ ] What's the difference between correlated and uncorrelated subqueries?
[ ] How do you write a query to return employees with salaries greater than their manager's? (Self Join)
[ ] How do you identify and remove orphan records across related tables?
[ ] How do you find records in one table that do not have a corresponding record in another?
[ ] Explain the difference between JOIN, LEFT JOIN, and LEFT JOIN ... WHERE right_table.id IS NULL (anti-join).
[ ] Given two tables — Orders (order_id, customer_id) and Payments (payment_id, order_id, status) — how do you find orders with no successful payments?
[ ] Can you join on multiple columns? Write an example using a composite key.
[ ] What happens when you join tables on columns with duplicate values? (e.g., many-to-many explosion)

[ ] What's the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?
[ ] How do you find the second highest salary in SQL without using TOP, LIMIT, or RANK?
[ ] How do you find the Nth highest value in a column without using window functions?
[ ] How do you calculate running totals or moving averages using window functions?
[ ] How do you pivot rows into columns in SQL (and vice versa)?
[ ] How do you perform recursive queries for hierarchical data (e.g., parent-child relationships using CTEs)?
[ ] How do you find gaps and islands in a sequence of dates using SQL?
[ ] How do you calculate the median in SQL without a built-in MEDIAN function?
[ ] What's the difference between LAG() and LEAD()? Give an example comparing a customer's current and previous purchase date.
[ ] How do you split a comma-separated string into rows (e.g., 'A,B,C' → 3 rows) using STRING_SPLIT (T-SQL) or UNNEST (PostgreSQL)?
[ ] Write a query using FIRST_VALUE() to get the first order date per customer.
[ ] How do you convert rows to JSON or XML in SQL (e.g., FOR JSON AUTO in SQL Server, ROW_TO_JSON in Postgres)?

[ ] What's the difference between clustered and non-clustered indexes?
[ ] How do you optimize queries with large datasets using indexing and query plans?
[ ] How do you implement Slowly Changing Dimensions (SCD) Type 2 logic in SQL?
[ ] Explain EXPLAIN (or EXPLAIN ANALYZE) — what do seq scan, index scan, bitmap scan mean?
[ ] When should you avoid indexes? Give 2–3 examples.
[ ] What's the difference between a temp table, a CTE, and a table variable? Which one materializes results?
[ ] What is partition pruning? When would you partition a fact table by date?
[ ] How do you implement upsert (insert or update) in MySQL (ON DUPLICATE KEY), PostgreSQL (ON CONFLICT), and SQL Server (MERGE)?

[ ] How do you safely change a column's data type without losing data (e.g., VARCHAR → INT with non-numeric values present)?
[ ] Write a query to replace outlier values (e.g., salaries above 99th percentile) with the 99th percentile value.
[ ] How do you standardize phone numbers or zip codes with inconsistent formatting (e.g., (123) 456-7890 → 1234567890)?
[ ] Given a table with start_date and end_date, how do you expand it into one row per day?
[ ] What's a "tally table" (numbers table) and how is it used for date generation or splitting ranges?

[ ] How do you find the first and last day of the current month, quarter, or year?
[ ] Calculate the number of weekdays (Mon–Fri) between two dates excluding holidays (conceptually).
[ ] How do you group records into 15-minute intervals (DATE_TRUNC, DATEPART, FLOOR)?
[ ] What's the difference between DATEADD, DATEDIFF, and DATEPART?
[ ] How do you handle time zones in SQL (e.g., AT TIME ZONE in T-SQL / Postgres)?

[ ] Write a query to compute month-over-month (MoM) revenue growth percentage.
[ ] Given a table of user logins (user_id, login_date), find users who logged in for 5+ consecutive days.
[ ] Identify customers who bought Product A but never bought Product B.
[ ] Build a retention cohort: for each signup month, show % of users active in months 1, 2, 3 etc.
[ ] Write a query to deduplicate events keeping the latest (max timestamp) per user per session.
[ ] How do you sample random rows (e.g., ORDER BY RAND() vs. TABLESAMPLE vs. RANDOM() with seeds)?

[ ] Why does WHERE column = NULL not work, and how do you fix it?
[ ] What's the actual order of execution of a SQL query (not the written order)?
[ ] Can you use an alias from SELECT in the WHERE clause? Why or why not?
[ ] How do you compare two tables to find differences in all columns (e.g., staging vs prod tables)?
[ ] What happens if you GROUP BY a column with NULLs — where do they go?