Data Analyst Interview Questions

Core Overview

Prep SQL joins, window functions, statistical testing, Excel dashboard decisions, and stakeholder metrics communication.

Reviewed on: 2026-08-04Reviewed by: ResumeLoopAI Editorial Team

Ready to test your knowledge?

Launch a focused practice session to review questions without distraction.

Show Practiced Only

beginnerSQL Querying & Joins

What is the difference between INNER JOIN, LEFT JOIN, and CROSS JOIN in SQL?

Direct Answer

INNER JOIN returns matching rows from both tables. LEFT JOIN returns all rows from the left table and matching rows from the right. CROSS JOIN returns the Cartesian product of both tables.

Detailed Explanation

SQL joins combine data from separate tables based on key relations:

INNER JOIN: Filters rows from both tables, returning only records where the join condition matches. Unmatched rows are excluded.

LEFT (OUTER) JOIN: Returns *all* rows from the left table, and matching values from the right table. If no match is found, columns from the right table are populated with NULL.

CROSS JOIN: Produces the Cartesian product. It joins every row in the first table to every row in the second table, resulting in N * M rows. Use this for combinations matrix builders. Performance: CROSS JOIN is highly CPU and memory intensive on large tables.

Code Example

SELECT customers.name, orders.id 
FROM customers 
LEFT JOIN orders ON customers.id = orders.customer_id;

Common Interview Pitfalls

Applying filters to the right-side table inside the WHERE clause on a LEFT JOIN, which converts the operation to an INNER JOIN implicitly (use filters inside ON clause instead).
Accidentally triggering a CROSS JOIN by listing multiple tables in a FROM clause without a WHERE join condition.

Rate your confidence:

intermediateSQL Querying & Joins

Compare window functions with GROUP BY aggregations in SQL.

Direct Answer

GROUP BY aggregates rows into a single summary row, collapsing the detailed records. Window functions calculate values over a set of rows while preserving individual row identity.

Detailed Explanation

Both operations perform aggregations, but they differ in row retention:

GROUP BY: Groups rows with matching column keys together, returning exactly one summary row per group. You lose access to the underlying detail records in the select statement.

Window Functions (using the `OVER` clause): Perform calculations over a partitioned set of rows (the "window"), but do *not* collapse the individual rows. Each detail row is retained in the final output along with the computed aggregate value. Common window functions include ROW_NUMBER(), RANK(), DENSE_RANK(), SUM(), and AVG().

Code Example

SELECT employee_id, department_id, salary,
       AVG(salary) OVER(PARTITION BY department_id) as dept_avg_salary
FROM employees;

Common Interview Pitfalls

Using window functions inside the WHERE clause (window functions are processed *after* the WHERE clause; use a CTE or subquery to filter window outputs instead).
Confusing `RANK()` and `DENSE_RANK()` (RANK leaves gaps in numbering after ties, whereas DENSE_RANK assigns consecutive numbers).

Rate your confidence:

beginnerSQL Querying & Joins

Compare Common Table Expressions (CTEs) with Subqueries in SQL.

Direct Answer

CTEs (`WITH` statements) define temporary result sets, improving query readability and reuse. Subqueries are nested statements that can be hard to read and debug.

Detailed Explanation

Both CTEs and subqueries supply temporary results for query engines, but they differ in readability and capability:

Subqueries: Nested directly inside other queries (SELECT, FROM, WHERE). In complex queries, deep nesting makes code difficult to read, maintain, and debug.

CTEs (Common Table Expressions): Defined at the top of the query using the WITH keyword. They break complex logic into named, modular blocks that read sequentially from top to bottom. They can reference other previously defined CTEs and support recursion (WITH RECURSIVE), which is essential for walking hierarchical tree data.

Code Example

WITH DeptRevenue AS (
    SELECT dept_id, SUM(amount) as total_rev 
    FROM sales GROUP BY dept_id
)
SELECT * FROM DeptRevenue WHERE total_rev > 100000;

Common Interview Pitfalls

Assuming that CTEs are always optimized better than subqueries (in older databases, CTEs were materialized as temp tables, slowing down performance; modern query planners optimize both similarly).
Forgetting the comma separator between multiple CTE declarations in a single query.

Rate your confidence:

advancedSQL Querying & Joins

How do database indexes speed up queries, and how do you analyze an EXPLAIN execution plan?

Direct Answer

Indexes are data structures (like B-Trees) that allow fast row lookups without scanning the entire table. `EXPLAIN` displays the query planner's execution path.

Detailed Explanation

Database performance depends on access paths:

Indexes: Data structures (typically B-Trees) that store ordered keys pointing to table row locations. Instead of scanning millions of rows (Sequential Scan), the engine walks the B-Tree (Index Scan), reducing I/O operations from O(N) to O(log N).

EXPLAIN Clause: Prepending EXPLAIN or EXPLAIN ANALYZE to a query outputs the execution plan chosen by the query optimizer. Key terms to analyze include:

Seq Scan (Sequential Scan): Bad on large tables. Scans all rows.

Index Scan / Index Only Scan: Good. Uses index keys.

