💼 Top 20 Frequently Asked Data Analyst Interview Questions

🧠 1) Can you walk me through the tools you use for data analysis?
👉 Answer: Absolutely! For data extraction I use SQL to query databases like MySQL and PostgreSQL. For cleaning and analysis, Python with pandas and NumPy is my go-to. Excel for quick pivots and Power BI/Tableau for interactive dashboards. I pick the right tool based on data size and stakeholder needs.

🎯 2) Write a SQL query to find the 2nd highest salary from employees table.
👉 Answer:
SELECT MAX(salary) as second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
Follow-up: Or using window functions: DENSE_RANK() OVER (ORDER BY salary DESC)

📊 3) Explain INNER JOIN vs LEFT JOIN with a business example.
👉 Answer: INNER JOIN gives only matching records. LEFT JOIN gives all from left table + matches from right.
Example: Customer orders analysis - LEFT JOIN keeps customers with zero orders to see churn patterns.

🔍 4) How would you handle missing values in a sales dataset?
👉 Answer: Step 1: df.isnull().sum() to assess impact. Step 2: For numbers - impute median (df.fillna(df.median())). For categories - mode. Step 3: Flag imputed values for transparency. Never drop >5% without business justification.

🧩 5) What's pandas groupby() and write an example?
👉 Answer:
# Sales by region + month
df.groupby(['region', 'month'])['revenue'].agg({
'mean': 'mean',
'total': 'sum',
'records': 'count'
}).round(2)
Split -> Apply -> Combine pattern!

📈 6) When would you normalize vs denormalize a database?
👉 Answer: Normalize for transactional systems (OLTP) to save storage. Denormalize for analytics (OLAP) for faster queries. Example: Star schema with fact/dimension tables.

🔢 7) VLOOKUP vs INDEX+MATCH - which is better and why?
👉 Answer: INDEX+MATCH wins! VLOOKUP breaks if columns shift and only looks right.
=INDEX(sales_range, MATCH(A2, id_range, 0))
Dynamic, safer, 2-way lookup.

📉 8) Difference between COUNT() vs COUNT(column_name)?
👉 Answer: COUNT(): Total rows including NULLs. COUNT(column): Non-null values only. Use COUNT() for total records, COUNT(sales) to exclude null sales.

⚙️ 9) How do you identify and remove duplicates in pandas?
👉 Answer:
# Find duplicates
dupe_count = df.duplicated(subset=['email']).sum()
print(f"Found {dupe_count} duplicates")

# Remove (keep first)
df_clean = df.drop_duplicates(subset=['email'], keep='first')
Always check business logic first!

🧠 10) Name 4 SQL aggregate functions with a practical example.
👉 Answer:
SELECT
dept,
COUNT() as headcount,
AVG(salary) as avg_salary,
MAX(salary) as top_earner,
SUM(salary) as payroll
FROM employees
GROUP BY dept;
📊 11) Sales dropped 20% last quarter. Walk me through your analysis.
👉 Answer: Framework:
1️⃣ Segment - Product/Category/Region/Customer
2️⃣ Trends - YoY, MoM, seasonality
3️⃣ Funnel - Where drop occurs
4️⃣ External - Competitor pricing, marketing
Dashboard: Drill-down + alerts for anomalies.

🎯 12) What's the difference between Data Analyst and Data Scientist?
👉 Answer: DA: SQL/Excel/Dashboards = 'What happened?' DS: ML/Python/R = 'What will happen?'
Analogy: DA = Rearview mirror, DS = Crystal ball. Most value from clean DA first!

🔍 13) Write a SQL window function to rank salaries by department.
👉 Answer:
SELECT name, dept, salary,
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) as dept_rank
FROM employees;
🧩 14) How do you create a pivot table showing sales by region/month?
👉 Answer: Excel: Insert -> PivotTable -> Rows: Region -> Columns: Month -> Values: Sum of Sales -> Slicers for filters. Power BI: Drag-drop + matrix visual.

📈 15) Explain correlation vs causation with an example.
👉 Answer: Classic: Ice cream sales correlate with drownings (both peak summer)

Correlation ≠ Causation. Need experiments to prove cause-effect.