Data Analytics Interview Questions for Lead Roles (₹20–25 LPA)
40 questions with answers for lead roles — 1 easy, 27 medium, 12 hard.
What ₹20–25 LPA interviews expect
Leading and reviewing others’ work Planning and estimation Strategy and roadmap input Cross-team collaboration
About ₹20–25 LPA Data Analytics roles
Roles in the ₹20–25 LPA band are typically for lead roles. Interviewers check whether you can already do the work expected at this level, so questions here are weighted towards Python for Analysis (7), SQL (6), Power BI (4), Data Modelling & Warehousing (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.
WITH touches AS (
SELECT t.user_id, t.channel, t.touch_ts,
ROW_NUMBER() OVER (PARTITION BY t.user_id ORDER BY t.touch_ts) AS first_rank,
ROW_NUMBER() OVER (PARTITION BY t.user_id ORDER BY t.touch_ts DESC) AS last_rank
FROM marketing_touches t
JOIN conversions c
ON c.user_id = t.user_id
AND t.touch_ts <= c.converted_ts
)
SELECT user_id,
MAX(CASE WHEN first_rank = 1 THEN channel END) AS first_touch,
MAX(CASE WHEN last_rank = 1 THEN channel END) AS last_touch
FROM touches
GROUP BY user_id;
03
What is the difference between a t-test and a z-test?
Both compare means. A z-test assumes the population standard deviation is known or the sample is large, so the normal distribution applies. A t-test estimates standard deviation from the sample and uses the t-distribution, which has heavier tails for small samples. In practice analysts mostly use t-tests: one-sample, independent two-sample (Welch's version if variances differ) or paired, for before-after measurements on the same users.
04
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.
05
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;
06
Write a DAX measure for year-over-year sales growth percentage.
Sales LY =
CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
Sales YoY % =
VAR cur = [Total Sales]
VAR prev = [Sales LY]
RETURN
DIVIDE(cur - prev, prev)
-- needs a marked date table; format the measure as a percentage
Clean as close to the source as is practical and in a repeatable tool. Fixes everyone needs belong upstream in the database or warehouse via SQL so all reports share them. Power Query suits report-specific shaping and Excel users. Python is best for complex logic like fuzzy matching or regex at scale. Manual Excel edits are fine for one-off exploration but should not be part of recurring reports because they cannot be reproduced.
10
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.
Incrementality testing measures sales that happened because of a campaign, compared with what would have happened anyway. You hold out a random group of users or a set of matched cities from the campaign and compare their conversions with the exposed group. Attribution tools often over-credit channels like retargeting and brand search, which reach people already likely to buy, so incrementality tests show the true lift and the true cost per incremental sale.
12
Find customers who ordered in January but not in February.
Pull data with SQL or APIs, clean and aggregate with Pandas, build charts and an Excel or PDF output, and schedule it with cron or Airflow with logging and alerts.
14
Write Python code for a simple difference-in-differences estimate of a store pilot.
Situation: the support team was judged only on tickets closed, which encouraged rushed replies. Task: propose a better measure. Action: I analysed reopen rates and satisfaction scores, then proposed first-contact resolution rate with clear rules for what counts as resolved. I tested it on three months of history and reviewed edge cases with team leads. Result: it became the main KPI and reopen rates fell 18% in a quarter.
Peeking means checking the p-value repeatedly during a test and stopping as soon as it drops below 0.05. Each look is another chance for random noise to look significant, so the real false positive rate can rise from 5% to 20% or more. Fix it by fixing the sample size in advance and analysing once, or by using methods built for continuous monitoring, such as sequential testing or always-valid confidence intervals.
apply runs a Python function on each row or column of a DataFrame, or each value of a Series. It is flexible but slow on large data because it loops in Python rather than using optimised C code. For example, df['total'] = df['qty'] * df['price'] is far faster than an apply with a lambda. Prefer vectorised operations, np.where, np.select, map with a dictionary, or string accessor methods first.
20
Write SQL to get each order's customer city as it was on the order date, using an SCD Type 2 customer dimension.
-- dim_customer has valid_from, valid_to (NULL for current row)
SELECT o.order_id,
o.order_date,
c.customer_id,
c.city
FROM fact_orders o
JOIN dim_customer c
ON c.customer_id = o.customer_id
AND o.order_date >= c.valid_from
AND o.order_date < COALESCE(c.valid_to, DATE '9999-12-31');
A dataflow is Power Query running in the Power BI Service. It extracts and cleans data once and stores the result so many datasets can reuse it. For example, a single cleaned Customer table can feed sales, finance and marketing reports, ensuring consistent logic and reducing load on the source. Dataflows suit shared, reusable preparation; logic that only one report needs can stay in that report's Power Query.
22
How do you make a dashboard accessible, for example for colour-blind users?
Avoid red-green as the only difference; use colour-blind-safe palettes such as blue and orange, and add icons, labels or patterns so meaning never depends on colour alone. Ensure enough contrast between text and background, use readable font sizes, and add alt text to visuals. Keep a logical tab order for keyboard users, and offer a table view of chart data. Accessible dashboards are usually clearer for everyone.
23
Write a DAX measure for cumulative sales from the start of the data to the selected date.
Cumulative Sales =
VAR maxDate = MAX('Date'[Date])
RETURN
CALCULATE(
[Total Sales],
FILTER(ALL('Date'[Date]), 'Date'[Date] <= maxDate)
)
-- for a running total that resets each year use TOTALYTD([Total Sales], 'Date'[Date])
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.
26
Traffic suddenly doubled from one city, with near-zero engagement time and no conversions. What is happening and what do you do?
This is very likely bot or spam traffic, or a misfiring tag. Check the user agents, IP ranges, hostnames, screen resolutions and landing pages for that traffic. If it is bots, exclude it with filters or data segments in analytics and in your reporting layer, and inform the web team so they can block it. Then restate the affected dashboard numbers, because bots inflate sessions and pull down conversion rate.
Situation: our team moved 15 Excel and older BI reports into Tableau. Task: I owned the sales set. Action: I listed every metric with its definition, rebuilt the shared data source first, then the dashboards, and ran side-by-side number checks for two weeks with the old reports. I held short demos for users. Result: all reports went live with matching numbers, and refresh effort fell from a day to an automatic schedule.
IN checks whether a value is in a list or subquery result; EXISTS checks whether a subquery returns at least one row and stops at the first match. Results are the same in most cases, and modern optimisers often run them similarly. The big difference is NOT IN with NULLs: if the subquery returns any NULL, NOT IN returns no rows. NOT EXISTS behaves correctly, so prefer it for anti-joins.
31
Write pandas code to find the top 2 products by revenue in each region.
Extract filters decide which rows go into the extract file at all, which reduces its size and speeds every view; filtered rows are simply not available. Data source filters apply to all worksheets using that data source, for live or extract, and are useful for security or scope, such as only the India business. Both act before context filters. Normal worksheet filters only affect one sheet.
33
What are leading and lagging indicators? Give examples.
Lagging indicators report outcomes that have already happened, such as monthly revenue, profit or churn; they are accurate but too late to change. Leading indicators move earlier and predict outcomes, such as qualified leads, demo bookings, cart additions or support ticket backlog. A sales dashboard should pair them: pipeline coverage today signals revenue next quarter. Leading indicators need validation that they really predict the lagging result.
SUMIFS sums values meeting several criteria and is fast. SUMPRODUCT multiplies arrays and adds them, handling more complex conditions and weighted totals.
35
How do you handle large CSV files that do not fit in memory in Python?
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.
Keep a cleaning log or README listing each rule, why it exists and its impact, for example removed 1,240 test orders flagged by email domain. Code-based cleaning in SQL, Python or Power Query steps is itself documentation if steps are named clearly. Record counts before and after each major step. This lets reviewers trust the numbers, helps a colleague rerun the work, and answers the question why your total differs from the raw file.
38
Orders arrive in the warehouse with customer IDs that do not exist yet in the customer dimension. What do you do?
These are late-arriving dimensions. Do not drop the orders, or revenue will be understated. Common practice is to insert a placeholder customer row with the natural key and 'Unknown' attributes, link the order to it, and update that row when full details arrive. Alternatively map to a single Unknown member with key -1. Also monitor how many such rows appear daily, because a spike usually means a broken feed.
Build PivotTables from a clean table, add PivotCharts and slicers, link KPIs with formulas, and refresh from Power Query so everything updates together.
40
Write pandas code to convert daily sales into weekly totals, showing zero for days with no sales.
df['date'] = pd.to_datetime(df['date'])
daily = (df.groupby('date')['sales'].sum()
.asfreq('D', fill_value=0)) # adds missing days as 0
weekly = daily.resample('W-MON', label='left', closed='left').sum()
print(weekly.head())
# W-MON with label/closed left gives weeks starting on Monday
Frequently asked questions
What do Data Analytics interviews for ₹20–25 LPA roles test?
At ₹20–25 LPA the focus is usually on: leading and reviewing others’ work, planning and estimation, strategy and roadmap input, cross-team collaboration. This page's set is weighted towards Python for Analysis (7), SQL (6), Power BI (4), Data Modelling & Warehousing (4).
Which companies pay ₹20–25 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 ₹20–25 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 ₹20–25 LPA.