Filter: Checks conditions. Look out for unindexed column filters.

Actual time: Shows execution costs (only with EXPLAIN ANALYZE).

Code Example

EXPLAIN ANALYZE 
SELECT * FROM users WHERE email = 'test@example.com';

Common Interview Pitfalls

Creating indexes on columns with low cardinality (like boolean active states), which query planners ignore in favor of Seq Scans.
Performing functions or casts on indexed columns (e.g. `WHERE LOWER(name) = 'foo'`), which disables the index (requires a functional index instead).

Rate your confidence:

advancedSQL Querying & Joins

What is a correlated subquery, and how does it compare to a join regarding query efficiency?

Direct Answer

A correlated subquery is a nested query that references columns from the outer query, executing once for every row processed. Joins are generally more efficient.

Detailed Explanation

A correlated subquery cannot be executed independently of the outer query because it references a column belonging to the outer table (e.g., matching a child record ID to the outer row's ID).

Efficiency: In worst-case scenarios, a correlated subquery executes its nested query once for *every single row* returned by the outer query, leading to O(N^2) complexity. Joins (combined with indexes) are processed in bulk using hashing (Hash Join) or sorted lists (Merge Join) by the query planner, which yields significantly faster execution times on large datasets.

Code Example

// Find employees earning more than their department's average salary
SELECT e1.name, e1.salary
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary) 
    FROM employees e2 
    WHERE e2.dept_id = e1.dept_id // Correlated reference
);

Common Interview Pitfalls

Using correlated subqueries on large tables without index support on matching columns, causing database performance freezes.
Writing correlated subqueries where a standard LEFT JOIN with a grouped subquery would retrieve the same output in a single pass.

Rate your confidence:

intermediateSQL Querying & Joins

How do SQL aggregate functions handle NULL values, and how do you resolve missing records?

Direct Answer

Aggregate functions (SUM, AVG, MIN, MAX) ignore NULL values. `COUNT(*)` counts all rows, while `COUNT(column)` ignores NULL values. Use `COALESCE` to default values.

Detailed Explanation

SQL aggregates handle NULL in specific ways:

SUM, AVG, MIN, MAX: Ignore NULL values entirely. If a column contains 10, 20, and NULL, AVG computes (10+20)/2 = 15. It does *not* treat NULL as zero.

`COUNT(*)`: Counts every row in the table, including rows containing NULL values or duplicates.

`COUNT(column)`: Counts only rows where the specified column is not NULL.

Resolution: To prevent NULL propagation from causing errors, wrap columns in COALESCE(column, fallback_value). COALESCE returns the first non-NULL argument from its parameter list.

Code Example

// If commission is NULL, defaults to 0 to prevent calculation failures
SELECT SUM(salary + COALESCE(commission, 0)) as total_compensation 
FROM employees;

Common Interview Pitfalls

Using checking operators like `= NULL` in queries (SQL requires `IS NULL` or `IS NOT NULL` because comparisons to NULL yield `UNKNOWN`).
Forgetting that `AVG` calculation denominators will exclude NULL records, potentially skewing metric reports.

Rate your confidence:

intermediateData Cleaning & Manipulation

Explain Pandas methodologies for cleaning duplicates, outliers, and missing values.

Direct Answer

Clean duplicates using `drop_duplicates()`. Clean missing values using `fillna()` or `dropna()`. Filter outliers using Z-score or IQR thresholds.

Detailed Explanation

Data cleaning is the initial step in analysis:

Duplicates: Identify with duplicated() and remove with df.drop_duplicates(subset=[...]).

Missing Values (NaN):

Drop: Use df.dropna() (removes whole rows/columns; use with care).

Imputation: Fill with static values or statistics (e.g., df["age"].fillna(df["age"].median(), inplace=True)).

Outliers: Identify using statistical thresholds:

Interquartile Range (IQR): Filter rows outside [Q1 - 1.5*IQR, Q3 + 1.5*IQR] bounds.

Z-score: Filter rows where standard deviations from the mean exceed a threshold (typically |z| > 3).

Code Example

import pandas as pd
# Remove duplicate records
df = df.drop_duplicates()

# Impute missing values with column median
df['income'] = df['income'].fillna(df['income'].median())

Common Interview Pitfalls

Imputing missing values with the mean when the dataset contains significant outliers, skewing calculations (use median instead).
Forgetting that `drop_duplicates` resets row indexing, requiring `reset_index(drop=True)` to fix row indices.

Rate your confidence:

beginnerData Cleaning & Manipulation

How do you manipulate text data and extract patterns in Pandas using string methods and regex?

Direct Answer

Access string methods using the `.str` accessor. Apply regular expressions using `.str.extract()`, `.str.contains()`, or `.str.replace()`.

Detailed Explanation

Pandas provides a vectorized .str accessor to perform string manipulations on entire columns at once, avoiding slow loops:

Case Operations: .str.lower(), .str.upper(), .str.strip().

Pattern Extraction: .str.extract(pattern) parses capturing groups from regex. E.g. extracting area codes from telephone numbers.

