Skip to content
Data Analytics
All courses OffersPlacement Report
‹ Back to previous sectionCourse homeAll page URLs

Business Intelligence Analyst Interview Questions & Answers

40 questions for Business Intelligence Analyst interviews — 4 easy, 33 medium, 3 hard.

About the Business Intelligence Analyst role

A business intelligence analyst turns company data into trusted reports and dashboards that teams use every day. The role involves writing SQL, designing star-schema data models, building Power BI or Tableau dashboards and defining KPIs with business teams. Interviews test SQL joins and window functions, dimensional modelling, DAX or calculated fields, dashboard design choices and how you handle mismatched numbers between reports. Expect a live SQL or dashboard task plus stakeholder scenario questions.

Star schema data modelling Advanced SQL Power BI and DAX KPI definition Report performance tuning
Solved for this roleStart 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 the difference between SUM and SUMX in DAX?

Power BIMedium
Save to ▾
Show answer
SUM adds up a single column in the current filter context. SUMX is an iterator: it goes row by row over a table, evaluates an expression for each row, then sums the results. Use SUMX when the value must be computed per row first, for example SUMX(Sales, Sales[Qty] * Sales[Price]). Iterators are flexible but can be slower on very large tables, so storing a simple column is sometimes better.
02

What is a gateway in Power BI?

Power BIMedium
Save to ▾
Show answer
A bridge that lets the Service refresh data from on-premises sources securely on a schedule.
03

What is the difference between ETL and ELT?

Data Modelling & WarehousingEasy
Save to ▾
Show answer
In ETL, data is extracted from sources, transformed on a separate engine and then loaded into the warehouse. In ELT, raw data is loaded first and transformed inside the warehouse using SQL, often with a tool like dbt. Cloud warehouses with cheap storage and scalable compute made ELT the common choice in 2026, because raw data stays available for reprocessing. ETL still suits cases needing masking or heavy cleansing before data lands.
04

Tell me about a time an experiment result went against what the team expected.

A/B Testing & ExperimentsMedium
Save to ▾
Show answer
Situation: the team was sure a shorter signup form would raise registrations. Task: I owned the test analysis. Action: registrations rose 6%, but I checked the guardrail and found seven-day activation fell, because we had removed a question that personalised onboarding. I shared both results with the segment breakdown. Result: we kept the short form but moved the question to the first screen after signup, which recovered activation.
05

How do you calculate a running total?

SQLMedium
Save to ▾
Show answer
SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) adds up values cumulatively.
06

What is the difference between ALL, ALLEXCEPT and REMOVEFILTERS in DAX?

Power BIHard
Save to ▾
Show answer
ALL removes filters from a table or columns and can also return all rows as a table. REMOVEFILTERS only removes filters and is clearer inside CALCULATE. ALLEXCEPT removes all filters on a table except the columns you list. A common use is share of total: DIVIDE([Sales], CALCULATE([Sales], ALL(Product))) gives each product's percentage, while ALLEXCEPT(Product, Product[Category]) gives share within its category.
07

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.
08

Which KPIs would you include in an HR attrition dashboard?

Dashboards and KPIsMedium
Save to ▾
Show answer
Core KPIs: headcount, monthly and annualised attrition rate, voluntary versus involuntary split, and early attrition within the first 90 days or first year. Breakdowns by department, location, manager, tenure band, performance rating and salary band show where it concentrates. Add hiring metrics such as time to fill and offer acceptance rate, and exit reasons from exit interviews. Protect privacy by hiding very small groups.
09

What is the logical order in which SQL executes a SELECT query?

SQLMedium
Save to ▾
Show answer
Although written as SELECT first, a query is logically processed as FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY and finally LIMIT. This explains common errors: you cannot use a SELECT alias in WHERE because SELECT runs later, but you can use it in ORDER BY. It also explains why aggregates belong in HAVING, not WHERE, and why window functions cannot be filtered in the same query level.
10

