Skip to content

Data Analytics Interview Questions for Senior Specialists (₹15–20 LPA)

40 questions with answers for senior specialists — 28 medium, 12 hard.

What ₹15–20 LPA interviews expect

Deep expertise in a specialisation Architecture-level reasoning Metrics and business impact Leading small initiatives

About ₹15–20 LPA Data Analytics roles

Roles in the ₹15–20 LPA band are typically for senior specialists. Interviewers check whether you can already do the work expected at this level, so questions here are weighted towards SQL (8), Excel (6), Dashboards and KPIs (5), Power BI (4).

Salary bands are indicative only. The same skills can pay differently across cities, company types and negotiation.

Solved in this bandStart 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 Power Query and why is it useful?

Power BIMedium
Save to ▾
Show answer
An ETL tool inside Excel for importing, cleaning and shaping data with recorded steps that refresh automatically when the source changes.
02

What do the match_mode and search_mode arguments of XLOOKUP control?

ExcelMedium
Save to ▾
Show answer
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.
03

What is Power Pivot and the Excel Data Model?

ExcelMedium
Save to ▾
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.
04

Why do ratio metrics need extra care on a dashboard compared with count metrics?

Save to ▾
Show answer
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.
05

Write a DAX measure for a KPI card showing achievement against target and a colour for conditional formatting.

Save to ▾
Show answer
Achievement % = DIVIDE([Total Sales], [Sales Target]) KPI Colour = VAR a = [Achievement %] RETURN SWITCH( TRUE(), ISBLANK(a), "#9E9E9E", a >= 1, "#2E7D32", a >= 0.9, "#F9A825", "#C62828" ) -- apply KPI Colour via Format > Callout value > fx > Field value
06

You loaded a month of sales into the warehouse. How do you check the load is complete and correct?

Save to ▾
Show answer
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

What is the difference between melt and pivot?

Save to ▾
Show answer
melt converts wide data to long format; pivot converts long data to wide, and they are used to reshape data for analysis or charts.
08

Tell me about a time you had to say no to a stakeholder's request.

Save to ▾
Show answer
Situation: a regional manager asked me to show his team's numbers excluding two weeks of a stock-out. Task: be helpful without misreporting. Action: I explained that removing the period would make his region incomparable with others, and offered an annotation and a separate view showing the stock-out impact. Result: he accepted, the leadership review understood the dip, and the main dashboard stayed consistent for every region.
09

Tell me about a time you designed or improved a data model.

Save to ▾
Show answer
Situation: our sales reports used five separate Excel extracts and numbers never matched. Task: build one reliable model. Action: I listed the business questions, fixed the grain at invoice line, built conformed date, product and branch dimensions and moved logic into SQL views, then rebuilt the Power BI reports on top. Result: refresh time dropped from 40 to 6 minutes and month-end reconciliation queries fell sharply.
10

What is the difference between a dashboard and a report?

Save to ▾
Show answer
A report is multi-page and interactive from one dataset; a dashboard is a single-page canvas of pinned tiles from many reports in the Service.
11

How would you calculate retention using SQL?

SQLHard
Save to ▾
Show answer
Find each user's first activity date as a cohort, join to later activity, and compute the share of each cohort active in day, week or month N.
12

What is a Data Table in Excel's What-If Analysis?

ExcelMedium
Save to ▾
Show answer
A Data Table shows how a formula's result changes when one or two inputs vary. For example, place a loan EMI formula in a corner, list interest rates down the column and tenures across the row, then use What-If Analysis, Data Table to fill every combination. It is ideal for sensitivity analysis in pricing or budgeting. Large Data Tables slow recalculation, so set calculation to automatic except tables when needed.
13

Why would you publish a Tableau data source separately instead of embedding it in each workbook?

TableauMedium
Save to ▾
Show answer
A published data source lives on Tableau Server or Cloud and can be reused by many workbooks. Calculations, joins, field names and extract refresh schedules are defined once, so every report uses the same revenue logic and the database is queried once per refresh instead of once per workbook. You can certify it and set permissions. Embedded sources are fine for a quick personal analysis but drift over time.
14