Filtering: .str.contains(pattern) checks if columns contain specific words or regex matches, returning a boolean mask to filter rows.

Splitting: .str.split(delimiter, expand=True) splits strings into multiple columns.

Code Example

import pandas as pd
# Extract email domains using regex
df['domain'] = df['email'].str.extract(r'@([a-zA-Z0-9.-]+)')

Common Interview Pitfalls

Applying string methods directly on a series without prefixing the `.str` accessor, throwing an AttributeError.
Failing to handle NaN values in string columns, which causes string accessor functions to return NaN instead of raising exceptions.

Rate your confidence:

beginnerData Cleaning & Manipulation

How do you cast data types in Pandas, and how do you handle parsing errors?

Direct Answer

Cast data types using `astype()`. Convert dates using `to_datetime()` and numeric values using `to_numeric()` with `errors="coerce"` configuration.

Detailed Explanation

Columns parsed from CSV or text files often default to generic object types. You must cast them to enable math calculations or date indexing:

Explicit Casting: Use df["column"].astype("int64") or "float64".

Date Parsing: Use pd.to_datetime(df["date_col"]) to enable datetime index methods (.dt.year, .dt.day_name()).

Error Handling: If a column contains unparseable values (like "N/A" strings in a numeric column), astype throws an error. Solve by using pd.to_numeric(col, errors="coerce") which replaces unparseable strings with NaN, allowing calculation progression.

Code Example

import pandas as pd
# Safe numeric conversion: replaces unparseable text with NaN
df['price'] = pd.to_numeric(df['price'], errors='coerce')

Common Interview Pitfalls

Casting float columns containing NaN values to `int64` directly (standard integers in older Pandas versions cannot represent NaN; throws a ValueError. Must use `Int64` capital-I nullable type).
Forgetting to specify the date format in `to_datetime()`, which slows down execution on large datasets.

Rate your confidence:

intermediateData Cleaning & Manipulation

Compare merge(), join(), and concat() operations in Pandas.

Direct Answer

`merge()` joins dataframes on columns or index keys. `join()` combines dataframes on index keys. `concat()` appends rows or columns along an axis.

Detailed Explanation

Pandas offers three primary operations to combine datasets:

`pd.merge(df1, df2, on="key")`: The most versatile method. Joins dataframes based on common columns (like database SQL joins). Supports inner, left, right, and outer join types.

`df1.join(df2)`: A convenience method to join dataframes on their index keys instead of columns. It behaves like a left join by default.

`pd.concat([df1, df2], axis=0)`: Appends dataframes along an axis:

axis=0: Appends rows (vertically). Common for stacking historical data.

axis=1: Appends columns (horizontally). Checks index alignment.

Code Example

import pandas as pd
# SQL-like inner join on 'customer_id' column
merged_df = pd.merge(df1, df2, on='customer_id', how='inner')

Common Interview Pitfalls

Performing horizontal concat (`axis=1`) expecting alignment when index values do not match (re-aligns rows based on index, creating duplicate, NaN-filled rows).
Merging columns with different data types (e.g. merging a string-based ID column with an integer-based ID column; throws a merge key type mismatch error).

Rate your confidence:

intermediateData Cleaning & Manipulation

Compare pivot() and melt() operations for reshaping data in Pandas.

Direct Answer

`pivot()` reshapes a dataframe from long to wide format. `melt()` unpivots a dataframe from wide to long format.

Detailed Explanation

Reshaping changes data structures to optimize aggregations or visual reports:

`pivot(index, columns, values)` (Long to Wide): Takes unique values from a column and converts them into headers for new columns. It collapses repeating records, making data wider and easier to read in spreadsheets.

`melt(id_vars, value_vars)` (Wide to Long): The reverse of pivot. It takes multiple columns and collapses them into two columns: a name variable column (holding former headers) and a value column. This "tidy data" format is required for seaborn visualization or relational database inserts.

Code Example

import pandas as pd
# Wide to Long (unpivot 'quarter' columns to key-value rows)
long_df = pd.melt(df, id_vars=['store_id'], value_vars=['Q1', 'Q2', 'Q3'])

Common Interview Pitfalls

Calling `pivot()` when the target index and columns contain duplicate pairs (throws a ValueError; use `pivot_table()` instead, which aggregates duplicate values).
Forgetting to specify the `id_vars` parameter in `melt()`, which results in all columns being collapsed into rows.

Rate your confidence:

advancedData Cleaning & Manipulation

Why is vectorization preferred over iterative loops in Pandas, and how do you implement it?

Direct Answer

Vectorization offloads element-wise calculations to compiled C code (NumPy), avoiding slow Python loops. Implement using operations or `.apply()` as fallback.

Detailed Explanation

Standard Python loops (for index, row in df.iterrows()) execute calculations sequentially in Python runtime, which adds significant overhead for every row (interpreter type checks, boxing/unboxing).

Vectorization: Applies operations to entire arrays at once. Pandas delegates these computations to underlying compiled C code (via NumPy), performing element-wise calculations in parallel (SIMD instructions).

Order of Speed (Fastest to Slowest):

