Data Analyst Interview Questions & Answers

Sample Answer

COUNT(*) counts all rows including those with NULL values—useful for total record counts. COUNT(column) counts non-NULL values in that column, so it may be less than COUNT(*) if NULLs exist. COUNT(DISTINCT column) counts unique non-NULL values, useful for understanding cardinality. For example, in a sales table: COUNT(*) gives total transactions, COUNT(customer_id) gives transactions with known customers, and COUNT(DISTINCT customer_id) gives how many unique customers made purchases. Choosing the right count depends on the question—total orders vs. unique customers are very different metrics.

Sample Answer

Marketing wanted to increase ad spend on our highest-converting channel based on last-click attribution. When I analyzed the full customer journey, I discovered that channel was actually the last step after customers had been nurtured through content and email—the real acquisition came from organic search. I built a multi-touch attribution model showing the actual path to purchase. This changed how we allocated budget, increasing content investment by 30% and reducing the "high-converting" channel spend. Within six months, customer acquisition cost dropped 15% while volume stayed constant. The insight was valuable because it challenged a widely-held assumption with data.

Sample Answer

I'd avoid statistical jargon and focus on what it tells us. I might say: "We wanted to understand what drives customer spending. Regression analysis lets us untangle multiple factors at once—like age, income, and purchase history—to see which ones actually matter. We found that purchase frequency is the strongest predictor of total spending, more than income or demographics. For every additional purchase per month, customers spend about $50 more. This helps us focus on increasing purchase frequency rather than targeting high-income customers." I'd use visualizations showing the relationship and always connect to actionable implications.

Sample Answer

SELECT customer_id, SUM(order_amount) as total_revenue FROM orders WHERE order_date >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months' AND order_date < DATE_TRUNC('quarter', CURRENT_DATE) GROUP BY customer_id ORDER BY total_revenue DESC LIMIT 5. I use DATE_TRUNC for clean quarter boundaries. The WHERE clause filters to completed orders in the target period. GROUP BY aggregates per customer, and ORDER BY with DESC puts highest first. If there might be ties at the cutoff, I might use RANK() instead to include all tied records. I'd also consider whether "revenue" means gross or net, and whether we should exclude refunds or certain order types.

Sample Answer

First, I understand why data is missing—is it random, systematic, or informative? Random missing data might be imputable; systematic missing data requires understanding the cause. For analysis, options include: excluding incomplete records (if missingness is random and limited), imputation with mean/median (simple but can distort distributions), model-based imputation (more sophisticated), or treating missing as its own category (if missingness is meaningful). I document missing data clearly in analyses. For reporting, I note sample sizes and what was excluded. Prevention is better—I work with data engineers to fix collection issues. I never silently drop data or assume it's random without investigation.

Sample Answer

A line chart is typically best for time series—it shows trends and patterns clearly. I'd use time on the x-axis, sales on the y-axis, with consistent intervals. For multiple categories, I'd use different lines with a clear legend, limiting to 4-5 series to avoid clutter. I'd consider whether to show absolute values or percentage change depending on the message. If seasonality matters, I might include year-over-year comparison. For presentation, I'd highlight key events or anomalies with annotations. I'd avoid 3D effects, pie charts for trends, or truncated y-axes that exaggerate changes. The goal is clarity—the right insight should be obvious at a glance.

Sample Answer

I analyzed two years of clickstream data—about 500 million records—to understand user journeys on our website. Standard tools couldn't handle the volume, so I used SQL on our data warehouse with efficient query design: filtering early, using appropriate indexes, and sampling for exploratory analysis before running full queries. I partitioned analysis by user segments to make it manageable. For visualization, I aggregated data appropriately rather than trying to plot everything. The project revealed that 70% of conversions came from just 3 common paths, allowing UX to optimize those journeys specifically. Working with big data taught me the importance of efficient query design and starting with clear questions.

Sample Answer

For lookups: VLOOKUP, INDEX-MATCH (more flexible), and XLOOKUP in newer versions. For aggregation: SUMIFS, COUNTIFS, and AVERAGEIFS for conditional calculations. For text: CONCATENATE/CONCAT, LEFT/RIGHT/MID for parsing, and TRIM for cleaning. For logic: IF, IFS, and nested conditions. For data handling: UNIQUE, FILTER, and SORT in dynamic arrays. Pivot tables are essential for quick analysis. I also use Power Query for data cleaning and transformation when handling multiple sources. The specific functions depend on the task, but I prioritize approaches that are maintainable and auditable—complex nested formulas are often better rebuilt as simpler steps.

Sample Answer

Validation is essential—I've learned to be skeptical of my own work. I check logic by testing with simple cases where I know the answer. I use sanity checks: does the order of magnitude make sense? Are totals what they should be? I cross-reference with other data sources when possible. For complex analyses, I approach the problem multiple ways to see if results converge. I have colleagues review methodology and results, explaining my approach to catch assumptions I've made implicitly. I also check for common errors: join explosions, missing filters, timezone issues, and double-counting. Documentation helps—writing down steps forces clearer thinking.

Sample Answer

First, verify the data—is this a real change or a measurement issue (broken tracking, reporting delay, definition change)? If real, determine scope: which segments, regions, or products are affected? Check timing: does it correlate with deployments, marketing changes, or external events? Decompose the metric: if revenue dropped, is it fewer transactions, lower order values, or both? Compare to historical patterns—is this within normal variation or truly anomalous? Generate hypotheses ranked by likelihood and test them with data. Communicate what you know and don't know to stakeholders early. The goal is finding root cause, but providing regular updates prevents panic and builds trust.

Sample Answer

I'm proficient in Tableau for interactive dashboards—I've built executive dashboards with drill-down capabilities and self-service analytics for business users. I use Python (matplotlib, seaborn, plotly) for statistical visualizations and custom charts not available in BI tools. Power BI for Microsoft-heavy environments. Excel for quick ad-hoc visualizations. I choose tools based on audience and purpose: Tableau for stakeholder-facing dashboards, Python for analysis and reports, Excel for collaborative work. Good visualization principles matter more than tools—I focus on clarity, appropriate chart types, and actionable insights regardless of platform.

Sample Answer

I have several questions: What does the data infrastructure look like—what tools and databases will I work with? What types of analysis are most common in this role—ad hoc requests, dashboards, deep dives? How does the analytics team work with stakeholders—are you embedded in business teams or centralized? How is the data quality here—are there known challenges? What would success look like in the first six months? And what do you enjoy most about working here?