Tell me about a time you found a serious data quality issue.

Save to ▾
Show answer
Situation: while building a returns report I noticed one warehouse had zero returns for three months. Task: confirm whether it was real. Action: I compared with the source system and found its return codes had changed after a software update, so my filter missed them. I informed the data owner, updated the mapping and back-filled the history. Result: the corrected report showed that warehouse had the highest return rate, which led to a packaging review.
15

Explain row context and filter context.

Power BIMedium
Save to ▾
Show answer
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.
16

Write Python code to bootstrap a 95% confidence interval for the difference in revenue per user between two groups.

Save to ▾
Show answer
import numpy as np rng = np.random.default_rng(42) control = df.loc[df.variant == 'A', 'revenue'].to_numpy() treat = df.loc[df.variant == 'B', 'revenue'].to_numpy() diffs = [] for _ in range(5000): c = rng.choice(control, size=len(control), replace=True) t = rng.choice(treat, size=len(treat), replace=True) diffs.append(t.mean() - c.mean()) low, high = np.percentile(diffs, [2.5, 97.5]) print(f'95% CI for difference: {low:.2f} to {high:.2f}')
17

Brand keyword ads show a ROAS of 15, and the team wants to double the budget. How do you respond?

Save to ▾
Show answer
Brand search ROAS is usually inflated because people searching the brand name were already looking for you and would often click the free organic result anyway. Before increasing spend, I would propose an incrementality test, such as pausing brand ads in some matched cities or time periods and measuring the drop in total brand-search orders. The incremental ROAS is often far lower, so the extra budget may work better elsewhere.
18

How are NULLs handled in SQL?

SQLMedium
Save to ▾
Show answer
NULL means unknown, so comparisons with = fail; use IS NULL, COALESCE for defaults and remember most aggregates ignore NULLs except COUNT(*).
19

How would you find customers who never placed an order?

SQLMedium
Save to ▾
Show answer
LEFT JOIN customers to orders and keep rows where order_id IS NULL, or use NOT EXISTS, which also handles NULLs safely.
20

What is the LAMBDA function in Excel and when would you use it?

ExcelHard
Save to ▾
Show answer
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.
21

Explain correlation vs regression.

Save to ▾
Show answer
Correlation shows the strength and direction of association; regression builds an equation to predict one variable from others.
22

What is normalisation?

SQLMedium
Save to ▾
Show answer
Organising tables to reduce redundancy and anomalies by splitting data into related tables, commonly up to third normal form.
23

Actual sales are at order-line level but targets are set per region per month. How do you model them together?

Save to ▾
Show answer
Keep two fact tables at their natural grains: fact_sales at order line and fact_target at region-month. Never spread targets onto order lines. Both share conformed dimensions at a common level, such as a month attribute in the date dimension and region in the geography dimension. In reports, compare them only at month and region or higher. In Power BI, relate targets to month-level or region-level tables, not to daily keys.
24

Your Top 10 products filter in Tableau ignores the category filter. Why, and how do you fix it?

TableauMedium
Save to ▾
Show answer
A context filter is applied before other dimension filters and before FIXED LOD calculations, creating a smaller dataset the rest of the view works on. Use it when a Top N filter should operate within a selected category, for example top 10 products within Electronics, or when a FIXED LOD must respect a filter. Add a filter to context from its right-click menu; it turns grey. Too many context filters can hurt performance.
25

How do you measure impact when a proper A/B test is not possible?

Save to ▾
Show answer
Use quasi-experimental methods. Difference-in-differences compares the change in a treated group, such as cities that got a new policy, with the change in similar untreated cities over the same period. Synthetic control builds a weighted mix of untreated regions to mimic the treated one. Regression discontinuity uses a cut-off, like a credit score threshold. Always check pre-period trends and state the assumptions clearly.
26

What is the pyramid principle and how does it apply to presenting analysis?

Save to ▾
Show answer
The pyramid principle means starting with the answer, then giving the few key arguments that support it, then the detailed data under each argument. Analysts are tempted to present in the order they worked, from data to conclusion, but leaders want the conclusion first. Example: we should cut the free-delivery threshold, because it lifts order frequency, margin impact is small and competitors already did it, each backed by evidence.
27