1. Vectorized operations (e.g. df["col_c"] = df["col_a"] + df["col_b"]).

2. Vectorized NumPy functions (e.g. np.where(condition, x, y)).

3. `.apply(lambda)`: Runs Python functions sequentially (faster than iterrows, but still slow).

4. `.iterrows()` / `.itertuples()`: Very slow.

Code Example

import numpy as np
# Fast vectorized evaluation using NumPy
df['status'] = np.where(df['sales'] > 10000, 'Elite', 'Standard')

Common Interview Pitfalls

Using `iterrows()` to perform mathematical transformations on large dataframes, causing script execution delays.
Modifying dataframes inside loops, which forces Pandas to re-allocate memory continuously.

Rate your confidence:

beginnerExcel & Dashboard Operations

Compare XLOOKUP, VLOOKUP, and INDEX-MATCH in Excel.

Direct Answer

VLOOKUP searches left-to-right only. INDEX-MATCH allows bi-directional searches. XLOOKUP is the modern, secure replacement that defaults to exact match and search direction.

Detailed Explanation

Excel lookup methods differ in flexibility, performance, and robustness:

VLOOKUP(value, table, col_index, [range]):

Cons: Searches left-to-right only (lookup column must be the first column). It requires hardcoding the column index number, meaning inserting a column in the table breaks the formula. Defaults to approximate match.

INDEX-MATCH:

Pros: Dynamic. Combines INDEX (returns a value from coordinates) and MATCH (returns a coordinate position). Can search left, right, up, or down. Column inserts do not break formulas.

XLOOKUP(value, lookup_array, return_array, [if_not_found], [match_mode]):

Pros: The modern standard. Safe syntax. It accepts separate lookup and return range arrays directly. It defaults to exact match, can search backwards, and handles errors with built-in fallback arguments.

Code Example

=XLOOKUP(A2, Employees[ID], Employees[Salary], "Not Found")

Common Interview Pitfalls

Forgetting the `FALSE` (or `0`) argument in VLOOKUP, returning incorrect values due to approximate match defaults.
Using whole-column references in INDEX-MATCH formulas on slow spreadsheets, causing Excel calculation lag.

Rate your confidence:

beginnerExcel & Dashboard Operations

How do Pivot Tables summarize data, and what is the difference between a Calculated Field and a Calculated Item?

Direct Answer

Pivot Tables aggregate raw data into summaries. Calculated Fields perform math on table columns. Calculated Items perform math on row elements.

Detailed Explanation

Pivot Tables summarize large datasets dynamically by grouping values across dimensions. When performing custom logic, you must distinguish between fields and items:

Calculated Field: Creates a new column in the Pivot Table. It executes mathematical calculations on existing column data (e.g. Revenue - Cost). It operates on the *aggregated sum* of the source data.

Calculated Item: Creates a new row inside a specific column dimension. It calculates values using individual row elements within the same field category (e.g. 2024_Sales - 2023_Sales). Use with care, as Calculated Items can slow down Pivot Table calculations on large grids.

Code Example

= Calculated Field Formula:
Revenue * 0.10 // Computes commission on total sales

Common Interview Pitfalls

Attempting to calculate averages inside a Calculated Field (e.g., `Revenue / Unit_Count` computes the sum of divisions instead of dividing the aggregate sums).
Forgetting to refresh the Pivot Table after updating the underlying source spreadsheet cells (Pivot Tables do not recalculate automatically).

Rate your confidence:

intermediateExcel & Dashboard Operations

What are dynamic arrays in modern Excel, and how does the spill behavior work?

Direct Answer

Dynamic arrays calculate values in a single formula and write them to adjacent cells. Spill behavior represents the automatic expansion into neighboring cells.

Detailed Explanation

Modern Excel (Excel 365) introduced Dynamic Arrays. Instead of copying a formula down a column, a single formula calculates a range of values and dynamically outputs them to neighboring cells:

Spill Range: The block of cells populated by a dynamic array formula. It is denoted by the # symbol (e.g. A2# references the entire spilled range).

Spill Error (`#SPILL!`): Occurs if any cell inside the expected output block is not empty (e.g. contains text or a space). Excel blocks the calculation to prevent data overwrite.

Common Functions: FILTER(), UNIQUE(), SORT(), and SEQUENCE(). These dynamically adjust their output size when the source data changes.

Code Example

=UNIQUE(A2:A100) // Spills all unique names in adjacent rows

Common Interview Pitfalls

Entering static values or typing text inside a dynamic array's spill path, throwing a `#SPILL!` calculation error.
Using old Ctrl+Shift+Enter (CSE) array methods instead of utilizing modern dynamic array formulas.

Rate your confidence:

beginnerExcel & Dashboard Operations

How does Data Validation secure spreadsheet models, and how do you build dependent dropdown lists?

Direct Answer

Data Validation restricts user inputs to specific types (lists, dates, numbers). Dependent dropdown lists use the INDIRECT function to reference source ranges.

Detailed Explanation

Data Validation protects spreadsheets from corrupt user inputs by defining cell input criteria (e.g. only allowing integers between 1 and 100, or matching a specific list of strings):

