Data Analytics Interview Questions for 1 Year Experience
40 questions with answers for candidates with 1 year of experience — 19 easy, 21 medium.
What interviewers expect: 1 year experience
With one year, you are expected to work independently on routine reports and ad hoc requests. Interviewers check practical SQL including window functions, comfortable Excel and Power Query, a BI tool used in real work, and how you verify numbers before sharing. Be ready to describe a report you built, its users and a problem you solved.
This set is weighted towards Python for Analysis (5), Data Cleaning (5), Tableau (4), Data Modelling & Warehousing (4).
Window functions in SQL Power Query automation Report accuracy checks Real work examples
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.
Tableau Prep is a visual tool for cleaning and shaping data before analysis. You build a flow of steps: connect inputs, clean fields, split columns, group similar values, pivot, join, union and aggregate, and see the data change at every step. The output can be an extract or published data source that dashboards use. It suits analysts who need repeatable preparation without writing SQL or Python.
A role-playing dimension is one physical dimension used several times by the same fact table in different roles. The classic case is the date dimension: an orders fact has order date, ship date and delivery date keys, all pointing to the same date table. In SQL you join it three times with different aliases; in Power BI you keep one active relationship and use USERELATIONSHIP or create separate date views for each role.
SUM adds up a single column in the current filter context. SUMX is an iterator: it goes row by row over a table, evaluates an expression for each row, then sums the results. Use SUMX when the value must be computed per row first, for example SUMX(Sales, Sales[Qty] * Sales[Price]). Iterators are flexible but can be slower on very large tables, so storing a simple column is sometimes better.
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
What is the Central Limit Theorem and why does it matter for analysts?
The Central Limit Theorem says that the distribution of sample means approaches a normal distribution as sample size grows, usually from about 30 observations, even if the underlying data is skewed. This is why we can use z-tests, t-tests and confidence intervals for averages such as order value or session time. It does not make the raw data normal, and very heavy-tailed data may need larger samples.
08
On day 4 of a planned 14-day test, the product manager wants to stop because the dashboard shows treatment winning. What do you say?
I would explain that early results swing a lot and stopping on a good day inflates false positives, and that four days miss weekly patterns like weekend shopping. I would show how the lift has moved day by day and agree to the planned end date. If speed really matters, we can agree a sequential testing rule for future tests, but changing the rule mid-test after seeing data is not valid.
09
What is the difference between a surrogate key and a natural key?
A natural key comes from the source system and has business meaning, such as a customer mobile number or product SKU. A surrogate key is a meaningless integer or hash generated in the warehouse. Surrogate keys protect the model when source keys change, get reused or clash across systems, and they are essential for slowly changing dimensions where one customer has several historical rows. Keep the natural key as an attribute for lookups.
10
Your scheduled Python report failed silently and nobody noticed for a week. How do you make it reliable?
Add logging to a file or central log, wrap the main steps in try and except that send an alert by email or chat on failure, and exit with a non-zero code so the scheduler marks the job failed. Add data checks, such as row counts above a minimum and no missing dates, before sending output. Use a proper scheduler or orchestrator with retries, and include the data date in every report.
11
What is a date table in Power BI and why should you mark it as one?
A date table has one row per day with no gaps, covering the full range of your data, plus columns like year, month, quarter and week. Marking it as a date table tells Power BI to use it for time intelligence functions such as TOTALYTD and SAMEPERIODLASTYEAR. Without it, those functions can give wrong results and auto date tables bloat the model. Relate it to every fact table's date column.
12
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)
13
After joining orders to payments, your revenue total doubles. What went wrong?
This is a join fan-out: an order has more than one matching payment row, for example split payments or retries, so each order line is repeated and sums are inflated. Check by counting rows per key on both sides. Fix it by aggregating payments to one row per order before joining, joining at the correct grain, or using the right key. Always compare row counts and totals before and after a join.
14
How do you create a dual-axis chart in Tableau, for example sales bars with a profit margin line?
Drag Order Date to Columns as a continuous or discrete month, put Sales on Rows, then drag Profit Ratio to Rows next to it. Right-click the second pill and choose Dual Axis. On the Marks card set Sales to Bar and Profit Ratio to Line. Decide whether to synchronise axes: do not synchronise when units differ, like rupees and percentage. Label the axes clearly so readers know which is which.
15
What do CTR, CPC, CPA and ROAS mean in digital marketing?
CTR, click-through rate, is clicks divided by impressions. CPC, cost per click, is spend divided by clicks. CPA, cost per acquisition, is spend divided by conversions such as orders or leads. ROAS, return on ad spend, is revenue from ads divided by ad spend, so a ROAS of 4 means 4 rupees of revenue per rupee spent. ROAS ignores product margin, so a high ROAS can still lose money.
16
What are novelty and primacy effects in experiments?
A novelty effect is when users engage more with a change simply because it is new, so the early lift fades over time. A primacy effect is the opposite: existing users resist a change at first because they are used to the old design, so results start poor and improve. Both mean short tests mislead. Run tests for at least one or two full weeks and compare new versus returning users.
17
How do you merge two DataFrames on different column names?
A data mart is a subset of the warehouse built for one department or subject, such as sales, finance or HR. It contains the facts and dimensions that team needs, often pre-aggregated and with business-friendly names. Marts make reporting faster and simpler for that team. Ideally they are built from the central warehouse using conformed dimensions, so the finance mart and the sales mart still agree on revenue.
19
Write a CREATE TABLE statement for a sales fact table linked to date, product and store dimensions.
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
);
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.
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.
23
Write SQL to calculate open rate and click-through rate for each email campaign.
SELECT
s.campaign_id,
COUNT(DISTINCT s.user_id) AS delivered,
COUNT(DISTINCT o.user_id) AS opened,
COUNT(DISTINCT c.user_id) AS clicked,
ROUND(100.0 * COUNT(DISTINCT o.user_id) / COUNT(DISTINCT s.user_id), 1) AS open_rate,
ROUND(100.0 * COUNT(DISTINCT c.user_id) / COUNT(DISTINCT s.user_id), 1) AS ctr
FROM email_sends s
LEFT JOIN email_opens o ON o.campaign_id = s.campaign_id AND o.user_id = s.user_id
LEFT JOIN email_clicks c ON c.campaign_id = s.campaign_id AND c.user_id = s.user_id
WHERE s.status = 'delivered'
GROUP BY s.campaign_id;
24
Write pandas code to trim whitespace and lower-case every text column in a DataFrame.
// 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
26
What is a lambda function in Python and where do analysts use it?
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.
Readers scan from the top-left, so place the most important KPIs as cards across the top, then trend charts, then breakdowns and detail tables at the bottom. Group related visuals, align them on a grid and keep consistent spacing, fonts and colours. Put filters in one consistent place. Limit a page to roughly five to eight visuals that answer related questions, and give the page a clear title stating its purpose.
28
What are parameters in Tableau and how do you use them?
A parameter is a single user-controlled value, such as a number, date or string, that is independent of the data. On its own it does nothing; you reference it in calculations, filters, reference lines or bins. Common uses are a Top N selector, a what-if growth rate, or a measure switcher where a CASE statement picks Sales, Profit or Quantity based on the parameter. Parameter actions let users set it by clicking a mark.
29
What is the unit of randomisation in an experiment, and how do you choose it?
The unit of randomisation is what gets assigned to control or treatment: a user, a session, a device, a store or a city. Usually it should be the user, so one person always sees the same version. Session-level randomisation can show one user both versions and contaminate results. Your analysis unit should match it; if you randomise by user but analyse per page view, standard errors become too small.
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.
31
Why do ratio metrics need extra care on a dashboard compared with count metrics?
A ratio like conversion rate or average order value can move because the numerator or the denominator changed, so always show both components nearby. Ratios can look dramatic on small bases, such as 2 of 4 users. They also cannot be averaged across segments; you must divide total numerator by total denominator, otherwise small segments get too much weight. Counts are simpler but need context like trends or targets.
Use a star schema, remove unused columns, prefer measures, reduce visuals per page, limit high-cardinality fields and use aggregations or incremental refresh.
33
Your sales data contains negative quantities. What do you do with them?
Do not delete them blindly. First find out what they mean: often they are returns, cancellations or credit notes, which are valid business events. Check with the source team or a documentation note. If they are returns, keep them but report gross sales, returns and net sales separately. If some are clearly entry errors, such as a negative quantity on a new order, flag and correct them and log the rule.
34
What is the difference between an outlier and a data error?
An outlier is a genuine but unusual value, such as a bulk corporate order of 50 laptops; it is real and may be important. A data error is an incorrect value, such as an age of 250 or a price entered in paise instead of rupees. Errors should be fixed or removed. Outliers should be investigated and often kept, perhaps analysed separately or with median-based metrics so they do not distort averages.
35
Tell me about a time requirements kept changing on a report you were building.
Situation: a sales head kept adding new metrics while I was building a Power BI report. Task: deliver on time without endless rework. Action: I wrote a one-page scope with the agreed KPIs, shared a rough mock-up first, and logged new asks in a backlog with effort estimates. We agreed phase one and phase two. Result: phase one went live on schedule, and later requests were planned instead of disrupting the work.
36
What are primary metrics and guardrail metrics in an A/B test?
The primary metric is the one decision metric the test is designed to move, such as checkout conversion. Guardrail metrics are ones that must not get worse, such as page load time, refund rate, unsubscribe rate or revenue per user. A change that lifts conversion but increases cancellations should not ship. Define both before launch, so the decision rule is clear and nobody picks the favourable metric afterwards.
37
Write Python code to extract valid 10-digit Indian mobile numbers from a messy text column.
import re
pattern = re.compile(r'(?:\+?91[\s-]?|0)?([6-9]\d{9})')
def get_mobile(text):
if not isinstance(text, str):
return None
digits = re.sub(r'[\s()-]', '', text)
m = pattern.search(digits)
return m.group(1) if m else None
df['mobile'] = df['contact'].apply(get_mobile)
-- A2:D500 holds Order, Customer, Region, Amount
=FILTER(A2:D500, C2:C500="North", "No orders")
-- two conditions (AND) multiply the tests
=FILTER(A2:D500, (C2:C500="North")*(D2:D500>10000), "No orders")
-- OR conditions add the tests
=FILTER(A2:D500, (C2:C500="North")+(C2:C500="East"))
Frequently asked questions
What is asked in Data Analytics interviews for 1 year of experience?
With one year, you are expected to work independently on routine reports and ad hoc requests. Interviewers check practical SQL including window functions, comfortable Excel and Power Query, a BI tool used in real work, and how you verify numbers before sharing. Be ready to describe a report you built, its users and a problem you solved.
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 1 year of experience?
Focus first on Window functions in SQL, Power Query automation, Report accuracy checks, Real work examples.