🎯 Advanced Data Analyst Mock Interview Questions (With Answers) 🚀

📊 1️⃣ A dashboard suddenly shows incorrect numbers. How would you troubleshoot it?

✅ Strong Answer:
“I would troubleshoot step-by-step:
1. Verify source data
2. Check recent ETL/data pipeline changes
3. Validate SQL queries and joins
4. Check filters and calculations in dashboard
5. Compare results with raw database values

This helps isolate whether the issue is from data ingestion, transformation, or visualization.”

🧠 2️⃣ Difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?

✅ Answer:
ROW_NUMBER → No duplicate rank, No skips rank
RANK → Yes duplicate rank, Yes skips rank
DENSE_RANK → Yes duplicate rank, No skips rank

Example:
SELECT salary,
RANK() OVER(ORDER BY salary DESC)
FROM employees;

📈 3️⃣ What is the difference between OLTP and OLAP?

✅ Answer:
OLTP
- Transactional systems
- Fast inserts/updates
- Normalized data
- Example: Banking app

OLAP
- Analytical systems
- Complex queries
- Denormalized data
- Example: Power BI dashboard

📊 4️⃣ How do you optimize a slow SQL query?

✅ Strong Answer:
“I would:
- Avoid SELECT
- Use indexes properly
- Filter early with WHERE
- Avoid unnecessary joins
- Analyze execution plan using EXPLAIN
- Use CTEs/window functions carefully”

🧠 5️⃣ Explain Primary Key vs Foreign Key

✅ Answer:
- Primary Key uniquely identifies each row
- Foreign Key creates relationship between tables

Example:
customer_id in customers → Primary Key
customer_id in orders → Foreign Key

📈 6️⃣ What is data cleaning?

✅ Answer:
“Data cleaning means handling:
- Missing values
- Duplicates
- Incorrect formats
- Inconsistent records

It improves data quality before analysis.”

📊 7️⃣ What are the most important SQL concepts for a data analyst?

✅ Answer:
- Joins
- Aggregations
- Window functions
- Subqueries & CTEs
- Date functions
- NULL handling

🧠 8️⃣ Explain a situation where you used data to solve a business problem

✅ Strong Answer:
“I analyzed customer purchase patterns and identified products with low repeat sales. Based on the analysis, targeted campaigns were suggested, which improved customer retention.”

📈 9️⃣ Difference between UNION and UNION ALL

✅ Answer:
- UNION removes duplicates
- UNION ALL keeps duplicates and is faster

📊 🔟 How do you measure dashboard performance?

✅ Answer:
“I check:
- Query execution time
- Dashboard load speed
- Number of visuals
- Data model optimization
- DAX/query efficiency”

🧠 1️⃣1️⃣ What is cardinality in databases?

✅ Answer:
Cardinality defines relationship between tables:
- One-to-One
- One-to-Many
- Many-to-Many

Example: One customer → many orders.

📈 1️⃣2️⃣ Explain ETL Process

✅ Answer:
- Extract → collect data
- Transform → clean/process data
- Load → store into warehouse/database

📊 1️⃣3️⃣ What is the difference between a view and a table?

✅ Answer:
- Table stores physical data
- View is a virtual query result

🧠 1️⃣4️⃣ How would you identify trends in sales data?

✅ Strong Answer:
“I would use:
- Time-series analysis
- Running totals
- Month-over-month growth
- Moving averages
- Visualization dashboards”

📈 1️⃣5️⃣ Explain Star Schema

✅ Answer:
Star schema contains:
- One fact table
- Multiple dimension tables

Used heavily in:
- Data warehouses
- Power BI models

⭐ Most Important Interview Advice
Interviewers test:
- SQL logic
- Business understanding
- Communication skills
- Problem-solving approach not just syntax.

🚀 Double Tap ❤️ For More