Dependent Dropdowns: To build a dropdown list whose options change based on a previous cell selection (e.g., selecting Country "USA" limits the next dropdown to US States):

1. Create named ranges matching the parent category values (e.g., define a range named "USA" containing state strings).

2. In the child data validation cell, set the criteria type to List, and enter the formula: =INDIRECT(A2) (where A2 is the parent category cell). The INDIRECT function dynamically converts the text value of A2 into the named range reference, fetching state options.

Code Example

=INDIRECT(A2) // Dynamic reference to named range matching cell A2

Common Interview Pitfalls

Naming parent categories with spaces or special characters (Excel named ranges cannot contain spaces; require replacing spaces with underscores to resolve `INDIRECT` errors).
Forgetting that Data Validation checks can be bypassed by pasting values directly into cells.

Rate your confidence:

intermediateExcel & Dashboard Operations

Describe core design principles for creating business intelligence dashboards in Tableau or PowerBI.

Direct Answer

Focus on visual hierarchy, high color contrast, minimal clutter (high data-ink ratio), and alignment to user reading patterns (F or Z pattern).

Detailed Explanation

Dashboard design must translate complex data into fast, actionable decisions:

1. Visual Hierarchy: Place critical KPIs (revenue, active users) at the top-left corner, as users read in F or Z patterns. Secondary detail grids should follow lower down.

2. High Color Contrast: Use color strategically. Avoid bright backgrounds. Limit distinct colors to 3-4 per screen. Use vibrant colors (like alert red) only to highlight targets or outliers; keep regular categories in muted grays/blues.

3. Data-Ink Ratio: Maximize the data-ink ratio (Edward Tufte principle). Strip away unnecessary borders, grid lines, three-dimensional charts, and decorative elements to draw focus to data patterns.

4. Contextual Scaling: Always include comparative benchmarks (like target markers or year-over-year deltas) to prevent users from misinterpreting raw metrics.

Code Example

// Best Practice layout structure:
Top Left: Executive Summary KPIs (Sales, Margin, Active Accounts)
Middle: Trend Charts (Line chart showing sales over time vs target)
Bottom: Detail Tables (Searchable, filterable list of transaction rows)

Common Interview Pitfalls

Using Pie Charts to compare more than 3 categories, making it difficult for the human eye to compare area sizes.
Adding excessive widgets or decorative images that distract from core data metrics.

Rate your confidence:

advancedExcel & Dashboard Operations

Compare Live Connections with Data Extracts in Tableau and PowerBI, and how to optimize dashboard speeds.

Direct Answer

Live Connections query the database in real-time. Data Extracts are static snapshots cached in memory. Optimize dashboards by hiding columns and aggregating data.

Detailed Explanation

Connecting to data sources requires balancing latency and query execution speeds:

Live Connection:

How: Executes SQL queries directly against the database on every user interaction (slicer change, tab switch).

Pros: Shows real-time database updates instantly.

Cons: Slow performance if the database is under load or unindexed.

Data Extract (Cached):

How: Imports data into the BI tool's local in-memory columnar database (Hyper/VertiPaq) via a scheduled refresh.

Pros: Fast query performance; offloads work from production databases.

Cons: Shows stale data until the next refresh.

Performance Optimization: Reduce columns (hide unused fields), pre-aggregate transactional data to the required grain, replace slow string calculations with boolean filters, and limit the number of visual widgets on a single tab page.

Code Example

// Performance optimization tip:
// Instead of importing 10M rows of raw logs, import aggregates grouped by day and region.

Common Interview Pitfalls

Configuring Live Connections to transactional databases (OLTP) for dashboard reports with millions of rows, slowing down production systems.
Importing thousands of distinct string ID columns into memory extracts, which bypasses columnar database compression benefits.

Rate your confidence:

beginnerStatistics & Probability

How do skewness and outliers affect the Mean, Median, and Mode of a distribution?

Direct Answer

The Mean is highly sensitive to outliers and pulls toward the tail of skewed distributions. The Median is robust against outliers. The Mode represents the most frequent value.

Detailed Explanation

Understanding distributions is critical for metric accuracy:

Mean (Average): Calculated by summing all values and dividing by the count. It is highly sensitive to outliers. In a right-skewed (positively skewed) distribution (like household income, where a few earners make millions), the mean is pulled right, skewing the summary upward.

Median (50th Percentile): The middle value when data is sorted. It is robust to outliers; adding a billionaire to a group of 100 people barely shifts the median. Use the median for skewed metrics.

Mode: The most frequent value. Unaffected by outliers. Useful for categorical data analysis.

Relation: In a right-skewed distribution, typically: Mean > Median > Mode. In a left-skewed (negatively skewed) distribution: Mean < Median < Mode.

Code Example

// Skewed dataset: [10, 20, 20, 30, 1000]
// Mean = 216 (Highly skewed by 1000)
// Median = 20 (Accurate center representation)

Common Interview Pitfalls

Reporting average (mean) salaries or transaction values in a highly skewed dataset without checking the median, misrepresenting typical user behaviors.
Assuming that a normal distribution is suitable for analyzing data sets that contain a significant amount of zero or negative values.

