01
How do you handle a dataset with 30% missing values in a key column?
Show answer
Understand why it is missing, consider imputation or a missing flag, test sensitivity, and talk to data owners about fixing collection if it is systematic.
Data Analyst Intern Interview Questions & Answers
40 questions for Data Analyst Intern interviews — 28 easy, 12 medium.
About the Data Analyst Intern role
A data analyst intern supports the analytics team by cleaning data, building simple reports, writing basic SQL queries and preparing charts for presentations. Interviews are about fundamentals and attitude: Excel functions, simple SQL with joins and GROUP BY, basic Python or pandas, mean versus median, and a small project you can explain end to end. Curiosity and clear communication count a lot.
Excel basics and PivotTables Basic SQL queries pandas fundamentals Simple charts Explaining a project
Solved for this roleStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
3 questions free Unlock the rest in seconds
02
How do you split a full name column into first and last name in Excel?
Show answer
-- A2 = 'Priya Ramesh Iyer'
-- first name
=TEXTBEFORE(TRIM(A2), " ")
-- last name (text after the last space)
=TEXTAFTER(TRIM(A2), " ", -1)
-- older Excel: first name
=LEFT(TRIM(A2), FIND(" ", TRIM(A2)&" ")-1)
-- or use Data > Text to Columns or Flash Fill (Ctrl+E) for a one-off task
03
A profit margin visual shows errors for products with zero sales. How do you fix it in DAX?
Show answer
DIVIDE(numerator, denominator, alternate) handles division by zero or blank safely and returns blank or the alternate value you supply, so visuals do not show errors or infinity. The / operator returns an error or infinity in those cases. For example, DIVIDE([Profit], [Sales]) shows blank for a product with no sales. Use / only when you are certain the denominator can never be zero, because it is marginally faster.
04
Write a CREATE TABLE statement for a sales fact table linked to date, product and store dimensions.
Show answer
CREATE TABLE fact_sales (
date_key INT NOT NULL REFERENCES dim_date(date_key),
product_key INT NOT NULL REFERENCES dim_product(product_key),
store_key INT NOT NULL REFERENCES dim_store(store_key),
invoice_no VARCHAR(20) NOT NULL, -- degenerate dimension
quantity INT NOT NULL,
net_amount DECIMAL(12,2) NOT NULL,
discount_amount DECIMAL(12,2) DEFAULT 0
);
05
What is the difference between a fact table and a dimension table?
Show answer
A fact table stores measurable events at a defined grain, such as one row per order line, with numeric measures like quantity and amount plus foreign keys. Dimension tables describe the context of those events: customer, product, store and date, with descriptive attributes used for filtering and grouping. Facts are long and grow quickly; dimensions are wider and smaller. Together they form star or snowflake schemas.
06
What is a pivot table and when do you use it?
Show answer
A pivot table summarises large data by dragging fields into rows, columns, values and filters — for example total sales by region and month — without writing formulas.
07
A report filters WHERE order_date = '2026-03-31' and misses almost all orders from that day. Why?
Show answer
order_date is probably a DATETIME or TIMESTAMP column, so the filter matches only rows at exactly midnight, 2026-03-31 00:00:00. Use a half-open range instead: WHERE order_date >= '2026-03-31' AND order_date < '2026-04-01'. This keeps the index usable, unlike wrapping the column in CAST or DATE(), and it correctly includes orders at 23:59:59.500. Also check the time zone of the stored timestamps.
08
How do you combine text from several cells with a separator in Excel?
Show answer
-- join first and last name with a space
=TEXTJOIN(" ", TRUE, A2, B2)
-- all products of one order in a single cell, comma separated
=TEXTJOIN(", ", TRUE, FILTER(C2:C500, A2:A500=F2))
-- older method
=A2 & " " & B2
-- TRUE in TEXTJOIN skips empty cells, which CONCAT and & do not
09
How do you choose the right chart?
Show answer
Line for trends, bar for comparisons, scatter for relationships, histogram for distributions and pie only for few parts of a whole.
10
What is the difference between WHERE and HAVING?
Show answer
WHERE filters rows before grouping. HAVING filters groups after GROUP BY and can use aggregates like SUM() or COUNT().
11
What is Power Pivot and the Excel Data Model?
Show answer
The Data Model is an in-memory columnar store inside Excel that holds several related tables, and Power Pivot is the interface to manage it. Instead of flattening data with VLOOKUPs, you load fact and dimension tables, create relationships and write DAX measures. It handles millions of rows that would not fit on a sheet, and PivotTables built on it can slice across tables. It is the same engine idea that Power BI uses.
12
Tell me about a time a query you wrote produced wrong results.
Show answer
Situation: my weekly active users number jumped 30 percent overnight. Task: find out whether it was real before the report went out. Action: I traced the query and found a new join to a device table created duplicate users. I changed the count to COUNT(DISTINCT user_id), added a row-count check, and informed my manager before publishing. Result: the corrected number went out on time, and I added that check to every report.
13
What is a virtual environment in Python and why should analysts use one?
Show answer
A virtual environment is an isolated folder with its own Python interpreter and installed packages, created with python -m venv or tools like conda or uv. Each project can then pin its own pandas or library versions without breaking other projects. Saving the package list in requirements.txt or a lock file lets a colleague or a scheduled server reproduce the same setup, which avoids the classic works-on-my-laptop problem.
14
What is tidy data?
Show answer
Tidy data follows three rules: each variable is a column, each observation is a row, and each type of observational unit is a table. A sheet with months as columns is not tidy; unpivoting it to Region, Month and Sales columns makes it tidy. Tidy data is easy to filter, group, join and chart in pandas, SQL, Power BI and Tableau, so reshaping into this form is a core cleaning step.
15
Explain the types of SQL joins.
Show answer
INNER returns matching rows, LEFT keeps all left rows, RIGHT keeps all right rows, FULL keeps all rows from both, and CROSS returns every combination.
16
What is a lambda function in Python and where do analysts use it?
Show answer
A lambda is a small anonymous function written in one line, for example lambda x: x * 1.18 to add GST. Analysts use lambdas with sorted for custom sort keys, with pandas assign for computed columns, for example assign(margin=lambda d: d.profit / d.sales), and occasionally with apply. For anything longer than one expression or reused in several places, a normal def function is clearer and easier to test.
17
Tell me about a time a dashboard you built changed a business decision.
Show answer
Situation: our operations team assumed late deliveries were evenly spread. Task: I built a delivery performance dashboard. Action: I added a breakdown by courier partner and pin code cluster with a weekly trend, and reviewed it with the operations manager. Result: it showed one partner caused 60 percent of delays in two zones. The company shifted those zones to another partner, and on-time delivery rose from 84 to 92 percent in two months.
18
How do you handle null values in Tableau calculations?
Show answer
// treat missing sales as zero
ZN(SUM([Sales]))
// replace a missing value with a default
IFNULL([Region], 'Unknown')
// test explicitly
IF ISNULL([Discount]) THEN 0 ELSE [Discount] END
// right-click a null indicator on an axis to filter or show nulls at default position
19
Show a Pandas groupby example.
Show answer
df.groupby('region')['sales'].agg(['sum', 'mean', 'count'])
# splits by region, aggregates, combines the results
20
Tell me about a time you disagreed with your manager about how to measure something.
Show answer
Situation: my manager wanted to report average resolution time for support tickets. Task: I felt a few very old tickets distorted the average. Action: I prepared a quick comparison showing mean versus median and the percentage resolved within 24 hours, and explained why the median was more representative. Result: we agreed to show median and the 24-hour rate, keeping the mean in a tooltip, and the team stopped chasing misleading spikes.
21
Write pandas code to trim whitespace and lower-case every text column in a DataFrame.
Show answer
text_cols = df.select_dtypes(include='object').columns
for col in text_cols:
df[col] = (
df[col]
.astype('string')
.str.strip()
.str.lower()
.replace('', pd.NA)
)
# also tidy column names
df.columns = df.columns.str.strip().str.lower().str.replace(' ', '_')
22
Write pandas code to compute month-on-month percentage change and cumulative sales for each store.
Show answer
df = df.sort_values(['store', 'month'])
df['mom_pct'] = (
df.groupby('store')['sales'].pct_change() * 100
).round(1)
df['cum_sales'] = df.groupby('store')['sales'].cumsum()
# first month of each store has NaN for mom_pct, which is expected
23
When would you drop missing values, and when would you impute with mean or median?
Show answer
Drop rows when few are missing, roughly under 5 percent, and missingness looks random. Impute when dropping would lose too much data. Use the median for skewed numbers such as income or order value, the mean for roughly symmetric data, and the mode or an Unknown category for categoricals. Group-wise imputation, such as median by city, is better than one global value. Always add a flag column showing what was imputed.
24
Pin codes in your CSV lost their leading zeros after opening in Excel. Why and how do you prevent it?
Show answer
Excel guessed the column was a number and dropped leading zeros; the same happens with long IDs turning into scientific notation. Identifiers that are not used in arithmetic should be stored as text. Import through Data, From Text/CSV or Power Query and set the column type to Text, or read it in pandas with dtype={'pincode': str}. To repair, use =TEXT(A2, "000000") when the original length is known.
25
Write Excel formulas to get a sorted list of unique customer names and their count.
Show answer
-- unique customers from column B, sorted A to Z
=SORT(UNIQUE(B2:B1000))
-- number of unique customers
=COUNTA(UNIQUE(B2:B1000))
-- unique customers sorted by total sales, highest first
=SORTBY(UNIQUE(B2:B1000), SUMIFS(D2:D1000, B2:B1000, UNIQUE(B2:B1000)), -1)
26
Write a Python function that reads all Excel files in a folder and combines them into one DataFrame.
Show answer
from pathlib import Path
import pandas as pd
def combine_excel(folder):
frames = []
for f in Path(folder).glob('*.xlsx'):
df = pd.read_excel(f)
df['source_file'] = f.name
frames.append(df)
return pd.concat(frames, ignore_index=True)
all_sales = combine_excel('monthly_reports')
27
What do COALESCE and NULLIF do in SQL?
Show answer
COALESCE returns the first non-null value from its arguments, for example COALESCE(phone, alt_phone, 'Not given') fills missing phone numbers. NULLIF(a, b) returns NULL when a equals b, otherwise a. Its classic use is avoiding division by zero: revenue / NULLIF(orders, 0) returns NULL instead of an error when orders is zero. Together they handle most null-related reporting problems cleanly.
28
What is the LET function in Excel and why would you use it?
Show answer
LET assigns names to intermediate results inside a single formula, for example =LET(sales, SUM(D2:D100), cost, SUM(E2:E100), (sales-cost)/sales). It makes long formulas readable and faster, because a repeated sub-calculation is evaluated once instead of several times. It is especially useful with dynamic arrays, where the same FILTER result might otherwise be recalculated three or four times in one nested formula.
29
Which Python libraries do analysts use most?
Show answer
Pandas for data handling, NumPy for numerics, Matplotlib and Seaborn for charts, and openpyxl or SQLAlchemy for files and databases.
30
SUMIFS vs COUNTIFS?
Show answer
SUMIFS adds values that meet several conditions; COUNTIFS counts rows that meet several conditions.
Example: =SUMIFS(Sales, Region, "North", Month, "Jan")
31
Numbers in an Excel column are stored as text and SUM returns zero. How do you fix it?
Show answer
Signs are left-aligned numbers, green triangles, and COUNT returning less than COUNTA. Fix options: select the column and use the warning menu Convert to Number; use Data, Text to Columns and Finish; or a helper column with =VALUE(TRIM(A2)). If there are hidden characters, use =VALUE(SUBSTITUTE(TRIM(A2), CHAR(160), "")) to remove non-breaking spaces from web exports. In Power Query, simply change the column type to number.
32
How do you deal with missing or duplicate data?
Show answer
Profile the data, remove exact duplicates using keys, impute missing values (mean/median/mode or a business rule), flag imputed rows and document every cleaning step.
33
Your pandas script shows SettingWithCopyWarning and a new column does not update. What is happening and how do you fix it?
Show answer
It warns that you may be modifying a temporary copy of a slice, so the change might not reach the original DataFrame, for example df[df.sales > 0]['flag'] = 1. Avoid it by using a single loc call, df.loc[df.sales > 0, 'flag'] = 1, or by calling .copy() when you intentionally create a new subset. Newer pandas versions with copy-on-write make behaviour more predictable, but clear loc assignments remain best practice.
34
How do you calculate working days between two dates in Excel, excluding holidays?
Show answer
-- A2 = start date, B2 = end date, Holidays = named range of holiday dates
=NETWORKDAYS(A2, B2, Holidays)
-- if the weekend is only Sunday (weekend code 11)
=NETWORKDAYS.INTL(A2, B2, 11, Holidays)
-- date that is 10 working days after A2
=WORKDAY(A2, 10, Holidays)
35
How do you handle outliers in Pandas?
Show answer
Detect with IQR or z-score, then cap with clip, remove, transform with log, or keep and flag them depending on whether they are errors or real events.
36
What is the difference between a primary key and a unique constraint?
Show answer
Both prevent duplicate values. A table can have only one primary key, it cannot contain NULLs, and it is the main identifier other tables reference through foreign keys. A table can have several unique constraints, for example on email and on PAN number, and most databases allow NULLs in a unique column. Use the primary key for the row identity and unique constraints for other business rules.
37
Your FILTER formula shows #SPILL! instead of results. Why, and how do you fix it?
Show answer
Dynamic array formulas such as FILTER, UNIQUE and SORT return several values that spill into neighbouring cells. #SPILL! appears when that spill range is blocked by existing data, merged cells, or when the formula sits inside an Excel Table, which does not allow spilling. Fix it by clearing the blocking cells, unmerging, or moving the formula outside the Table. Select the error cell to see the dashed border of the range it needs.
38
Tell me about a time you had to learn a new tool or library quickly for a project.
Show answer
Situation: in my internship the team needed survey results analysed in Python, which I had only used in coursework. Task: deliver cleaned data and charts within a week. Action: I spent two evenings on pandas and Seaborn tutorials using our real file, kept a notes document of commands, and asked a senior to review my first notebook. Result: I delivered on day five, and my notebook became the template for the next survey.
39
Write SQL to count orders by week and by day of week.
Show answer
-- orders per week (PostgreSQL style)
SELECT DATE_TRUNC('week', order_date) AS week_start,
COUNT(*) AS orders
FROM orders
GROUP BY 1
ORDER BY 1;
-- orders by day of week (0 = Sunday)
SELECT EXTRACT(DOW FROM order_date) AS dow,
COUNT(*) AS orders
FROM orders
GROUP BY 1
ORDER BY 1;
40
Write SQL to show each category's percentage share of total revenue.
Show answer
SELECT category,
SUM(amount) AS revenue,
ROUND(100.0 * SUM(amount)
/ SUM(SUM(amount)) OVER (), 2) AS pct_share
FROM sales
GROUP BY category
ORDER BY revenue DESC;
-- SUM(SUM(amount)) OVER () is the grand total across all groups
Frequently asked questions
What does a Data Analyst Intern do?
A data analyst intern supports the analytics team by cleaning data, building simple reports, writing basic SQL queries and preparing charts for presentations. Interviews are about fundamentals and attitude: Excel functions, simple SQL with joins and GROUP BY, basic Python or pandas, mean versus median, and a small project you can explain end to end. Curiosity and clear communication count a lot.
Which skills are tested in a Data Analyst Intern interview?
Expect questions on Excel basics and PivotTables, Basic SQL queries, pandas fundamentals, Simple charts, Explaining a project. Most rounds also ask you to explain a project and solve one practical problem live.
How do I prepare for a Data Analyst Intern interview?
Revise Excel, SQL, Python for Analysis, Data Cleaning first using the topic sheets, practise the questions on this page aloud, then do one full mock interview to practise timing.
Topic sheets for this role
Other roles
More ways to practise
Company-wise Data Analytics questionsPackage-wise Data Analytics questionsExperience-wise Data Analytics questionsRole-wise Data Analytics questionsCity-wise Data Analytics questionsMost asked Data Analytics questionsScenario-based questionsPractical / hands-on questionsHR & behavioural questionsData Analytics mock interviewOnline assessment questionsTechnical round questionsManagerial round questionsData Analytics topic sheets