The definitive question bank of 25 technical and scenario-based interview questions asked by top Indian analytics consultancies and product companies, with clear solutions in SQL, Excel, and Power BI.
Landing a high-paying Data Analyst position in India requires clearing a multi-stage technical screening process: timed SQL coding assessments, live Excel modeling tests, and scenario-based Power BI architecture rounds. Companies like Deloitte, EXL, Genpact, Mu Sigma, and high-growth Indian startups evaluate not just whether you know syntax, but whether you can reason through business edge cases. Below are the most critical questions and solutions asked across Indian analytics interview panels.
Q1: What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?
Answer: ROW_NUMBER() assigns a unique sequential integer regardless of ties. RANK() assigns the same rank to ties but skips subsequent rank numbers (e.g., 1, 2, 2, 4). DENSE_RANK() assigns the same rank to ties without skipping numbers (e.g., 1, 2, 2, 3).
Q2: How do you identify duplicate records without an ID column?
Answer: Use GROUP BY on all columns with "HAVING COUNT(*) > 1", or assign "ROW_NUMBER() OVER(PARTITION BY ...)" and filter where row_number > 1.
A favorite assessment question at top tech firms: Identify missing sequential transaction IDs or dates.
-- Finding Gaps in Sequential Invoice Numbers
WITH NumberedInvoices AS (
SELECT
invoice_id,
LEAD(invoice_id, 1) OVER (ORDER BY invoice_id ASC) AS next_invoice_id
FROM billing_records
)
SELECT
invoice_id + 1 AS gap_start,
next_invoice_id - 1 AS gap_end,
(next_invoice_id - invoice_id - 1) AS missing_count
FROM NumberedInvoices
WHERE next_invoice_id - invoice_id > 1;Q1: Explain Context Transition in Power BI.
Answer: Context transition occurs when a row context is converted into an equivalent filter context. This happens automatically whenever CALCULATE is called inside a row context (such as within SUMX or calculated columns), causing visual filters to apply across the data model.
Q2: What is the difference between DirectQuery and Import Mode?
Answer: Import Mode loads compressed data into the VertiPaq in-memory engine, offering maximum speed and full DAX support. DirectQuery leaves data in the underlying database, querying it dynamically on visual interaction; it supports real-time data but has DAX limitations and network latency.
Q3: How do you handle Many-to-Many relationships in Power BI?
Answer: Best practice is to create a Bridge/Dimension table with unique keys between the two tables, converting the relationship into two clean 1-to-many relationships, avoiding bidirectional cross-filtering hazards.
// DAX Measure: New vs Returning Customers Count
New Customers Count =
VAR CurrentCustomers = VALUES(Fact_Sales[Customer_ID])
VAR PreviousCustomers =
CALCULATETABLE(
VALUES(Fact_Sales[Customer_ID]),
FILTER(
ALL(Dim_Date[Date]),
Dim_Date[Date] < MIN(Dim_Date[Date])
)
)
RETURN
COUNTROWS(EXCEPT(CurrentCustomers, PreviousCustomers))Q1: When would you use INDEX-MATCH over XLOOKUP?
Answer: XLOOKUP is standard for modern Excel 365. However, INDEX-MATCH remains essential for backward compatibility with legacy corporate workbooks (.xls / Excel 2016 and older) and supports 3D multi-sheet lookups.
Q2: How do you calculate median sales per region without array formulas?
Answer: Modern Excel supports dynamic arrays: "=MEDIAN(FILTER(Sales_Amount, Region_Column = "North"))".
Preparing thoroughly with real interview scenarios transforms your confidence and guarantees success. At Datadrix Delhi (Dilshad Colony & online), our students undergo comprehensive mock technical interviews with former hiring managers from Deloitte, EXL, and Genpact.
Join Datadrix’s live cohort with 1:1 mentor code reviews, capstone projects, and 400+ hiring partner referrals.
Explore the ideal data analytics curriculum in Delhi NCR: essential tools (Excel, SQL, Power BI, Python), hands-on portfolio case studies, and localized hiring networks in Connaught Place, South Delhi, and Noida.
A complete blueprint connecting the entire analytics lifecycle: raw CSV ingestion in Excel, relational warehousing in PostgreSQL, exploratory data wrangling in Python Pandas, and executive reporting in Power BI.
The golden rule of Power BI development: "Push data transformations as far upstream as possible." Discover exact rules on when to transform in Power Query (M) vs compute in DAX for high-speed dashboards.