Rate your confidence:

intermediateStatistics & Probability

Explain hypothesis testing, null hypothesis, and the interpretation of p-values.

Direct Answer

Hypothesis testing evaluates assumptions about data. The Null Hypothesis states there is no effect. The p-value is the probability of seeing the data if the Null is true.

Detailed Explanation

Hypothesis testing evaluates data claims:

1. Null Hypothesis (H0): The default assumption that there is no difference, change, or effect between categories (e.g. "Option A and Option B have the same conversion rate").

2. Alternative Hypothesis (H1): The statement that there is a significant difference (e.g. "Option B conversions are higher").

3. p-value: The probability of observing a difference at least as extreme as the one in your sample data, assuming the Null Hypothesis is true. If the p-value is below your significance level (typically alpha = 0.05), you reject H0, concluding that the difference is statistically significant. A low p-value does *not* prove H1 is true; it simply states that H0 is highly unlikely.

Code Example

import scipy.stats as stats
# Perform two-sample T-test
t_stat, p_val = stats.ttest_ind(group_a, group_b)
if p_val < 0.05:
    print("Statistically significant difference detected")

Common Interview Pitfalls

Interpreting the p-value as the probability that the null hypothesis is true (the p-value is calculated *assuming* the null is true).
Declaring success solely on a low p-value without checking the physical effect size (with large datasets, tiny, useless differences can still yield low p-values).

Rate your confidence:

advancedStatistics & Probability

How do you design an A/B test, and what factors determine the required sample size?

Direct Answer

Design an A/B test by isolating variables, assigning groups randomly, and defining metrics. Sample size is determined by significance level, statistical power, and MDE.

Detailed Explanation

A/B testing (split testing) compares a control group (A) with a variant group (B):

1. Design: Define your primary metric (e.g., Conversion Rate), select the significance level (typically alpha = 0.05), statistical power (beta = 0.80), and assign users randomly to prevent selection bias.

2. Sample Size Factors:

Baseline Conversion Rate: Current conversion rate of the control group.

Minimum Detectable Effect (MDE): The smallest change in conversion you care about detecting (smaller MDE requires significantly more samples).

Significance Level (alpha): Probability of Type I error. Lower alpha requires more samples.

Statistical Power (1 - beta): Probability of correctly rejecting the Null Hypothesis. Higher power requires more samples.

Code Example

# Sample size calculation logic in Python:
from statsmodels.stats.power import TTestIndPower
power_analysis = TTestIndPower()
sample_size = power_analysis.solve_power(effect_size=0.05, nobs1=None, alpha=0.05, power=0.8)

Common Interview Pitfalls

Stopping the test early once a p-value drops below 0.05 (peeking problem: increases Type I error rates; you must run the test until the calculated sample size is reached).
Running A/B tests without checking for sample ratio mismatch (SRM), which indicates assignment bias.

Rate your confidence:

beginnerStatistics & Probability

What is the difference between correlation and causation, and how do you evaluate relationships?

Direct Answer

Correlation measures the strength of linear association between two variables. Causation implies that changes in one variable directly cause changes in the other.

Detailed Explanation

A core analytics trap is confusing correlation with causation:

