Data Analyst Interview Questions (SQL, Excel & Insights)

What Data Analyst Interviews Assess

Data analyst interviews test technical skills across SQL interview questions analyst scenarios, Excel data analysis interview competency, and visualization tool expertise (Tableau, Power BI). Companies probe how you extract insights from messy datasets, write efficient queries joining multiple tables, and communicate findings to non-technical stakeholders. You’ll face scenario-based challenges requiring you to identify trends, calculate business metrics, and recommend actions based on data. Looking to prepare across other tech roles? Explore our complete technical interview resources.

These data analyst interview questions cover SQL fundamentals (joins, aggregations, window functions), Excel techniques (pivot tables, VLOOKUP, conditional formatting), Tableau dashboard questions testing visualization best practices, and business insights analysis requiring you to translate raw data into actionable recommendations. Modern data analyst roles emphasize storytelling with data, understanding business context over pure technical manipulation, and building dashboards that drive decisions rather than just display numbers.

SQL Queries & Data Extraction

Q: Explain the difference between INNER JOIN, LEFT JOIN, and FULL OUTER JOIN.

INNER JOIN returns only matching rows from both tables. LEFT JOIN returns all rows from the left table plus matching rows from the right (unmatched right rows show NULL). RIGHT JOIN returns all rows from the right table plus matches from left. FULL OUTER JOIN returns all rows from both tables, filling unmatched sides with NULL. Use INNER JOIN when you need only matching records (orders with customers), LEFT JOIN when preserving all left table records matters (all customers even without orders).

Q: How do you find duplicate records in a table?

Use GROUP BY with HAVING COUNT(*) > 1 to identify duplicates. For example: SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1 finds duplicate email addresses. To see all duplicate rows with details, use window functions: SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) as rn FROM users) WHERE rn > 1. This shows second and subsequent occurrences of each duplicate.

Q: What are window functions and when would you use them?

Window functions perform calculations across row sets related to the current row without collapsing results like GROUP BY. Common functions include ROW_NUMBER() for ranking, LAG()/LEAD() for accessing previous/next rows, and SUM() OVER() for running totals. Use them for calculating moving averages, finding top N per category, or computing differences between consecutive rows. Example: SUM(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) calculates 7-day moving average.

Q: How do you optimize a slow SQL query?

First, use EXPLAIN to understand the query execution plan identifying bottlenecks. Add indexes on columns used in WHERE clauses, JOIN conditions, and ORDER BY. Avoid SELECT * retrieving only needed columns. Replace subqueries with JOINs when possible. For large aggregations, consider materialized views or pre-aggregated tables. Partition large tables by date or category. Ensure statistics are up-to-date for the query optimizer to make good decisions.

💡 Pro tip: Interviewers test SQL understanding through real business scenarios. “Find customers who purchased in Q1 but not Q2” reveals whether you understand self-joins, NOT EXISTS, or EXCEPT clauses versus just memorizing syntax.

Excel Functions & Analysis

Tableau Dashboards & Visualization

What’s the difference between data extract and live connection in Tableau?

Explain Level of Detail (LOD) expressions and when to use them.

How do you optimize dashboard performance in Tableau?

Business Insights & Communication

Q: How do you approach analyzing a new dataset?

Start with exploratory data analysis (EDA): check data types, distributions, missing values, and outliers. Calculate basic statistics (mean, median, range) for numeric columns and frequency counts for categorical data. Visualize distributions with histograms and relationships with scatter plots. Identify data quality issues early including duplicates, inconsistent formats, or logical errors. Understand business context by asking stakeholders what decisions this data informs. Document assumptions and data limitations before detailed analysis.

Q: Explain how you would calculate month-over-month growth rate.

Month-over-month growth compares current month to previous month. Formula: (Current Month - Previous Month) / Previous Month * 100. In SQL, use LAG window function: SELECT month, revenue, (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month) * 100 as mom_growth FROM sales. Handle edge cases like division by zero when previous month has zero revenue. For seasonality, also calculate year-over-year growth comparing same months across years.

Q: How do you communicate findings to non-technical stakeholders?

Lead with business impact, not technical methods. Start with the recommendation or key insight, then support with data. Use simple visualizations avoiding complex charts non-technical audiences struggle to interpret. Avoid jargon like “regression analysis” instead say “found relationship between X and Y.” Provide context comparing to targets, previous periods, or industry benchmarks. Anticipate questions about methodology but keep technical details brief unless asked. Focus on actionable next steps rather than just reporting numbers.

Q: What metrics would you track for an e-commerce business?

Revenue metrics: total revenue, average order value (AOV), revenue per customer. Customer metrics: customer acquisition cost (CAC), customer lifetime value (CLV), retention rate, churn rate. Conversion metrics: conversion rate, cart abandonment rate, checkout completion rate. Traffic metrics: website visitors, traffic sources, bounce rate. Product metrics: best sellers, inventory turnover, product return rate. Profitability: gross margin, contribution margin by product. Choose metrics aligned with business goals like growth versus profitability focus.

⚠️ Common mistake: Creating dashboards without understanding who uses them and for what decisions. I’ve seen analysts build complex visualizations nobody looks at because they didn’t validate requirements with actual users first.

SQL & Analytics Challenges

❓ FAQ

Final Thoughts

Mastering data analyst interview questions requires balancing technical skills with business understanding. The best preparation combines practicing SQL queries on real datasets, building dashboards that answer actual business questions, and learning to communicate insights clearly to non-technical audiences. Focus on understanding why certain analyses matter to business decisions rather than just memorizing tool features.

Companies value data analysts who translate data into action, not just create reports. Your preparation should include working with messy real-world data, calculating business metrics like retention and growth rates, and presenting findings effectively. Build a portfolio demonstrating complete analysis projects from data extraction through insight communication rather than just showcasing technical skills in isolation.