Data Analyst Interview Questions 2026

Sample Answer

SELECT c.customer_name, SUM(o.order_value) AS total_value FROM customers c JOIN orders o ON c.id = o.customer_id WHERE o.order_date >= DATEADD(MONTH, -6, GETDATE()) GROUP BY c.customer_name ORDER BY total_value DESC LIMIT 5. This query joins customers with orders, filters to the last 6 months, aggregates by customer, and sorts descending. In production, you would add indexes on order_date and customer_id for performance. Mention that you would also consider using window functions like ROW_NUMBER() for more complex ranking scenarios.

Sample Answer

INNER JOIN returns only rows with matching values in both tables. LEFT JOIN returns all rows from the left table and matched rows from the right table (NULLs for non-matches). RIGHT JOIN is the reverse. FULL OUTER JOIN returns all rows from both tables, with NULLs where there is no match. In practice, INNER JOIN and LEFT JOIN cover 95% of use cases. LEFT JOIN is essential for finding records without matches, such as customers who have never placed an order. Always consider the performance implications of each join type on large datasets.

Sample Answer

WHERE filters rows before grouping (operates on individual rows), while HAVING filters groups after aggregation (operates on aggregated results). For example, WHERE salary > 50000 filters individual employees, while HAVING COUNT(*) > 5 filters groups with more than 5 members. WHERE cannot use aggregate functions, but HAVING can. For performance, always filter as early as possible with WHERE to reduce the data that needs to be grouped. Use HAVING only when you need to filter on aggregated values like SUM, COUNT, AVG, or MAX.

Sample Answer

First, understand why data is missing: is it Missing Completely At Random (MCAR), Missing At Random (MAR), or Missing Not At Random (MNAR)? Options include: deletion (listwise or pairwise) if missing percentage is small and MCAR; imputation with mean, median, or mode for numerical data; forward-fill or backward-fill for time-series data; and advanced methods like KNN imputation or MICE for complex patterns. Always document your approach and its impact on analysis. Never silently drop missing data, as it can introduce bias. Create a missing data report showing the percentage and pattern of missingness across variables.

Sample Answer

A pivot table summarizes large datasets by reorganizing data into rows, columns, and values with aggregation functions. Use pivot tables to quickly summarize sales by region and product, analyze trends across time periods, compare performance metrics across categories, or create cross-tabulations. In Excel, use Insert > PivotTable, drag fields to Rows, Columns, and Values areas. Best practices include: refreshing data regularly, using slicers for interactive filtering, grouping dates by month or quarter, and using calculated fields for custom metrics. Pivot tables are the single most important Excel skill for data analysts.

Sample Answer

VLOOKUP searches for a value in the first column of a range and returns a value from a specified column. INDEX-MATCH combines INDEX (returns a value at a given position) with MATCH (finds the position of a value). INDEX-MATCH is preferred because it can look left (VLOOKUP only looks right), handles column insertions without breaking, is faster on large datasets, and allows both row and column matching. XLOOKUP in newer Excel versions combines the simplicity of VLOOKUP with the flexibility of INDEX-MATCH, supporting exact, approximate, and wildcard matches with built-in error handling.

Sample Answer

Correlation measures the statistical relationship between two variables, ranging from -1 to +1. Causation means one variable directly causes changes in another. Correlation does not imply causation because of confounding variables, reverse causality, or coincidence. For example, ice cream sales and drowning deaths are correlated, but both are caused by summer heat (confounding variable). To establish causation, you need controlled experiments (A/B tests), randomized trials, or careful causal inference methods like instrumental variables or regression discontinuity. In business analytics, always be cautious about claiming causation from observational data.

Sample Answer

Match the chart to the message: use bar charts for comparisons across categories, line charts for trends over time, scatter plots for relationships between two variables, pie charts only for parts-of-a-whole with few categories, histograms for distribution shapes, and box plots for comparing distributions. Avoid 3D charts, dual-axis charts (they often mislead), and truncated y-axes. Follow Edward Tufte's principle: maximize data-ink ratio. Every element should serve the data. Use color purposefully to highlight key findings, not to decorate. For dashboards, use consistent color schemes and provide context with benchmarks or targets.

Sample Answer

SELECT month, sales, SUM(sales) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM monthly_sales. Window functions perform calculations across rows related to the current row without collapsing them. Key window functions include ROW_NUMBER() for ranking, RANK() and DENSE_RANK() for handling ties, LAG() and LEAD() for comparing with previous or next rows, and NTILE() for distributing rows into buckets. The PARTITION BY clause resets the window for each group, essential for calculating running totals per category or department.

Sample Answer

A p-value is the probability of observing results as extreme as the actual results, assuming the null hypothesis is true. A p-value below the significance level (typically 0.05) means we reject the null hypothesis. However, a low p-value does not prove practical significance. A p-value of 0.001 in an A/B test might represent a statistically significant but practically meaningless 0.01% conversion lift. Always pair p-values with effect sizes and confidence intervals. Consider sample size: with large enough samples, any tiny difference becomes statistically significant. The p-value is a tool for decision-making, not a proof of truth.

Sample Answer

A primary key uniquely identifies each record in a table and cannot be NULL. A foreign key is a column in one table that references the primary key of another table, establishing a relationship between them. For example, in an orders table, order_id is the primary key and customer_id is a foreign key referencing the customers table. Foreign keys enforce referential integrity, preventing orphaned records. In practice, always index foreign keys for join performance. Composite primary keys use multiple columns together as the unique identifier, common in junction tables for many-to-many relationships.

Sample Answer

An A/B test compares two versions of something (webpage, email, feature) by randomly splitting users into control and treatment groups. To determine sample size, you need: baseline conversion rate, minimum detectable effect (the smallest improvement worth detecting), statistical significance level (typically 95%), and statistical power (typically 80%). Use the formula or online calculators. A common mistake is stopping the test early when results look significant, leading to false positives. Run the test for the full predetermined duration. Account for novelty effect and seasonality. After the test, check for segment-level effects that the aggregate might mask.

Sample Answer

ETL (Extract, Transform, Load) extracts data from sources, transforms it in a staging area, then loads it into the destination warehouse. ELT (Extract, Load, Transform) loads raw data first, then transforms it using the warehouse's computing power. ELT is gaining popularity with modern cloud warehouses like Snowflake and BigQuery that have massive compute capability. ETL is better when you need to clean sensitive data before loading or when your warehouse has limited compute. In practice, most modern data stacks use ELT with tools like dbt for transformation, Fivetran or Airbyte for extraction, and a cloud warehouse for storage and compute.

Sample Answer

Database normalization organizes data to reduce redundancy and improve integrity through a series of normal forms. First Normal Form (1NF) eliminates repeating groups. Second Normal Form (2NF) removes partial dependencies. Third Normal Form (3NF) removes transitive dependencies. Normalization reduces data anomalies (update, insert, delete anomalies) and saves storage. However, highly normalized databases require more joins, which can impact query performance. In analytics, denormalized tables (star schema, snowflake schema) are often preferred for faster queries. The right level of normalization depends on whether the use case prioritizes write integrity (OLTP) or read performance (OLAP).