What does CALCULATE do in DAX?

Power BIHard
Save to ▾
Show answer
CALCULATE evaluates an expression in a modified filter context, e.g. CALCULATE([Total Sales], Region[Name] = "North"). It is the key function for time intelligence and comparisons.
11

What is Power Query's role in Power BI?

Power BIMedium
Save to ▾
Show answer
It extracts and transforms data using recorded steps in M language, such as merging, unpivoting, changing types and removing errors, before loading to the model.
12

What is Power BI and its main components?

Power BIEasy
Save to ▾
Show answer
A Microsoft BI tool made of Power BI Desktop for building reports, the Service for sharing, and Mobile; it connects, models and visualises data.
13

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

Dashboards and KPIsMedium
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
14

Write pandas code to compute month-on-month percentage change and cumulative sales for each store.

Python for AnalysisMedium
Save to ▾
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
15

How should you lay out a dashboard page?

Dashboards and KPIsEasy
Save to ▾
Show answer
Readers scan from the top-left, so place the most important KPIs as cards across the top, then trend charts, then breakdowns and detail tables at the bottom. Group related visuals, align them on a grid and keep consistent spacing, fonts and colours. Put filters in one consistent place. Limit a page to roughly five to eight visuals that answer related questions, and give the page a clear title stating its purpose.
16

What is a correlated subquery?

SQLMedium
Save to ▾
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).
17

Tell me about a time requirements kept changing on a report you were building.

Power BIMedium
Save to ▾
Show answer
Situation: a sales head kept adding new metrics while I was building a Power BI report. Task: deliver on time without endless rework. Action: I wrote a one-page scope with the agreed KPIs, shared a rough mock-up first, and logged new asks in a backlog with effort estimates. We agreed phase one and phase two. Result: phase one went live on schedule, and later requests were planned instead of disrupting the work.
18

How do you pivot rows into columns in SQL, for example sales by quarter as columns?

SQLMedium
Save to ▾
Show answer
SELECT region, SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) AS q1, SUM(CASE WHEN quarter = 'Q2' THEN amount ELSE 0 END) AS q2, SUM(CASE WHEN quarter = 'Q3' THEN amount ELSE 0 END) AS q3, SUM(CASE WHEN quarter = 'Q4' THEN amount ELSE 0 END) AS q4 FROM sales GROUP BY region; -- conditional aggregation works in every database; some also offer PIVOT
19

What is a CTE and why use it?

SQLMedium
Save to ▾
Show answer
A Common Table Expression is a named temporary result defined with WITH that makes complex queries readable and allows recursion.
20

What is the difference between a view and a materialised view?

SQLMedium
Save to ▾
Show answer
A view stores a query and runs it each time; a materialised view stores the result physically and must be refreshed, making reads faster.
21

Import mode vs DirectQuery?

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

What are indexes and when can they hurt?

SQLMedium
Save to ▾
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.
23

What is the difference between DELETE, TRUNCATE and DROP?

SQLEasy
Save to ▾
Show answer
DELETE removes chosen rows and can be rolled back; TRUNCATE empties the table quickly; DROP removes the table structure itself.
24

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

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

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.
26

Tell me about a time you disagreed with your manager about how to measure something.

Data StorytellingMedium
Save to ▾
Show answer
Situation: my manager wanted to report average resolution time for support tickets. Task: I felt a few very old tickets distorted the average. Action: I prepared a quick comparison showing mean versus median and the percentage resolved within 24 hours, and explained why the median was more representative. Result: we agreed to show median and the 24-hour rate, keeping the mean in a tooltip, and the team stopped chasing misleading spikes.
27

Difference between UNION and UNION ALL?

SQLMedium
Save to ▾
Show answer
UNION removes duplicate rows and sorts internally; UNION ALL keeps every row and is faster.
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

What does CASE WHEN do?

