Data Analyst Technical Interview Questions and Answers
Landing a data analyst role depends heavily on how well you handle data analyst technical interview questions and answers — from SQL queries and Python coding to statistics, data cleaning, and business case problems. This guide walks through the most commonly asked questions, gives clear sample answers, and shows you how to prepare so you can walk into your interview with confidence And it works..
Why Technical Interviews Matter for Data Analysts
Hiring managers use technical rounds to verify that you can actually turn raw data into decisions, not just list tools on your resume. You will typically face four question types:
- SQL and database questions — writing queries, joins, aggregations
- Python or R questions — manipulating data with pandas or dplyr
- Statistics and probability questions — testing hypotheses, interpreting results
- Business and case questions — measuring performance, building dashboards
Understanding this structure helps you study strategically instead of randomly reviewing tutorials.
SQL Interview Questions and Answers
1. How do you find duplicate records in a table?
A classic question. The answer uses GROUP BY with HAVING:
SELECT email, COUNT(*) AS occurrences
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;
Explain that WHERE cannot filter aggregates — that is exactly why HAVING exists after grouping.
2. What is the difference between INNER JOIN and LEFT JOIN?
- INNER JOIN returns only rows where the key exists in both tables.
- LEFT JOIN returns every row from the left table, filling unmatched right-side columns with NULL values.
A strong answer adds a practical example: "If I want all customers plus their orders — including customers who never ordered — I use LEFT JOIN so no customer disappears from the report."
3. Explain ROW_NUMBER, RANK, and DENSE_RANK.
These are window functions:
- ROW_NUMBER gives every row a unique sequential number, even with ties.
- RANK skips positions after ties (1, 1, 3).
- DENSE_RANK never skips (1, 1, 2).
Mentioning a use case — like "top 5 products by revenue" — shows real
4. When would you use CTEs (Common Table Expressions) versus sub‑queries?
A CTE makes a complex query more readable and reusable. Use a CTE when you need to:
- Break a query into logical steps (e.g.,
WITH recent_orders AS (SELECT * FROM orders WHERE order_date >= '2023‑01‑01') SELECT * FROM recent_orders WHERE amount > 1000;). - Reference the same derived dataset multiple times within a single statement.
- Improve maintainability for teammates who later modify the logic.
Sub‑queries are preferable when you only need a temporary result set inside a single SELECT, WHERE, or HAVING clause and you don’t intend to reuse it And it works..
5. How do you handle missing values in SQL?
- For numeric columns – fill with
0, the column’s average, or a sentinel value, e.g.:
UPDATE sales
SET quantity = COALESCE(quantity, 0);
- For text columns – use an empty string or
'Unknown':
UPDATE customers
SET region = COALESCE(region, 'Unknown');
- For dates – replace with a default like
'1900‑01‑01'or the column’s min/max date, depending on the business context.
Explain that the choice depends on downstream analysis; a later WHERE filter may need a placeholder that won’t break joins.
Python / R Interview Questions and Answers
1. How do you filter rows in a pandas DataFrame based on multiple conditions?
# Using boolean indexing
mask = (df['sales'] > 1000) & (df['region'] == 'West')
filtered = df[mask]
Key points to mention: short‑circuiting with & (and) versus | (or), the importance of parentheses around each condition, and an alternative using query() for readability (df.query('sales > 1000 and region == "West"')) Which is the point..
2. Explain the difference between apply() and applymap() in pandas.
| Function | Scope | Example Use‑Case |
|---|---|---|
apply() |
Row‑wise (axis=0) or column‑wise (axis=1) |
Computing a per‑row total: df.In real terms, apply(lambda r: r['price'] * r['quantity'], axis=1) |
applymap() |
Element‑wise (applies to every cell) | Converting units: `df. applymap(lambda x: x * 0.621371 if pd. |
Highlight that applymap is vectorizable in newer pandas versions, making it faster for simple element transformations.
3. How would you reshape a DataFrame from wide to long format?
df_long = pd.melt(df, id_vars=['product_id'],
value_vars=['Q1','Q2','Q3','Q4'],
var_name='quarter', value_name='sales')
Discuss when to use melt versus stack, and mention that pd.wide_to_long is a convenience wrapper for regular melt patterns.
4. What is the purpose of groupby() and how do you handle multiple aggregations?
agg_df = df.groupby('category').agg(
total_sales=('sales', 'sum'),
avg_price=('price', 'mean'),
transaction_count=('id', 'count')
).reset_index()
Explain that agg() can accept a dict mapping column names to functions, or a list of functions for each column. highlight the importance of resetting the index when you need a flat table for downstream reporting Worth knowing..
5. In R, how do you handle missing values (NA) in a data frame column?
# Remove rows where a key column is NA
df
### 6. Detecting and removing duplicate rows in a pandas DataFrame
```python
# Flag duplicates (True for all but the first occurrence)
dup_mask = df.duplicated()
# Drop every duplicated row, keeping none
df_unique = df.drop_duplicates(keep=False)
# Or keep the first occurrence and discard later repeats
df_clean = df.drop_duplicates()
Why it matters: duplicate records can skew aggregates, inflate model training sizes, or cause ambiguous joins. Spotting them early with duplicated() and pruning with drop_duplicates() helps maintain data integrity before any downstream analysis.
7. loc versus iloc in pandas
| Aspect | loc |
iloc |
|---|---|---|
| Indexing basis | Label‑based (row/column names) | Position‑based (integer locations) |
| Slice support | Accepts : for ranges, lists, and boolean masks |
Same slice syntax, but only for integer positions |
| Typical use | When you know the exact column names or want to select by row labels | When you need to reference rows/columns by their order (e.g., the 5th row, the 2nd‑4th columns) |
# Label‑based selection
subset = df.loc['A':'C', ['price', 'quantity']]
# Positional selection
subset = df.iloc[0:3, 1:2] # rows 0‑2, columns 1