Data Analyst (SQL) Interview Questions &amp; Answers

Show answer

Aggregate functions compute a single value from many rows: COUNT, SUM, AVG, MIN and MAX are standard, and databases add others such as STRING_AGG, GROUP_CONCAT, STDDEV and MEDIAN or PERCENTILE_CONT. Used with GROUP BY they return one value per group; without it they return one value for the whole table. Except COUNT(*), they ignore NULL values.

Find the number of employees and average salary in each department.

Show answer

SELECT department_id, COUNT(*) AS emp_count, ROUND(AVG(salary), 2) AS avg_salary FROM employees GROUP BY department_id ORDER BY emp_count DESC; Join with the departments table if department names are needed, and use LEFT JOIN from departments to include empty departments.

What is a CTE and why use it?

Subqueries and CTEsEasy

Show answer

A Common Table Expression is a named temporary result defined with WITH before the main query. It improves readability by breaking a complex query into named steps, can be referenced multiple times in the same statement, and supports recursion. Example: WITH high_value AS (SELECT * FROM orders WHERE amount > 10000) SELECT customer_id, COUNT(*) FROM high_value GROUP BY customer_id.

Find employees who earn more than their department average.

Show answer

SELECT e.emp_id, e.name, e.department_id, e.salary FROM employees e WHERE e.salary > ( SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = e.department_id ); This is a correlated subquery. A window version uses AVG(salary) OVER (PARTITION BY department_id) in a CTE and filters in the outer query.

Why should we hire you for a SQL or data role as a fresher?

SQL BasicsEasy

Show answer

Situation: in college I built a library management project with a MySQL backend. Task: make queries fast and correct. Action: I designed normalised tables, wrote queries with joins and group by, and practised over 150 interview problems online. Result: I can write clean, tested SQL today. I learn quickly, ask good questions and want to grow into a strong data professional with your team.

Calculate a running total of daily sales.

Show answer

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; Add PARTITION BY store_id to restart the running total for each store.

Your GROUP BY query returns an error that a column is not in the group by clause. How do you fix it?

Aggregation and GroupingMedium

Show answer

Each selected column must be grouped or aggregated. Decide what you need: add the column to GROUP BY if it defines the group, aggregate it with MIN, MAX or STRING_AGG if any or all values are acceptable, or remove it. If you need details of the top row in each group, use a window function such as ROW_NUMBER instead of GROUP BY.

Rank employees by salary within each department.

Show answer

SELECT emp_id, name, department_id, salary, DENSE_RANK() OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS dept_rank FROM employees; PARTITION BY restarts ranking for each department, and DENSE_RANK keeps equal salaries at the same rank.

What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?

Show answer

COUNT(*) counts all rows including those with NULLs. COUNT(column) counts rows where that column is not NULL. COUNT(DISTINCT column) counts unique non-null values. In a table of 100 orders where 10 have no coupon and 30 distinct coupons were used, COUNT(*) is 100, COUNT(coupon) is 90 and COUNT(DISTINCT coupon) is 30.

Describe a time you reviewed someone else's SQL and suggested improvements.

Show answer

Situation: a teammate's report query took eight minutes and had nested subqueries. Task: help without discouraging him. Action: I reviewed it with him, rewrote parts as CTEs, replaced a correlated subquery with a pre-aggregated join, and explained the execution plan differences. Result: the query ran in 40 seconds, and he later applied the same approach to two other reports.

What is the difference between IN and EXISTS?

Show answer

IN checks whether a value matches any value in a list or subquery result. EXISTS checks whether a correlated subquery returns at least one row and stops at the first match. Modern optimisers often treat them similarly, but EXISTS is clearer for correlated checks and handles NULLs safely. For large subquery results, EXISTS with an index on the join column is usually efficient.

What is the difference between GROUP BY and window functions?

Show answer

GROUP BY collapses rows into one row per group, so detail columns are lost. Window functions keep every row and add the computed value alongside, such as each employee with their department average. You can combine both: group first, then apply a window over the grouped result, for example percent of total using SUM(SUM(sales)) OVER ().

What do PARTITION BY and ORDER BY do inside OVER?

Show answer

PARTITION BY divides rows into groups and the window function restarts for each group, like per department. ORDER BY defines the order of rows within each partition, needed for ranking, running totals and LAG or LEAD. Without PARTITION BY the whole result is one partition. Without ORDER BY, aggregate windows compute over the entire partition.

What is a CROSS JOIN and when is it useful?

JoinsEasy

Show answer

