01
How do you create a dynamic dashboard in Excel?
Show answer
Build PivotTables from a clean table, add PivotCharts and slicers, link KPIs with formulas, and refresh from Power Query so everything updates together.
Data Analyst Interview Questions & Answers
40 questions for Data Analyst interviews — 1 easy, 36 medium, 3 hard.
About the Data Analyst role
A data analyst collects, cleans and analyses business data to answer questions such as why sales fell or which customers are most valuable, then shares findings through reports and dashboards. Interviews test SQL querying, Excel, one BI tool such as Power BI or Tableau, basic statistics and the ability to explain insights clearly. Expect a live SQL round, a case question and behavioural questions about working with stakeholders.
SQL joins and window functions Excel lookups and PivotTables Power BI or Tableau dashboards Descriptive statistics Clear stakeholder communication
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
02
A report filters WHERE order_date = '2026-03-31' and misses almost all orders from that day. Why?
Show answer
order_date is probably a DATETIME or TIMESTAMP column, so the filter matches only rows at exactly midnight, 2026-03-31 00:00:00. Use a half-open range instead: WHERE order_date >= '2026-03-31' AND order_date < '2026-04-01'. This keeps the index usable, unlike wrapping the column in CAST or DATE(), and it correctly includes orders at 23:59:59.500. Also check the time zone of the stored timestamps.
03
How do you calculate month-over-month growth?
Show answer
Aggregate revenue by month, use LAG(revenue) OVER (ORDER BY month), then (revenue - prev) / prev x 100.
04
What is a slowly changing dimension? Explain Type 1 and Type 2.
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.
05
What is a CTE and why use it?
Show answer
A Common Table Expression is a named temporary result defined with WITH that makes complex queries readable and allows recursion.
06
Write SQL to show each category's percentage share of total revenue.
Show answer
SELECT category,
SUM(amount) AS revenue,
ROUND(100.0 * SUM(amount)
/ SUM(SUM(amount)) OVER (), 2) AS pct_share
FROM sales
GROUP BY category
ORDER BY revenue DESC;
-- SUM(SUM(amount)) OVER () is the grand total across all groups
07
What is the difference between SUM and SUMX in DAX?
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.
08
Explain INDEX and MATCH together.
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.
09
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.
10
What is the difference between Goal Seek and Solver?
Show answer
Goal Seek changes one input to hit a target output; Solver optimises with several variables and constraints, for example maximising profit under budget limits.
11
Write SQL to find the number of days between each customer's consecutive orders and their average gap.
Show answer
WITH gaps AS (
SELECT customer_id,
order_date,
order_date - LAG(order_date) OVER (
PARTITION BY customer_id ORDER BY order_date) AS days_since_prev
FROM orders
)
SELECT customer_id,
COUNT(*) AS orders,
AVG(days_since_prev) AS avg_gap_days
FROM gaps
GROUP BY customer_id
HAVING COUNT(*) > 1;
-- order_date is DATE (PostgreSQL); use DATEDIFF in other databases
12
What is a gateway in Power BI?
Show answer
A bridge that lets the Service refresh data from on-premises sources securely on a schedule.
13
What are absolute and relative cell references?
Show answer
Relative references change when copied (A1); absolute references stay fixed ($A$1); mixed references lock only the row or column (A$1 or $A1).
14
How do you handle errors like #N/A and #DIV/0!?
Show answer
Fix the root cause when possible, or wrap formulas with IFERROR or IFNA to return a clear fallback value.
15
What is the difference between a t-test and a z-test?
Show answer
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.
16
What is DAX and how does it differ from Excel formulas?
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.
17
What is a self join and when is it used?
Show answer
Joining a table to itself with aliases, useful for hierarchies such as employees and managers or comparing rows within the same table.
18
What is the LAMBDA function in Excel and when would you use it?
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.
19
What is the LET function in Excel and why would you use it?
Show answer
LET assigns names to intermediate results inside a single formula, for example =LET(sales, SUM(D2:D100), cost, SUM(E2:E100), (sales-cost)/sales). It makes long formulas readable and faster, because a repeated sub-calculation is evaluated once instead of several times. It is especially useful with dynamic arrays, where the same FILTER result might otherwise be recalculated three or four times in one nested formula.
20
Explain time intelligence functions.
Show answer
DAX functions like TOTALYTD, SAMEPERIODLASTYEAR and DATEADD compare periods; they need a proper continuous date table marked as a date table.
21
What is the difference between a dashboard and a report?
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.
22
Tell me about a time you handled a request for access to sensitive data.
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.
23
Explain correlation vs regression.
Show answer
Correlation shows the strength and direction of association; regression builds an equation to predict one variable from others.
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
Analytics shows 1,820 purchases last month but the order database shows 2,150. Which is right, and how do you explain the gap?
Show answer
The order database is the source of truth for counts and revenue. Analytics usually undercounts because of ad blockers, declined cookie consent, users closing the page before the confirmation tag fires, and payment redirects that break sessions. It can also overcount from duplicate tags on page refresh. Quantify the gap per month; if it is stable around 15%, use analytics for channel mix and trends and the database for totals.
26
Checkout conversion fell from 3.2% to 2.6% the week after an app release. How do you investigate?
Show answer
First confirm it is real: check tracking events, data delays and whether the denominator changed. Then segment by app version, platform, device, payment method and traffic source. If the drop is concentrated in the new version or one payment option, it points to a bug or UX change. Look at funnel steps to find where users drop. Compare with seasonality and campaigns, then share findings with product and engineering quickly.
27
When do you use a chi-square test?
Show answer
A chi-square test of independence checks whether two categorical variables are related, using a contingency table of counts. Example: is preferred payment method related to city tier? It compares observed counts with the counts expected if there were no relationship. Use it with counts, not percentages, and make sure expected counts are not too small, roughly at least 5 per cell. A significant result shows association, not causation.
28
What is Power Query's role in Power BI?
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.
29
What is the difference between a calculated field and a helper column in Excel?
Show answer
A calculated field lives inside a PivotTable and works on summarised data; a helper column is a normal column of formulas that feeds the PivotTable or other formulas.
30
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.
31
How do you detect and handle skewed data?
Show answer
Check histograms and skew(), then apply log, square-root or Box-Cox transforms, or use medians and robust statistics.
32
How do you pivot rows into columns in SQL, for example sales by quarter as columns?
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
33
How do you calculate a rolling average?
Show answer
df['sales'].rolling(window=7).mean() gives a 7-period moving average, useful to smooth daily noise.
34
What is the difference between a view and a materialised view?
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.
35
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.
36
What is the difference between a calculated table in DAX and a table created in Power Query?
Show answer
A Power Query table is built during data refresh using M, before data enters the model, and is compressed like any imported table. A DAX calculated table is computed after load from other model tables, using functions such as CALENDAR, SUMMARIZE or DISTINCT. Prefer Power Query for shaping and cleaning source data. Use calculated tables for model-dependent needs, such as a date table from existing data or a small what-if table.
37
Difference between a CTE, a subquery and a temp table?
Show answer
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.
38
How are NULLs handled in SQL?
Show answer
NULL means unknown, so comparisons with = fail; use IS NULL, COALESCE for defaults and remember most aggregates ignore NULLs except COUNT(*).
39
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.
40
What is cohort analysis?
Show answer
Grouping users by a shared start date or trait and tracking behaviour over time, for example monthly retention per signup month.
Frequently asked questions
What does a Data Analyst do?
A data analyst collects, cleans and analyses business data to answer questions such as why sales fell or which customers are most valuable, then shares findings through reports and dashboards. Interviews test SQL querying, Excel, one BI tool such as Power BI or Tableau, basic statistics and the ability to explain insights clearly. Expect a live SQL round, a case question and behavioural questions about working with stakeholders.
Which skills are tested in a Data Analyst interview?
Expect questions on SQL joins and window functions, Excel lookups and PivotTables, Power BI or Tableau dashboards, Descriptive statistics, Clear stakeholder communication. Most rounds also ask you to explain a project and solve one practical problem live.
How do I prepare for a Data Analyst interview?
Revise SQL, Excel, Power BI, Statistics and Case Studies 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
Other roles
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