Data Analytics Interview Questions for 5 Years Experience
40 questions with answers for candidates with 5 years of experience — 1 easy, 23 medium, 16 hard.
What interviewers expect: 5 years experience
At five years, you are often interviewed for senior analyst or lead roles. Expect questions on metric governance, designing a reporting layer or semantic model, experiment design, and influencing leadership. Interviewers look for examples where you defined KPIs for a function, mentored analysts, set standards, and translated analysis into strategy with measurable business results.
This set is weighted towards Dashboards and KPIs (5), Power BI (5), SQL (5), Web & Marketing Analytics (4).
Metric definitions and governance Experiment design Mentoring and code review Executive 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
A stakeholder wants 20 more visuals added to an already crowded dashboard page. How do you respond?
I would ask which decisions each new visual supports and how often they will look at it. Usually a few are essential and the rest are nice to have. I would propose keeping the main page focused on core KPIs and moving detail into drill-through pages, tooltips or a separate analysis tab. Showing a quick mock-up of both versions helps them see that the cleaner page answers questions faster.
02
A manager asks for a dashboard that refreshes every minute. How do you decide the right refresh frequency?
Match refresh to how quickly decisions are made. A monthly board pack needs daily refresh at most, while a warehouse dispatch board may need 15-minute or live updates. Faster refresh costs more in source load, licensing and complexity, and can show incomplete data. Ask how quickly someone will act on a change, check when upstream systems finish loading, and always display the last refreshed time on the dashboard.
Row context evaluates one row at a time (calculated columns); filter context is the set of filters from slicers, visuals and relationships that a measure sees.
04
Write pandas code to calculate customer acquisition cost and payback period in months for each channel.
Reconcile against the source: compare row counts, total amount, and counts by day and by store between source and target. Check for duplicate keys, nulls in mandatory columns, and dates outside the expected month. Compare with the previous month to spot big unexplained jumps. Spot-check a few individual transactions end to end. Automate these checks so each future load produces a small pass or fail report.
07
How do you implement row-level security in Tableau?
The scalable approach is an entitlement table mapping usernames to allowed values, such as region managers to their regions. Relate or join it to the data, then add a calculated filter like USERNAME() = [Username] and apply it as a data source filter on the published source so it cannot be removed. For small cases, user filters or group-based functions like ISMEMBEROF work. Test by viewing as different users before release.
08
How do you let users choose Top N customers with a parameter in Tableau?
Create an integer parameter Top N with a range of 5 to 50 and show its control. Drag Customer to the Filters shelf, choose the Top tab, select By field, and set it to top Top N by SUM(Sales). Alternatively create a set based on the parameter so you can show Top N versus Others in one chart. If the Top N must respect a category filter, add that category filter to context.
A calculated column is computed row by row and stored in the model. A measure is computed at query time using the filter context, e.g. Total Sales = SUM(Sales[Amount]). Prefer measures for aggregations.
10
Tell me about a time you worked with a marketing team to improve a campaign using data.
Situation: a lead generation campaign had a high cost per lead and sales said leads were poor. Task: find what to change. Action: I joined ad data with CRM outcomes and found two audience segments produced most leads but almost no closed deals, while one smaller segment converted well. Result: we moved budget to that segment and changed the form questions; cost per qualified lead fell about 30% in six weeks.
A factless fact table records that an event or relationship happened but has no numeric measures, only dimension keys. Examples are student attendance (student, class, date) or which products were on promotion in which store each day. You analyse it by counting rows. A promotion coverage table is useful for questions like which promoted products did not sell, by comparing it with the sales fact.
12
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.
13
Write SQL to find the median order value overall and per city.
-- Works in PostgreSQL, Snowflake, SQL Server (with OVER for SQL Server)
SELECT
city,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY order_value) AS median_value,
AVG(order_value) AS mean_value
FROM orders
GROUP BY city
ORDER BY median_value DESC;
14
Users say a Power BI report still shows yesterday morning's data, although a refresh is scheduled every few hours. What do you check?
Open the semantic model's refresh history in the Service to see whether refreshes failed, for example due to expired credentials, an offline gateway or a timeout. Check whether the report is connected to a different copy of the model, whether the source itself was updated, and whether incremental refresh only covers some partitions. Also confirm the schedule time zone. Finally, add a 'data as of' timestamp on the report.
15
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.
A north star metric is the single measure that best captures the value a product delivers to customers and predicts long-term growth, such as weekly active learners for an education app or orders delivered for a delivery service. It aligns teams around one outcome. It should be supported by input metrics that teams can influence, such as activation rate and repeat usage, and balanced with guardrails like cost or quality.
17
Your workbook has 200,000 rows and hundreds of VLOOKUPs and takes minutes to recalculate. What do you do?
First switch calculation to manual to work safely, then find the cause. Replace whole-column references with Tables, swap repeated VLOOKUPs for one XLOOKUP or INDEX-MATCH helper column, and remove volatile functions like OFFSET, INDIRECT and TODAY used in many cells. Better still, move the joins into Power Query or the Data Model so lookups happen once at refresh. Finally paste values for historic data that never changes and keep formulas only where needed.
Links between tables on key columns; cardinality can be one-to-many, many-to-one or many-to-many, and cross-filter direction controls how filters flow.
A function that calculates over a set of rows related to the current row without collapsing them, such as ROW_NUMBER, RANK, LAG or SUM() OVER (PARTITION BY ...).
21
What is the LAMBDA function in Excel and when would you use it?
LAMBDA lets you write a custom, reusable function with formulas only, no VBA. You define it, for example =LAMBDA(price, rate, price*(1+rate)), and save it in the Name Manager as AddGST, then call =AddGST(B2, 18%) anywhere. Helper functions like MAP, REDUCE, SCAN and BYROW pair with it for array logic. Use it when a complex calculation repeats across a workbook, so the logic lives in one tested place.
Build PivotTables from a clean table, add PivotCharts and slicers, link KPIs with formulas, and refresh from Power Query so everything updates together.
Start with KPIs (revenue, margin, growth), add trend and comparison to target, allow drill-down by region and product, keep it to one screen and test with the audience.
25
What is a query execution plan and how do you read it?
An execution plan shows how the database will run a query: which indexes it uses, join methods such as nested loop or hash join, scan types and estimated rows. You get it with EXPLAIN, or EXPLAIN ANALYZE for actual timings. Look for full table scans on big tables, large gaps between estimated and actual rows, and expensive sorts. These point to missing indexes, stale statistics or non-sargable filters like functions on columns.
26
Describe a time you explained the limits of attribution data to a senior stakeholder.
Situation: a director wanted to cut social ads because last-click reports showed almost no sales. Task: give a fairer view. Action: I showed with simple path examples that social often started journeys that ended in search, compared first-click and data-driven views, and proposed a two-city holdout test. Result: the test showed meaningful incremental sales, so the budget was trimmed modestly instead of cut, and attribution caveats were added to the dashboard.
27
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.
28
Your Tableau numbers change unexpectedly when a filter is applied to a view that uses a FIXED LOD. Why?
Usually it is the order of operations. FIXED LODs are computed before dimension filters, so they ignore a normal region filter, while other measures in the view respect it. That mix makes percentages and totals look inconsistent. Decide what the business wants: if the LOD should respect the filter, add the filter to context; if it should not, keep it and label it clearly, for example share of national sales.
29
Difference between a CTE, a subquery and a temp table?
A subquery is inline; a CTE is a named, readable block for one statement; a temp table is physically stored for the session and can be indexed and reused.
30
Sales data from different countries mixes currencies and some weights are in grams while others are in kilograms. How do you handle it?
Never add mixed units. Identify the unit for each row from a currency or unit column, or infer it from the source system. Convert to one standard, for example INR using a dated exchange rate table joined on transaction date, and kilograms using a conversion factor. Keep the original value and unit columns for audit. Document which rates were used, and flag rows where the unit could not be determined.
31
Write SQL to incrementally load new and changed products into a dimension using MERGE.
MERGE INTO dim_product t
USING stg_product s
ON t.product_id = s.product_id
WHEN MATCHED AND (t.name <> s.name OR t.category <> s.category) THEN
UPDATE SET name = s.name,
category = s.category,
updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
INSERT (product_id, name, category, updated_at)
VALUES (s.product_id, s.name, s.category, CURRENT_TIMESTAMP);
Table calculations are computed on the aggregated results already in the view, not on the underlying rows. Examples are running total, percent of total, difference from previous, rank and moving average. Their result depends on the direction and partition you set with Compute Using, for example table across or by specific dimensions. They are quick to add from Quick Table Calculation, but incorrect Compute Using settings are a frequent cause of wrong numbers.
33
How do you make sure a metric like active customer means the same thing across all teams?
Write a clear definition in a shared metric dictionary: formula, filters, time window, grain, owner and source table, for example a customer with at least one paid order in the last 90 days. Implement it once, in a governed dataset or semantic layer, and have all dashboards reuse it rather than recalculating. Version changes, announce them and show the definition in the dashboard tooltip. Review new metrics before they are published.
34
What is statistical significance vs practical significance?
Broadcasting lets NumPy perform operations on arrays of different shapes without copying data, by virtually stretching the smaller array. For example, subtracting a 1D array of column means from a 2D array centres every column in one line: X - X.mean(axis=0). Shapes are compatible when trailing dimensions are equal or one of them is 1. It is a key reason vectorised code is much faster than Python loops.
36
An A/B test is flat overall but significant for mobile users in Tier 2 cities. Do you ship it only for that segment?
Not straight away. Slicing results into many segments makes a chance finding likely, so this is a hypothesis, not a conclusion. Check whether the segment was planned in advance, how many segments were examined, and whether there is a plausible reason, such as slower networks benefiting from a lighter page. If it still looks promising, run a follow-up test targeted at that segment with enough sample to confirm it.
37
What is referential integrity and how do orphan records appear in analytics data?
Referential integrity means every foreign key value points to an existing row in the parent table, for example every order's customer_id exists in customers. Orphan records break this rule. They appear when source systems load tables at different times, records are deleted without cascading, or IDs are formatted differently across systems. Detect them with a LEFT JOIN where the parent key is NULL, and decide whether to fix, map or flag them.
38
What is the difference between marketing mix modelling and multi-touch attribution?
Multi-touch attribution tracks individual user journeys and assigns credit to clicks and views, but it misses offline channels like TV and outdoor, and it is weakened by cookie loss and privacy rules. Marketing mix modelling uses aggregated weekly data, such as spend per channel, sales, price and seasonality, in a regression to estimate each channel's contribution and diminishing returns. MMM is better for budget planning; MTA is better for quick digital optimisation.
39
Your pandas script takes 40 minutes on 10 million rows. How do you speed it up?
First profile to find the slow step instead of guessing. Common fixes: replace row-wise apply and loops with vectorised operations, convert repeated strings to category dtype, downcast numeric types, read only needed columns with usecols, and filter early. Push heavy joins and aggregations into SQL. If it is still slow, use a columnar engine such as DuckDB or Polars, or store intermediate data as Parquet instead of CSV.
A Common Table Expression (WITH name AS (...)) is a named temporary result used inside one query. Use it to break complex logic into readable steps or for recursive queries.
Frequently asked questions
What is asked in Data Analytics interviews for 5 years of experience?
At five years, you are often interviewed for senior analyst or lead roles. Expect questions on metric governance, designing a reporting layer or semantic model, experiment design, and influencing leadership. Interviewers look for examples where you defined KPIs for a function, mentored analysts, set standards, and translated analysis into strategy with measurable business results.
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 5 years of experience?
Focus first on Metric definitions and governance, Experiment design, Mentoring and code review, Executive communication.