Data Analytics

🚀 Data Analyst Interview Questions with Answers — Part 2

📊 SQL & Databases

11. What is SQL and why is it critical for data analysts?
SQL (Structured Query Language) is used to communicate with databases. It helps analysts retrieve, filter, clean, and analyze data efficiently.

It is critical because most business data is stored in databases, and SQL allows analysts to extract insights directly from large datasets.

12. How do "SELECT", "WHERE", "ORDER BY", and "LIMIT" work?
✅ "SELECT" → Used to choose columns from a table

SELECT name, salary FROM employees;

✅ "WHERE" → Filters rows based on conditions

SELECT FROM employees
WHERE salary > 50000;

✅ "ORDER BY" → Sorts data ascending or descending

SELECT FROM employees
ORDER BY salary DESC;

✅ "LIMIT" → Restricts the number of rows returned

SELECT FROM employees
LIMIT 5;

13. How do you join two tables ("INNER", "LEFT", "RIGHT", "FULL" joins)?

📌 "INNER JOIN" → Returns matching records from both tables

📌 "LEFT JOIN" → Returns all records from the left table + matching rows from the right table

📌 "RIGHT JOIN" → Returns all records from the right table + matching rows from the left table

📌 "FULL JOIN" → Returns all matching and non-matching records from both tables

Example:
SELECT customers.name, orders.order_id
FROM customers
INNER JOIN orders
ON customers.id = orders.customer_id;

14. How do "GROUP BY" and aggregate functions work?

Aggregate functions summarize data.

Common functions:
✔️ "SUM()"
✔️ "AVG()"
✔️ "COUNT()"
✔️ "MAX()"
✔️ "MIN()"

Example:
SELECT department, AVG(salary)
FROM employees
GROUP BY department;

This groups employees by department and calculates average salary.

15. How do you write subqueries and CTEs?
📌 Subquery → Query inside another query

SELECT name
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);

📌 CTE (Common Table Expression) → Temporary result set that improves readability

WITH high_salary AS (
SELECT
FROM employees
WHERE salary > 50000
)
SELECT FROM high_salary;

16. How do you calculate running totals or rolling averages with window functions?

Window functions perform calculations across rows without collapsing data.

Example — Running Total:
SELECT order_date,
sales,
SUM(sales) OVER (ORDER BY order_date) AS running_total
FROM orders;
Example — Rolling Average:
SELECT order_date,
AVG(sales) OVER (
ORDER BY order_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS rolling_avg
FROM orders;

17. How do you clean and filter data directly in SQL?

Data cleaning in SQL includes:
✔️ Removing duplicates
✔️ Handling NULL values
✔️ Standardizing text
✔️ Filtering invalid rows

Example:
SELECT TRIM(LOWER(name))
FROM customers
WHERE email IS NOT NULL;

18. How do you handle duplicates and NULL values in SQL?

✅ Remove duplicates using "DISTINCT"
SELECT DISTINCT city
FROM customers;

✅ Find NULL values
SELECT
FROM employees
WHERE salary IS NULL;

✅ Replace NULL values
SELECT COALESCE(salary, 0)
FROM employees;

19. How do you optimize a slow query?
Common optimization techniques:

🚀 Use indexes
🚀 Avoid unnecessary columns in "SELECT *"
🚀 Filter data early using "WHERE"
🚀 Optimize joins
🚀 Use proper aggregations
🚀 Analyze execution plans

Efficient queries improve performance and reduce database load.

20. How do you design a simple schema for a business domain?

A schema organizes data into related tables.

Example for an e-commerce business:
📌 "Customers" table
📌 "Orders" table
📌 "Products" table
📌 "Payments" table

Relationships are created using primary keys and foreign keys to maintain data integrity.

🚀 Double Tap ❤️ For Part-3

❤22

4.27K views12:39