A CROSS JOIN returns the Cartesian product, every row of the first table combined with every row of the second, so 10 rows by 5 rows gives 50 rows. It is useful for generating combinations, such as all stores by all dates to build a calendar grid and then left join actual sales to show zero-sales days. Accidental cross joins from missing conditions produce huge results.

What is a self join? Give an example.

JoinsEasy

Show answer

A self join joins a table to itself using aliases, used for hierarchical or comparative data in one table. Example: SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.emp_id. Each employee row is matched with the row of its manager. LEFT JOIN keeps the top manager whose manager_id is NULL.

Compare this month's and last month's sales per product using a full outer join.

JoinsMedium

Show answer

SELECT COALESCE(c.product_id, p.product_id) AS product_id, COALESCE(c.sales, 0) AS this_month, COALESCE(p.sales, 0) AS last_month FROM cur_month c FULL OUTER JOIN prev_month p ON c.product_id = p.product_id; Products sold in only one of the months still appear, with zero for the other month.

Get the top 2 highest paid employees in each department.

Show answer

WITH r AS ( SELECT emp_id, name, department_id, salary, ROW_NUMBER() OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS rn FROM employees ) SELECT * FROM r WHERE rn <= 2; Use DENSE_RANK instead of ROW_NUMBER if tied salaries should all be included.

What is the difference between ROWS and RANGE frames?

Show answer

ROWS counts physical rows, so 2 PRECEDING means exactly two rows before. RANGE works on values of the ORDER BY column, so rows with the same value are treated together, and value ranges like INTERVAL '7 days' PRECEDING are possible in some databases. With duplicate dates, a RANGE running total jumps for all ties at once, while ROWS increases row by row.

What is a lateral join or CROSS APPLY?

Subqueries and CTEsHard

Show answer

A lateral join, written as JOIN LATERAL in PostgreSQL and MySQL 8 or CROSS APPLY and OUTER APPLY in SQL Server, lets a subquery in FROM reference columns of tables listed before it. It acts like a correlated subquery that can return multiple rows and columns, ideal for top N per group, for example the three latest orders for each customer.

What is the difference between MIN/MAX on numbers and on strings or dates?

Show answer

MIN and MAX work on any comparable type. On numbers they return smallest and largest values, on dates the earliest and latest, and on strings the first and last in alphabetical order based on collation. MAX(order_date) gives the latest order, and MIN(name) gives the alphabetically first name, which is useful but not a meaningful business metric by itself.

Tell me about a time you significantly improved query performance.

Indexes and PerformanceMedium

Show answer

Situation: a daily settlement report took 45 minutes and often missed its deadline. Task: bring it under ten minutes. Action: I studied the execution plan, replaced a non-SARGable date filter, added a composite covering index, pre-aggregated a large join in a temp table and updated statistics. Result: runtime dropped to six minutes, and the finance team received reports on time every day.

What is the difference between clustered and non-clustered indexes?

Indexes and PerformanceEasy

Show answer

A clustered index defines the physical order of rows in the table, so there can be only one, and in SQL Server the primary key creates it by default. A non-clustered index is a separate structure with key values and pointers to rows, and a table can have many. Range queries on the clustered key are very fast, while non-clustered indexes suit selective lookups.

After joining orders with payments, revenue doubled. What went wrong?

JoinsMedium

Show answer

Some orders have multiple payment rows, such as partial payments or retries, so each order row repeats and SUM(order amount) counts it multiple times. This is join fan-out. Fix it by aggregating payments per order in a subquery or CTE before joining, or summing order amounts from the orders table alone. Always check row counts before and after a join.

You need to delete duplicate rows but keep one copy of each. How do you do it?

Show answer

Use a CTE with ROW_NUMBER: WITH d AS (SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn FROM users) DELETE FROM users WHERE id IN (SELECT id FROM d WHERE rn > 1). SQL Server allows DELETE FROM d WHERE rn > 1 directly. Back up the table first, run a SELECT to verify, then add a unique constraint to prevent new duplicates.

Write a query to list orders with customer names.

JoinsEasy

Show answer

SELECT o.order_id, o.order_date, c.name, o.amount FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id ORDER BY o.order_date DESC; Aliases o and c keep the query short and clear. INNER JOIN drops orders whose customer is missing, so use LEFT JOIN if such orders must still appear.

How does AVG treat NULL values?

Show answer

