40 questions with answers for candidates with Freshers — 31 easy, 9 medium.
What interviewers expect: freshers
Interviewers expect strong fundamentals rather than experience: Excel formulas and PivotTables, SQL joins and GROUP BY, basic Python or pandas, mean, median and simple charts. You should explain one or two projects clearly, including how you cleaned data and what insight you found. Attitude, clarity of thought and willingness to learn matter as much as tool knowledge.
This set is weighted towards Data Cleaning (7), Web & Marketing Analytics (5), Data Storytelling (5), SQL (4).
Excel and SQL basics One BI tool fundamentals Academic or personal projects Clear communication
Solved at this levelStart 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
01
What is the difference between a fact table and a dimension table?
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.
Dimensional modelling is a way of designing analytical tables around business processes. Numeric events such as sales or payments go into fact tables, and descriptive context such as customer, product, store and date goes into dimension tables. The design is easy for business users to understand and fast for BI tools to aggregate. For example, a retail model might have one sales fact joined to date, product, store and customer dimensions.
Typical steps: remove top junk rows and promote the real header row, remove blank rows and columns, set correct data types, trim and clean text, split combined columns such as Name - City, replace error values, fill down for grouped labels, remove duplicates on the business key, and unpivot wide month columns. Rename steps meaningfully so others can follow them, and keep the query foldable when the source is a database.
05
Write a UTM-tagged URL for a paid Instagram campaign promoting a summer sale, and give two naming rules.
https://www.example.com/sale?utm_source=instagram&utm_medium=paid_social&utm_campaign=summer_sale_2026&utm_content=reel_video_a
-- Rule 1: always lower case with underscores; 'Instagram' and 'instagram' become separate rows
-- Rule 2: keep a shared UTM sheet with allowed source and medium values so every team tags the same way
06
How would you write a short email summarising an analysis for a manager?
Use a clear subject that states the finding, such as Q3 repeat purchase rate fell 4 points, mainly in Tier 2 cities. Open with one or two sentences of the key insight and the recommendation. Follow with three bullet-style lines of supporting evidence with numbers, then any caveats about data, and a clear next step or decision needed with a date. Attach or link the detailed workings instead of pasting large tables.
07
Numbers in an Excel column are stored as text and SUM returns zero. How do you fix it?
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.
08
What is the difference between a live connection and an extract in Tableau?
A live connection queries the source database every time a user interacts, so data is always current but performance depends on the database. An extract is a compressed snapshot stored in Tableau's hyper format, usually much faster, refreshed on a schedule and usable offline. Use live for near real-time needs on a fast warehouse, and extracts for speed, slow sources or when you want to reduce load on production systems.
Data profiling is a quick systematic look at a dataset before analysis: row counts, data types, null percentages, distinct counts, minimum and maximum, common values and value patterns per column. In pandas, df.info(), df.describe(include='all') and value_counts() cover most of it; Power Query has column quality and distribution views. Profiling reveals problems like a date column stored as text or a status field with unexpected values early.
10
Write SQL to calculate users, conversions and conversion rate for each test variant.
SELECT
a.variant,
COUNT(DISTINCT a.user_id) AS users,
COUNT(DISTINCT o.user_id) AS converters,
ROUND(100.0 * COUNT(DISTINCT o.user_id)
/ COUNT(DISTINCT a.user_id), 2) AS conversion_pct
FROM experiment_assignments a
LEFT JOIN orders o
ON o.user_id = a.user_id
AND o.order_ts >= a.assigned_ts
WHERE a.experiment_id = 'checkout_v2'
GROUP BY a.variant;
Data storytelling is communicating insights by combining three things: data, the evidence; visuals, charts that make patterns obvious; and narrative, the context and so-what that explains why it matters and what to do. A table of numbers informs, but a story persuades someone to act. For an analyst, it means leading with the key finding, showing just enough evidence, and ending with a clear recommendation.
12
What KPIs would you track for a customer support call centre?
Volume and staffing: calls offered, answered, abandoned rate and average speed of answer. Efficiency: average handle time and occupancy. Quality and outcome: first contact resolution, customer satisfaction or CSAT score, and repeat contacts within 7 days. Service level, for example 80 percent of calls answered in 20 seconds, ties them together. Balance speed metrics with quality, otherwise agents rush calls to cut handle time.
UTM parameters are tags added to a URL so analytics tools know where a visit came from. The main ones are utm_source (instagram, newsletter), utm_medium (paid_social, email, cpc), utm_campaign (diwali_sale_2026), plus optional utm_content and utm_term. Without them, traffic from emails, messaging apps and many social links shows up as direct or referral, and you cannot measure which campaign drove sign-ups or sales.
14
Write SQL to calculate sessions, orders and conversion rate by traffic source.
SELECT
s.traffic_source,
COUNT(*) AS sessions,
COUNT(o.order_id) AS orders,
ROUND(100.0 * COUNT(o.order_id) / COUNT(*), 2) AS conversion_pct
FROM sessions s
LEFT JOIN orders o
ON o.session_id = s.session_id
GROUP BY s.traffic_source
ORDER BY sessions DESC;
-- assumes at most one order per session; otherwise use COUNT(DISTINCT o.session_id)
15
Write a Tableau calculated field for profit ratio and a profit category.
// Profit Ratio (aggregate first, then divide)
SUM([Profit]) / SUM([Sales])
// Profit Category
IF SUM([Profit]) < 0 THEN 'Loss'
ELSEIF [Profit Ratio] < 0.1 THEN 'Low margin'
ELSE 'Healthy'
END
// avoid AVG([Profit]/[Sales]): it averages row ratios and gives a wrong total
16
What is data cleaning and why does it take so much of an analyst's time?
Data cleaning means fixing or removing incorrect, incomplete, duplicated or inconsistent data so analysis is trustworthy. Real data comes from many systems and manual entry, so you meet typos, mixed formats, missing values and duplicates. Analysts often spend half or more of a project on it because every later chart and model depends on it. Wrong inputs give confident but wrong answers, which is worse than no answer.
17
What does Flash Fill do in Excel, and when should you not rely on it?
Flash Fill (Ctrl+E) detects a pattern from one or two examples you type and fills the rest of the column, for example extracting first names or reformatting phone numbers. It is fast for one-time cleanup. However, it produces static values, not formulas, so it will not update when source data changes, and it can guess wrongly on irregular rows. For repeatable reports, use formulas or Power Query instead.
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.
19
How do you split a full name column into first and last name in Excel?
-- 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
20
How can a chart mislead even when the numbers are correct?
Common tricks are a truncated y-axis on bar charts, which exaggerates small differences; inconsistent scales across side-by-side charts; dual axes chosen to imply a correlation; cherry-picked time windows; cumulative charts that always rise; and area or 3D effects that distort size. Bars should start at zero, while line charts may zoom in if clearly labelled. Always check that the visual impression matches the actual magnitude.
21
What are statistical power and minimum detectable effect (MDE)?
Power is the probability that the test detects a real effect of a given size, commonly set at 80%. The minimum detectable effect is the smallest lift you care about and can reliably detect with your sample size. Smaller MDEs, lower baseline rates and higher power all need more users. If you can only get 10,000 users a week, you may only be able to detect large lifts, which should shape what you test.
22
How do you show actual versus target on a dashboard?
Use a KPI card with actual, target and variance as both value and percentage, coloured by status. For trends, a bar or line for actual with a target line or band works well. Bullet charts compare actual against target and ranges compactly in tables. Make the period clear, such as month to date versus prorated monthly target, because comparing half a month of sales to a full month target looks falsely bad.
value_counts returns how many times each unique value appears in a Series, sorted from most to least frequent. It is a quick way to check category distribution or spot typos. Pass normalize=True to get proportions, then multiply by 100 for percentages, for example df['city'].value_counts(normalize=True).mul(100).round(1). Use dropna=False to include missing values, which are excluded by default and are often important.
26
Estimate how many cups of tea are sold in a city of 1 crore people in a day.
State assumptions clearly. Of 1 crore people, assume 70 percent drink tea, so 70 lakh. Say half of their cups are bought outside, at an average of one bought cup per drinker every day for that half, giving about 35 lakh purchased cups, with more at offices and stations. So roughly 30 to 40 lakh cups. Interviewers judge the structure, segmentation and sanity check more than the exact number.
Variance is the average squared distance of values from their mean; standard deviation is its square root, in the same units as the data. They measure spread. Two stores may both average 50,000 rupees daily, but one with a standard deviation of 2,000 is stable while one with 20,000 is volatile. For samples, divide by n minus 1 instead of n to get an unbiased estimate.
Field parameters let report users switch which measure or dimension a visual uses through a slicer. For example, one chart can switch between Sales, Profit and Quantity, or between grouping by Region, Category and Channel. This replaces many duplicate visuals or bookmark tricks, keeping the report lighter and easier to maintain. They are created from Modeling, New parameter, Fields, which generates a small table behind the slicer.
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.
30
What is the difference between dimensions and measures in Tableau?
Dimensions are qualitative fields such as region, product or date that slice data into groups and create headers. Measures are quantitative fields such as sales or profit that get aggregated, by default with SUM. Dragging a dimension to rows creates one row per member; dragging a measure creates an axis. A numeric field like customer ID should be converted to a dimension so it is not summed.
31
A campaign landing page gets heavy traffic but very few sign-ups. How would you diagnose it?
Check traffic quality first: is the right audience coming, and does the ad message match the page? Then look at page behaviour by device: load time, scroll depth, form start versus form submit, and which form field users abandon. Session recordings or heatmaps help. Test the form on mobile for errors. Common fixes are faster pages, fewer form fields, a clear call to action above the fold and matching the ad's offer.
32
Write pandas code to extract month name, weekday and hour from a timestamp column.
RAG means red, amber, green, a traffic-light status that shows at a glance whether a KPI is on track. Thresholds should be agreed with the business owner in advance, for example green at or above 100 percent of target, amber from 90 to 99 percent, red below 90 percent. Do not rely only on colour; add icons or text for colour-blind users, and avoid so many reds that nobody reacts.
34
What is a virtual environment in Python and why should analysts use one?
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.
Check the components: sessions may have fallen, for example after cutting paid campaigns, which removes low-intent traffic and lifts the rate without new sales. Average order value may have dropped because of discounts or a mix shift towards cheaper products. Also check whether conversion definitions or tracking changed. Revenue equals sessions times conversion rate times AOV, so break down the change across those three factors by channel.
37
What do the match_mode and search_mode arguments of XLOOKUP control?
match_mode decides how a value is matched: 0 exact (default), -1 exact or next smaller, 1 exact or next larger, and 2 wildcard. search_mode decides direction: 1 searches first to last, -1 searches last to first, and 2 or -2 use binary search on sorted data. Searching last to first is a quick way to fetch the latest entry for a customer without sorting, and next-smaller matching handles tax slabs or commission bands.
38
A report filters WHERE order_date = '2026-03-31' and misses almost all orders from that day. Why?
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.
39
Write pandas code to trim whitespace and lower-case every text column in a DataFrame.
A good answer links genuine interest, evidence and the role. For example: I enjoy turning messy questions into clear answers. In my final-year project I analysed two years of canteen sales in Excel and Python and showed that changing the menu on exam weeks could cut waste. I liked that the analysis led to an actual change. I want a role where my SQL, Power BI and communication skills help teams make better decisions.
Frequently asked questions
What is asked in Data Analytics interviews for Freshers?
Interviewers expect strong fundamentals rather than experience: Excel formulas and PivotTables, SQL joins and GROUP BY, basic Python or pandas, mean, median and simple charts. You should explain one or two projects clearly, including how you cleaned data and what insight you found. Attitude, clarity of thought and willingness to learn matter as much as tool knowledge.
How many questions should I practise before a Data Analytics interview?
Work through all 40 questions here, then the company-wise sets for the employers you are targeting. Quality matters more than count: be able to explain why each answer is right.
Which topics matter most for Freshers?
Focus first on Excel and SQL basics, One BI tool fundamentals, Academic or personal projects, Clear communication.