Top 5 SQL Questions for Data Analytics Interviews
1. Expect a question on SQL JOINS (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL
OUTER JOIN, CROSS JOIN, SELF JOIN).
Why is it asked?
SQL JOINS are essential for combining data from multiple tables, a core skill in data
analytics.
Example Question: Retrieve all customers and their orders, ensuring customers without
orders are also included.
Sample SQL Query:
SELECT c.customer_id, c.customer_name, o.order_id, o.order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
Tips: Understand how different JOINS impact the result set, especially with NULLs in LEFT
and RIGHT JOINS.
2. Expect a question on Subqueries/CTEs (Common Table Expressions).
Why is it asked?
Subqueries and CTEs improve query structure, readability, and efficiency when dealing with
complex data retrieval.
Example Question: Find customers who placed more than three orders using both a
subquery and a CTE.
Subquery Approach:
SELECT customer_id FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 3;
CTE Approach:
WITH OrderCounts AS (
SELECT customer_id, COUNT(order_id) AS order_count
FROM orders GROUP BY customer_id
)
SELECT customer_id FROM OrderCounts WHERE order_count > 3;
Tips: Use CTEs for readability and recursion; subqueries work well for one-time
calculations.
3. Expect a question on how you would optimize a slow SQL query.
Why is it asked?
Performance tuning is crucial for handling large datasets efficiently.
Example Question: Given a slow-running query, how would you improve its performance?
Optimization Strategies:
-- Avoiding SELECT * and using specific columns:
SELECT customer_id, order_date FROM orders;
-- Creating indexes on frequently filtered columns:
CREATE INDEX idx_orders_date ON orders(order_date);
-- Using proper filtering instead of scanning the full table:
SELECT * FROM orders WHERE order_date > '2023-01-01';
Tips: Always check execution plans, avoid unnecessary calculations, and use indexing
wisely.
4. Expect a question on SQL Window Functions to identify or rank records.
Why is it asked?
Window functions allow ranking, running totals, and calculations within partitions.
Example Question: Rank employees within each department by salary.
Sample SQL Query:
SELECT employee_id, department_id, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;
Tips: Understand the difference between RANK(), DENSE_RANK(), and ROW_NUMBER().
5. Expect a question on Aggregations and GROUP BY with filtering conditions.
Why is it asked?
Grouping data and filtering aggregated results is a core skill in analytics.
Example Question: Find departments where the average salary is greater than $50,000.
Sample SQL Query:
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;
Tips: HAVING is used for filtering after aggregation, while WHERE filters before
aggregation.