AVG ignores NULLs, dividing the sum of non-null values by the count of non-null values. If three employees have bonuses 1000, 2000 and NULL, AVG(bonus) is 1500, not 1000. If NULL should mean zero, use AVG(COALESCE(bonus, 0)). Interviewers ask this to check whether you understand how missing data changes business metrics.

Explain 1NF, 2NF and 3NF with simple examples.

Database DesignEasy

Show answer

1NF: each column holds atomic values and there are no repeating groups, so no comma-separated phone numbers. 2NF: in 1NF and every non-key column depends on the whole composite key, so product name should not sit in an order_items table keyed by order and product. 3NF: in 2NF with no transitive dependency, so city should not determine state inside the employees table.

Your NOT IN query returns zero rows even though you expect results. What do you check?

Show answer

Check whether the subquery returns any NULL values, because NOT IN with a NULL makes every comparison unknown. Run the subquery alone and look for NULLs. Fix it with NOT EXISTS, which handles NULLs correctly, or add WHERE column IS NOT NULL inside the subquery. Also confirm data types match so values compare as expected.

Find employees earning more than the company average.

Subqueries and CTEsEasy

Show answer

SELECT emp_id, name, salary FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees ) ORDER BY salary DESC; The inner query is a non-correlated scalar subquery that runs once and returns one value used for comparison.

What is GROUP BY and what is the rule for selected columns?

Show answer

GROUP BY groups rows that share values in specified columns so aggregates can be computed for each group. Every column in SELECT must either be in GROUP BY or inside an aggregate function. SELECT city, COUNT(*) FROM customers GROUP BY city is valid, but adding name without aggregating it is an error in standard SQL, though older MySQL modes allowed it.

Write a query to show each employee with their manager's name.

JoinsMedium

Show answer

SELECT e.emp_id, e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.emp_id; The same table is used twice with different aliases. LEFT JOIN ensures the CEO, who has no manager, still appears with a NULL manager name.

What do NTILE, PERCENT_RANK and CUME_DIST do?

Show answer

NTILE(n) splits ordered rows into n roughly equal buckets, useful for quartiles or deciles. PERCENT_RANK returns (rank - 1) / (rows - 1), a relative position between 0 and 1. CUME_DIST returns the fraction of rows with values less than or equal to the current row. They are used for customer segmentation, percentile reports and scoring.

Match each employee's salary to a grade using a range table.

JoinsMedium

Show answer

SELECT e.name, e.salary, g.grade FROM employees e JOIN salary_grades g ON e.salary BETWEEN g.min_salary AND g.max_salary; This is a non-equi join. Ensure grade ranges do not overlap, otherwise an employee matches more than one grade and appears twice.

How do you approach a SQL problem you have never seen before in an interview?

Subqueries and CTEsEasy

Show answer

Situation: in a campus interview I got a problem about consecutive login days that I had not practised. Task: solve it while explaining my thinking. Action: I clarified the table and expected output, wrote a small example, broke it into steps with CTEs, and talked through each step before writing the final query. Result: I solved it with a small hint and the interviewer appreciated the structured approach.

Find duplicate email addresses in a users table.

Aggregation and GroupingMedium

Show answer

SELECT email, COUNT(*) AS cnt FROM users GROUP BY email HAVING COUNT(*) > 1; To see full duplicate rows: SELECT * FROM users WHERE email IN ( SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1 ) ORDER BY email; Normalise case with LOWER(email) if needed.

What happens when you join on a column that has NULL values?

JoinsEasy

Show answer

NULL never equals NULL in a join condition, so rows with NULL in the join column do not match each other in an inner join. In a LEFT JOIN, left rows with NULL keys are kept but get NULLs for right columns. If you really need NULLs to match, use a condition such as a.key IS NOT DISTINCT FROM b.key, or COALESCE both sides carefully.

An interviewer asks to show each employee's salary next to their department's average. How do you do it?

Show answer

Use a window aggregate: SELECT name, department_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS dept_avg, salary - AVG(salary) OVER (PARTITION BY department_id) AS diff FROM employees. Unlike GROUP BY, every employee row is kept. Mention that a join with a grouped subquery produces the same result but is longer.

What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

Show answer

For salaries 900, 800, 800, 700 ordered descending: ROW_NUMBER gives 1, 2, 3, 4 with unique numbers even for ties; RANK gives 1, 2, 2, 4, skipping after ties; DENSE_RANK gives 1, 2, 2, 3 without gaps. Use ROW_NUMBER for deduplication or picking one row, and DENSE_RANK for Nth highest value questions where ties should share a position.

What are LAG and LEAD functions?