SQLMedium
Save to ▾
Show answer
It adds conditional logic inside queries, for example bucketing ages or flagging values, returning different results per condition.
30

What are relationships and cardinality in Power BI?

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

What is DAX and how does it differ from Excel formulas?

Power BIMedium
Save to ▾
Show answer
DAX is the formula language for Power BI models. It works on columns and tables with filter context rather than on individual cells.
32

Explain time intelligence functions.

Power BIMedium
Save to ▾
Show answer
DAX functions like TOTALYTD, SAMEPERIODLASTYEAR and DATEADD compare periods; they need a proper continuous date table marked as a date table.
33

RANK vs DENSE_RANK vs ROW_NUMBER?

SQLMedium
Save to ▾
Show answer
ROW_NUMBER gives a unique sequence. RANK gives ties the same rank and skips the next (1, 1, 3). DENSE_RANK gives ties the same rank without gaps (1, 1, 2).
34

Traffic suddenly doubled from one city, with near-zero engagement time and no conversions. What is happening and what do you do?

Web & Marketing AnalyticsMedium
Save to ▾
Show answer
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.
35

How do you present insights to non-technical stakeholders?

Data StorytellingMedium
Save to ▾
Show answer
Lead with the answer, use one simple chart per point, translate numbers into business impact (₹, %), avoid jargon and end with a clear recommendation and next step.
36

Many reporting queries in your team use SELECT DISTINCT to remove duplicate rows. What is the risk?

SQLMedium
Save to ▾
Show answer
SELECT DISTINCT often hides a real problem, such as a join on the wrong key or a one-to-many join that multiplies rows. The output looks clean, but sums computed before the DISTINCT, or legitimate repeated rows, can be wrong, and DISTINCT adds a costly sort on large tables. Instead, find why duplicates appear, fix the join or aggregate the child table first, and add a uniqueness test on the key.
37

After joining orders to payments, your revenue total doubles. What went wrong?

SQLMedium
Save to ▾
Show answer
This is a join fan-out: an order has more than one matching payment row, for example split payments or retries, so each order line is repeated and sums are inflated. Check by counting rows per key on both sides. Fix it by aggregating payments to one row per order before joining, joining at the correct grain, or using the right key. Always compare row counts and totals before and after a join.
38

Orders arrive in the warehouse with customer IDs that do not exist yet in the customer dimension. What do you do?

Data Modelling & WarehousingMedium
Save to ▾
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.
39

How do you make a dashboard accessible, for example for colour-blind users?

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

What is the difference between a table and a normal range?

ExcelMedium
Save to ▾
Show answer
A table auto-expands, supports structured references, banded rows and slicers, and keeps formulas consistent as rows are added.

Frequently asked questions

What does a Business Intelligence Analyst do?

A business intelligence analyst turns company data into trusted reports and dashboards that teams use every day. The role involves writing SQL, designing star-schema data models, building Power BI or Tableau dashboards and defining KPIs with business teams. Interviews test SQL joins and window functions, dimensional modelling, DAX or calculated fields, dashboard design choices and how you handle mismatched numbers between reports. Expect a live SQL or dashboard task plus stakeholder scenario questions.

Which skills are tested in a Business Intelligence Analyst interview?

Expect questions on Star schema data modelling, Advanced SQL, Power BI and DAX, KPI definition, Report performance tuning. Most rounds also ask you to explain a project and solve one practical problem live.

How do I prepare for a Business Intelligence Analyst interview?

Revise Data Modelling & Warehousing, Power BI, SQL, Dashboards and KPIs first using the topic sheets, practise the questions on this page aloud, then do one full mock interview to practise timing.

Topic sheets for this role

Data Modelling & Warehousing questionsPower BI questionsSQL questionsDashboards and KPIs questions

Other roles

Data AnalystPower BI DeveloperSQL AnalystReporting Analyst / MIS AnalystData Analyst InternSenior Data AnalystMarketing Analyst

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