01
How do you find duplicate rows in a table?
Show answer
SELECT col, COUNT(*) FROM t GROUP BY col HAVING COUNT(*) > 1; use ROW_NUMBER() partitioned by the key to delete extras.
Data Analytics Interview Questions for Consulting Companies
40 questions weighted to the interview style of consulting companies — 4 easy, 31 medium, 5 hard.
How consulting companies interview for Data Analytics
Consulting firms look for people who can structure messy business problems and explain them simply to clients. Expect case and guesstimate questions, data interpretation, and questions on how you handled stakeholders, along with the technical skills for the role.
This set focuses on Python for Analysis (6), Power BI (5), SQL (4), Data Modelling & Warehousing (4).
Technical interview Partner or HR round Managerial round Online assessment (aptitude + technical) HR round
Solved — consulting companiesStart 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
02
Write pandas code to find the top 2 products by revenue in each region.
Show answer
top2 = (
df.groupby(['region', 'product'], as_index=False)['revenue']
.sum()
.sort_values(['region', 'revenue'], ascending=[True, False])
.groupby('region')
.head(2)
)
print(top2)
03
Import mode vs DirectQuery?
Show answer
Import loads data into memory for fast visuals; DirectQuery queries the source live, giving fresh data but slower performance and some DAX limits.
04
Your pandas script shows SettingWithCopyWarning and a new column does not update. What is happening and how do you fix it?
Show answer
It warns that you may be modifying a temporary copy of a slice, so the change might not reach the original DataFrame, for example df[df.sales > 0]['flag'] = 1. Avoid it by using a single loc call, df.loc[df.sales > 0, 'flag'] = 1, or by calling .copy() when you intentionally create a new subset. Newer pandas versions with copy-on-write make behaviour more predictable, but clear loc assignments remain best practice.
05
Write SQL to get each order's customer city as it was on the order date, using an SCD Type 2 customer dimension.
Show answer
-- 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');
06
What is data profiling?
Show answer
Data profiling is a quick systematic look at a dataset before analysis: row counts, data types, null percentages, distinct counts, minimum and maximum, common values and value patterns per column. In pandas, df.info(), df.describe(include='all') and value_counts() cover most of it; Power Query has column quality and distribution views. Profiling reveals problems like a date column stored as text or a status field with unexpected values early.
07
Mean vs median vs mode — when do you use which?
Show answer
Mean for symmetric data without outliers, median for skewed data (salaries, house prices), mode for categories (the most common city).
08
How do you implement row-level security in Tableau?
Show answer
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.
09
What is Row-Level Security?
Show answer
Rules that restrict data per user role, for example sales managers seeing only their region, defined with DAX filters and assigned in the Service.
10
What are indexes and when can they hurt?
Show answer
Indexes speed up lookups by keeping sorted pointers. They slow writes and use space, so index columns used in filters and joins, not every column.
11
How do you measure impact when a proper A/B test is not possible?
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.
12
Your NOT IN query unexpectedly returns zero rows. Why might that happen, and how do EXISTS and IN differ?
Show answer
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.
13
What is the difference between Power BI Desktop, Power BI Service and Power BI Report Server?
Show answer
Power BI Desktop is the free Windows application where you connect data, model it, write DAX and design reports. Power BI Service is the cloud platform where reports are published, shared, scheduled for refresh, and organised into workspaces and apps. Power BI Report Server is an on-premises option for organisations that cannot use the cloud, with fewer features. Most analysts build in Desktop and share through the Service.
14
How would you investigate a sudden 20% drop in daily sales?
Show answer
Check data pipelines first, then segment by channel, region, product and device, compare to the same period last year, and look for launches, outages or campaigns.
15
Your test increased conversion rate by 3% but average order value dropped by 5%. What do you recommend?
Show answer
Look at the metric that reflects business value, such as revenue or margin per visitor, which combines both effects. Here revenue per visitor is roughly 1.03 times 0.95, about 2% lower, so the change may hurt. Check whether the drop is significant, which segments drive it, and whether new buyers are making smaller first orders that grow later. Without a strong reason, I would not ship it as is.
16
Email open rates suddenly jumped from 22% to 45% without any campaign change. Should you celebrate?
Show answer
No. Privacy features in some mail apps pre-load email images, which fires the open tracking pixel even if the person never reads the email, so open rates get inflated. Check opens by email client to confirm. Move reporting towards clicks, click-to-send rate, conversions and unsubscribes, which reflect real behaviour, and avoid using opens alone to pick winning subject lines or to define engaged subscribers.
17
Tell me about a time you had to say no to a stakeholder's request.
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.
18
Write Python code to extract valid 10-digit Indian mobile numbers from a messy text column.
Show answer
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)
19
What is the unit of randomisation in an experiment, and how do you choose it?
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.
20
A stakeholder wants 20 more visuals added to an already crowded dashboard page. How do you respond?
Show answer
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.
21
Write an Excel formula to look up a price using two criteria, product and city.
Show answer
=XLOOKUP(1, (A2:A500=G2) * (B2:B500=H2), C2:C500, "Not found")
-- A = product, B = city, C = price
-- G2 and H2 hold the product and city to find
-- each condition returns TRUE/FALSE; multiplying gives 1 only where both match
22
Write pandas code to compute month-on-month percentage change and cumulative sales for each store.
Show answer
df = df.sort_values(['store', 'month'])
df['mom_pct'] = (
df.groupby('store')['sales'].pct_change() * 100
).round(1)
df['cum_sales'] = df.groupby('store')['sales'].cumsum()
# first month of each store has NaN for mom_pct, which is expected
23
Average delivery time improved by 4 minutes, but customer complaints about late deliveries went up. How do you investigate?
Show answer
An average can improve while the tail gets worse. I would look at the distribution: median, 90th and 95th percentiles and the share of orders beyond the promised time. Then break it down by city, time slot and restaurant to see where late orders cluster. Also check whether promised times changed, whether complaint tracking changed and whether a few very fast orders pulled the mean down.
24
What is bidirectional cross-filtering in Power BI and when is it risky?
Show answer
Normally filters flow from the one side to the many side, for example from Product to Sales. Bidirectional filtering lets filters also flow back, so a Sales filter can limit Product. It helps in some many-to-many or bridge-table cases. The risks are ambiguous filter paths, unexpected results and slower performance. Prefer single direction and use CROSSFILTER inside a specific measure when you need both directions only there.
25
Your pandas script takes 40 minutes on 10 million rows. How do you speed it up?
Show answer
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.
26
How do you decide the sample size for a survey?
Show answer
It depends on margin of error, confidence level and expected variability; larger samples give smaller errors with diminishing returns.
27
Write Python code to extract UTM source, medium and campaign from a column of landing page URLs.
Show answer
import pandas as pd
from urllib.parse import urlparse, parse_qs
def get_utms(url):
params = parse_qs(urlparse(str(url)).query)
return pd.Series({
'source': params.get('utm_source', ['(direct)'])[0].lower(),
'medium': params.get('utm_medium', ['(none)'])[0].lower(),
'campaign': params.get('utm_campaign', ['(not set)'])[0].lower(),
})
df[['source', 'medium', 'campaign']] = df['landing_url'].apply(get_utms)
28
Orders arrive in the warehouse with customer IDs that do not exist yet in the customer dimension. What do you do?
Show answer
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.
29
Your Top 10 products filter in Tableau ignores the category filter. Why, and how do you fix it?
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.
30
What is vectorisation and why does it matter?
Show answer
Applying operations to whole arrays at once instead of Python loops, which is far faster because it runs in optimised compiled code.
31
Explain Matplotlib vs Seaborn.
Show answer
Matplotlib is the low-level plotting library with full control; Seaborn builds on it with attractive statistical charts and simpler syntax.
32
Your event logs store timestamps in UTC, but the business reports daily sales in IST, and late-night orders land on the wrong day. How do you fix it?
Show answer
Convert timestamps to the business time zone before deriving the date. IST is UTC plus 5 hours 30 minutes, so an order at 20:00 UTC belongs to the next day in IST. In SQL use AT TIME ZONE or CONVERT_TZ, and in pandas use tz_localize('UTC') followed by tz_convert('Asia/Kolkata'). Store raw UTC, add a local date column in the model, and document which one each report uses.
33
A stakeholder says the Power BI report shows different revenue than their Excel file. How do you troubleshoot?
Show answer
I would first agree on definitions: gross or net, including tax or returns, order date or invoice date. Then compare one small slice, such as one month and one region, in both. I check filters on the report and page, relationships that drop unmatched rows, refresh time versus the Excel extract time, and duplicate rows. Once the gap is explained, I document the definition in the report so the question does not repeat.
34
Your workbook has 200,000 rows and hundreds of VLOOKUPs and takes minutes to recalculate. What do you do?
Show answer
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.
35
What is data cleaning and why does it take so much of an analyst's time?
Show answer
Data cleaning means fixing or removing incorrect, incomplete, duplicated or inconsistent data so analysis is trustworthy. Real data comes from many systems and manual entry, so you meet typos, mixed formats, missing values and duplicates. Analysts often spend half or more of a project on it because every later chart and model depends on it. Wrong inputs give confident but wrong answers, which is worse than no answer.
36
What is a correlated subquery?
Show answer
A correlated subquery refers to a column from the outer query, so it is logically evaluated once per outer row. Example: find employees earning above their department average with WHERE salary > (SELECT AVG(salary) FROM emp e2 WHERE e2.dept_id = e1.dept_id). It is readable but can be slow on large tables. The same result is often faster with a window function such as AVG(salary) OVER (PARTITION BY dept_id).
37
Organic search traffic fell 40% overnight. How do you investigate?
Show answer
First check whether it is a tracking problem: did the analytics tag break on some templates, did consent settings change, does server log or backend order data show the same fall? If the drop is real, check the search console for impressions and rankings, recent site releases such as changed URLs, missing redirects, noindex tags or robots.txt changes, and whether a search algorithm update or seasonal pattern matches. Then break the drop down by page group.
38
You inherit a monthly Excel report and suspect some numbers are hard-typed over formulas. How do you audit it?
Show answer
Use Go To Special and select Constants with Numbers to highlight hard-typed values inside formula areas. Show Formulas (Ctrl+`) to scan columns for inconsistencies, and use Trace Precedents and Dependents on key totals. Inquire or Spreadsheet Compare, if available, can flag inconsistent formulas. Then fix the overwritten cells, protect formula ranges and add check totals that compare with the source data each month.
39
What is columnar storage and why do analytical warehouses use it?
Show answer
Columnar storage keeps all values of one column together on disk instead of storing full rows together. Analytical queries usually read a few columns across millions of rows, so the engine reads only those columns. Similar values sit side by side and compress very well, which cuts storage and scan cost. The trade-off is slower single-row inserts and updates, which is why OLTP databases stay row-based.
40
A junior analyst wants to load one huge flat table with every column into Power BI instead of a star schema. How do you advise them?
Show answer
A flat table is quick to start and fine for a small one-off analysis. But at scale it bloats the model because repeated text attributes compress poorly, makes DAX filters and time intelligence harder, and breaks when you add a second fact like targets or returns. I would show them a star schema with one fact and a few dimensions, compare model size and measure speed, and let the results convince them.
Consulting companies (13)
Consulting
Accenture
Data Analytics interview questions
Bengaluru, Hyderabad, Pune +2
View questionsConsulting
Deloitte
Data Analytics interview questions
Hyderabad, Bengaluru, Mumbai +2
View questionsConsulting
EY
Data Analytics interview questions
Bengaluru, Kolkata, Gurugram +2
View questionsConsulting
KPMG
Data Analytics interview questions
Gurugram, Bengaluru, Mumbai +2
View questionsConsulting
PwC
Data Analytics interview questions
Kolkata, Bengaluru, Mumbai +2
View questionsConsulting
Publicis Sapient
Data Analytics interview questions
Gurugram, Noida, Bengaluru
View questionsConsulting
Thoughtworks
Data Analytics interview questions
Pune, Bengaluru, Chennai +2
View questionsConsulting
ZS Associates
Data Analytics interview questions
Pune, Gurugram, Bengaluru
View questionsConsulting
McKinsey & Company
Data Analytics interview questions
Gurugram, Mumbai, Bengaluru +1
View questionsConsulting
Boston Consulting Group
Data Analytics interview questions
Mumbai, New Delhi, Bengaluru +1
View questionsConsulting
Bain & Company
Data Analytics interview questions
Gurugram, Mumbai, Bengaluru
View questionsConsulting
Grant Thornton Bharat
Data Analytics interview questions
Gurugram, Mumbai, Bengaluru +3
View questionsConsulting
Protiviti
Data Analytics interview questions
Gurugram, Mumbai, Bengaluru +1
View questionsFrequently asked questions
How are Data Analytics interviews at consulting companies different?
Consulting firms look for people who can structure messy business problems and explain them simply to clients. Expect case and guesstimate questions, data interpretation, and questions on how you handled stakeholders, along with the technical skills for the role.
Which consulting companies are covered?
This page links to 13 consulting companies, including Accenture, Deloitte, EY, KPMG, PwC, Publicis Sapient, Thoughtworks, ZS Associates. Each has its own page with rounds and a separate practice set.
What rounds should I expect?
Common rounds at these companies include: Technical interview; Partner or HR round; Managerial round; Online assessment (aptitude + technical); HR round. The exact process varies by company and role.
Other company types
More ways to practise
Company-wise Data Analytics questionsPackage-wise Data Analytics questionsExperience-wise Data Analytics questionsRole-wise Data Analytics questionsCity-wise Data Analytics questionsMost asked Data Analytics questionsScenario-based questionsPractical / hands-on questionsHR & behavioural questionsData Analytics mock interviewOnline assessment questionsTechnical round questionsManagerial round questionsData Analytics topic sheets