Correlation: Indicated by a correlation coefficient (e.g. Pearson's r between -1 and +1). A value of +0.8 shows that two variables move together (e.g. ice cream sales and sunscreen sales). It does *not* mean one causes the other.

Causation: Means changes in variable X directly cause changes in variable Y. In the example, temperature changes are the common cause (confounding variable) driving both. To prove causation, you must run controlled experiments (like A/B testing) to isolate variables and eliminate confounding factors.

Code Example

import pandas as pd
# Calculates Pearson correlation matrix
correlation_matrix = df[['sales', 'temp', 'clicks']].corr()

Common Interview Pitfalls

Assuming that a high correlation coefficient proves a causal business relationship, recommending product modifications without experimental validation.
Ignoring non-linear relationships (Pearson correlation only measures linear associations; variables with quadratic relationships can have `r = 0`).

Rate your confidence:

intermediateStatistics & Probability

What is the difference between Standard Deviation and Standard Error of the Mean?

Direct Answer

Standard Deviation measures the variability of individual data points in a sample. Standard Error of the Mean measures the variability of the sample mean.

Detailed Explanation

Both metrics measure dispersion, but they apply to different targets:

Standard Deviation (SD): Measures the spread of individual data points around the sample mean. It represents the natural variability of the population. Formula: SD = sqrt(variance).

Standard Error of the Mean (SEM): Measures the precision of your estimated sample mean. It represents how far the sample mean is likely to be from the true population mean. It decreases as the sample size (n) increases. Formula: SEM = SD / sqrt(n). SEM is used to compute confidence intervals.

Code Example

import numpy as np
# Calculate standard deviation and standard error
sd = np.std(data)
sem = sd / np.sqrt(len(data))

Common Interview Pitfalls

Reporting Standard Error of the Mean (SEM) instead of Standard Deviation (SD) to describe population spread (SEM is always smaller than SD, which can deceptively hide high data variance).
Failing to adjust degrees of freedom (`ddof=1` for sample standard deviation) when computing sample statistics.

Rate your confidence:

advancedStatistics & Probability

What is the Central Limit Theorem, and why is it fundamental to statistical analysis?

Direct Answer

The Central Limit Theorem states that the distribution of sample means approaches a normal distribution as the sample size increases, regardless of the population distribution.

Detailed Explanation

The Central Limit Theorem (CLT) is the foundation of parametric statistical tests:

The Theorem: If you take multiple independent random samples of size n from *any* population distribution (even skewed, uniform, or bimodal) and compute the mean of each sample, the distribution of those sample means will approach a normal (Gaussian) distribution as n becomes large (typically n >= 30).

Importance: It allows analysts to use normal distribution characteristics (like z-scores and confidence intervals) to make inferences about a population without knowing the underlying population distribution pattern.

Code Example

// CLT in Action:
// If you roll a 6-sided die (uniform distribution), the outcomes are flat.
// If you roll 50 dice and take the average, and repeat this 1000 times,
// the resulting averages will form a normal bell curve.

Common Interview Pitfalls

Assuming the Central Limit Theorem states that the *population* distribution becomes normal over time (only the *sampling distribution of the mean* becomes normal).
Applying parametric tests (like t-tests) to small, highly skewed samples where `n < 30` and CLT does not apply.

Rate your confidence:

intermediateBusiness Metrics & Reporting

What is cohort analysis, and how do you use it to evaluate user retention?

Direct Answer

Cohort analysis groups users based on a shared starting characteristic (e.g. signup month) and tracks their behaviors over time to evaluate retention and churn.

Detailed Explanation

Cohort analysis groups users based on a common event (usually signup date, acquisition channel, or first purchase month) and monitors their retention rates over subsequent time periods:

Retention Heatmaps: Displays the percentage of active users remaining in Month 1, Month 2, etc. A steep decline in early periods indicates onboarding issues.

Usage: It helps isolate product changes. If the Jan cohort has 20% Month-3 retention and the Feb cohort (after a UI redesign) has 40% Month-3 retention, you have proof of user engagement improvement, which would be hidden in flat, aggregated active user metrics.

Code Example

// Typical Cohort Grid representation:
Cohort  | Size  | Month 0 | Month 1 | Month 2
Jan 2026| 1000  | 100%    | 40%     | 25%
Feb 2026| 1200  | 100%    | 55%     | 38%

Common Interview Pitfalls

Analyzing aggregated Active Users (DAU/MAU) for growth without checking cohorts (active user counts can rise due to heavy advertising spend while underlying retention cohorts are failing).
Configuring cohort boundaries too broadly, blending distinct user demographics into a single cohort.

Rate your confidence:

intermediateBusiness Metrics & Reporting

How do you calculate Lifetime Value (LTV) and Customer Acquisition Cost (CAC), and what is a healthy ratio?

Direct Answer

LTV is the total net revenue expected from a customer. CAC is total marketing spend divided by new customers. A healthy LTV:CAC ratio is 3:1 or higher.

Detailed Explanation

LTV and CAC evaluate unit economics and business viability:

Customer Acquisition Cost (CAC): Total sales and marketing spend over a period divided by the number of new customers acquired during that period. Formula: CAC = total_marketing_spend / new_customers.

Customer Lifetime Value (LTV): The expected revenue generated by a single customer over their lifespan. Formula: LTV = ARPU (Average Revenue Per User) * Gross_Margin (%) / Churn_Rate.

Healthy Ratio: A standard benchmark is LTV:CAC >= 3:1. A ratio of 1:1 means you spend too much to acquire customers, risking bankruptcy. A ratio of 5:1 or higher indicates under-spending on marketing, meaning you are leaving growth opportunities on the table.

Code Example

// If CAC = $50, ARPU = $20/mo, Churn = 5%, Margin = 70%
// LTV = ($20 * 0.70) / 0.05 = $280
// Ratio = 280 / 50 = 5.6:1 (Highly viable business model)

Common Interview Pitfalls

Calculating LTV using revenue instead of gross profit (overestimates LTV because it ignores the cost of servicing customers).
Excluding salaries of sales and marketing personnel from the CAC calculation cost pool.

Rate your confidence:

beginnerBusiness Metrics & Reporting

How is Churn Rate calculated, and what is the difference between Customer Churn and Revenue Churn?

Direct Answer

Customer Churn is the percentage of customers lost in a period. Revenue Churn is the percentage of recurring revenue lost in a period. Revenue churn can be negative.

Detailed Explanation

Churn measures lost value over time:

Customer Churn: The proportion of active users who cancel their subscriptions in a period. Formula: Lost_Customers_in_Period / Total_Customers_at_Start_of_Period.

Revenue (MRR) Churn: The proportion of Monthly Recurring Revenue lost due to cancellations. Formula: Lost_MRR_in_Period / MRR_at_Start_of_Period.

Difference: Customer Churn only tracks user count, treating all customers equally. Revenue Churn weights cancellations by subscription cost. If low-paying users churn but high-paying enterprise users upgrade, you can achieve Negative Churn (meaning expansion revenue from existing users exceeds lost revenue from cancellations), which is a key growth driver.

Code Example

// Active users Jan 1 = 100. Canceled during Jan = 5.
// Churn Rate = 5 / 100 = 5% for January

Common Interview Pitfalls

Including new customers acquired during the month in the starting denominator of the churn calculation (artificially lowers the churn rate).
Averaging monthly churn rates over a year instead of calculating compound annual churn rates.

Rate your confidence:

beginnerBusiness Metrics & Reporting

What is Return on Investment (ROI) and how do you calculate it for marketing campaigns?

Direct Answer

ROI is the ratio of net profit to the cost of investment. Marketing ROI is calculated as net campaign return divided by campaign marketing costs.

Detailed Explanation

Return on Investment (ROI) evaluates the efficiency and profitability of business investments. It expresses return as a percentage:

Basic Formula: ROI = (Net_Profit / Investment_Cost) * 100.

Marketing ROI: To evaluate a marketing campaign, compare the revenue driven by the campaign to the cost of running it. Formula: (Campaign_Revenue - Campaign_Cost) / Campaign_Cost * 100.

Context: If a campaign costs $10,000 and generates $15,000 in revenue, the Net Profit is $5,000, yielding an ROI of (5000 / 10000) * 100 = 50%. To be accurate, campaign revenue should be multiplied by the gross margin percentage to reflect actual company profits rather than raw sales.

Code Example

// Campaign Cost = $5,000, Revenue = $12,000, Gross Margin = 60%
// Profit = ($12,000 * 0.60) - $5,000 = $2,200
// True ROI = (2200 / 5000) * 100 = 44%

Common Interview Pitfalls

Using gross revenue instead of gross margin in ROI calculations, overstating marketing campaign profitability.
Failing to attribute sales correctly, counting organic traffic conversions as campaign-driven returns.

Rate your confidence:

intermediateBusiness Metrics & Reporting

How do you design Key Performance Indicators (KPIs) that align with business goals?

Direct Answer

Select metrics that are specific, measurable, actionable, relevant, and time-bound. Anchor KPIs to target outcomes, not vanity actions.

Detailed Explanation

KPIs must drive business action, not just visual reporting:

1. Smart Alignment: Align KPIs directly to high-level company goals (e.g. if the goal is growth, track Net New ARR; if the goal is profitability, track Gross Margin).

2. Vanity vs. Value: Avoid vanity metrics (like page views or signup counts) that do not translate to cash flow. Focus on retention, active usage, and conversion metrics.

3. Leading vs. Lagging Indicators:

Lagging: Output results (e.g., quarterly revenue). Good for reviews, but cannot be changed after the fact.

Leading: Input activities (e.g., weekly active users, sales pipeline size). Predictive of lagging outputs, allowing early corrections.

Code Example

// Goal: Improve customer service satisfaction
// Lagging KPI: Monthly Net Promoter Score (NPS)
// Leading KPI: Average customer ticket resolution time (hours)

Common Interview Pitfalls

Setting too many KPIs (focusing on 20+ metrics at once dilutes team focus; limit to 3-5 core KPIs per team).
Reporting raw metrics without defining baseline targets or context, leaving stakeholders unable to judge performance.

Rate your confidence:

beginnerBusiness Metrics & Reporting

How do you choose the right chart type to communicate data patterns to business stakeholders?

Direct Answer

Select charts based on analytical goals: Use line charts for trends, bar charts for comparisons, scatter plots for correlations, and tables for exact lookups.

Detailed Explanation

Visual storytelling must match the stakeholder's analytical intent:

Comparison: Use horizontal or vertical Bar Charts to compare categorical sizes. Keep categories sorted by value size to allow fast visual sorting.

Trend Over Time: Use Line Charts with time on the X-axis. Avoid bar charts for long timelines as they clutter the screen.

Correlation: Use Scatter Plots to show relationships between two variables. Add a trendline to indicate direction.

Composition: Use Stacked Bar Charts to show parts of a whole over time. Avoid pie charts unless comparing only two or three elements.

Exact Values: Use a formatted Table with heat-map styling (conditional formatting) when users need to look up exact details.

Code Example

// Chart Choice Matrix:
Trend over 12 months -> Line Chart
Sales across 5 categories -> Sorted Bar Chart
Sales conversion funnel -> Funnel Chart or Stacked Bar

Common Interview Pitfalls

Using dual-axis charts with different scales, which can visually distort data trends and mislead stakeholders.
Using complex 3D charts or excessive color fills that add visual clutter without conveying data.

Rate your confidence:

Official Documentation & Specifications

SQL Querying & Joins

Data Cleaning & Manipulation

Excel & Dashboard Operations

Statistics & Probability

Business Metrics & Reporting

Want to tailer your resume for Data Analyst roles?

Import your resume, scan it for critical Data Analyst keywords, and compare it against ATS standards instantly.

View Data Analyst Resume Example Analyze Your Resume Free