Which KPIs would you track for an e-commerce app?

Save to ▾
Show answer
DAU/MAU, conversion rate, average order value, cart abandonment, retention/churn, CAC, LTV, return rate and NPS.
28

What is a CTE and when would you use one?

SQLMedium
Save to ▾
Show answer
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.
29

Your scheduled Python report failed silently and nobody noticed for a week. How do you make it reliable?

Save to ▾
Show answer
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.
30

What is a window function?

SQLMedium
Save to ▾
Show answer
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 ...).
31

Leadership wants sales by store manager, but managers move between stores every few months. How would you model it?

Save to ▾
Show answer
First agree the business rule: should a sale belong to the manager in charge on the sale date, or to the current manager? For history, track the manager on the store dimension as SCD Type 2, or keep a separate store-manager assignment table with start and end dates and join on the sale date. Add a current-manager attribute too, so both views are possible. Document the rule on the report.
32

What is a slowly changing dimension? Explain Type 1 and Type 2.

SQLHard
Save to ▾
Show answer
A slowly changing dimension handles attributes that change over time, like a customer's city. Type 1 overwrites the old value, so history is lost and past sales appear under the new city. Type 2 adds a new row with a new surrogate key plus valid_from, valid_to and is_current columns, so old sales stay linked to the old city. Type 2 preserves accurate history but makes the table larger and joins more careful.
33

Explain GROUP BY with an example.

SQLMedium
Save to ▾
Show answer
It groups rows sharing values so aggregates run per group, for example SELECT region, SUM(sales) FROM orders GROUP BY region.
34

What is the unit of randomisation in an experiment, and how do you choose it?

Save to ▾
Show answer
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.
35

Leadership wants a single revenue number for next quarter, but your forecast is uncertain. How do you present it?

Save to ▾
Show answer
Show ranges, not just a single number: a forecast line with a shaded prediction interval, or a statement like revenue will likely be between 48 and 55 crore, most likely 51. Explain the main assumptions and what would change the outcome, perhaps with best, base and worst scenarios. Use plain words such as likely or unlikely consistently. Hiding uncertainty makes a forecast look precise but leads to bad decisions when reality differs.
36

How do SUMIFS and SUMPRODUCT differ?

ExcelMedium
Save to ▾
Show answer
SUMIFS sums values meeting several criteria and is fast. SUMPRODUCT multiplies arrays and adds them, handling more complex conditions and weighted totals.
37

Explain INDEX and MATCH together.

ExcelMedium
Save to ▾
Show answer
MATCH finds the position of a value in a range and INDEX returns the item at that position, giving a flexible lookup that works left or right and survives column insertions.
38

Tell me about a time you handled a request for access to sensitive data.

Save to ▾
Show answer
Situation: a regional manager asked for a salary-level export of all employees to plan hiring. Task: help without breaking data policy. Action: I clarified the actual need, which was headcount cost by grade and location, checked with HR on access rules, and built a Power BI view with aggregated costs and row-level security limiting each manager to their region. Result: the manager got what was needed and no individual salaries were exposed.
39

Your sales data contains negative quantities. What do you do with them?

Save to ▾
Show answer
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.
40

What are field parameters in Power BI?

Power BIMedium
Save to ▾
Show answer
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.

Frequently asked questions

What do Data Analytics interviews for ₹15–20 LPA roles test?

At ₹15–20 LPA the focus is usually on: deep expertise in a specialisation, architecture-level reasoning, metrics and business impact, leading small initiatives. This page's set is weighted towards SQL (8), Excel (6), Dashboards and KPIs (5), Power BI (4).

Which companies pay ₹15–20 LPA for Data Analytics roles?

Pay depends on company type, city, skills and negotiation, not only experience. Use the company-wise pages to practise for the specific employers you are targeting, and treat salary bands as a rough guide.

How should I prepare for a ₹15–20 LPA Data Analytics interview?

Solve every question here without looking at the answer first, mark the ones you miss, save them to Last-Minute Revision and revise them the night before. Prepare two project stories that show impact at the level expected for ₹15–20 LPA.