Skip to content

Data Analytics Interview Questions & Answers

All 414 unique questions in one place — 115 easy, 214 medium, 85 hard — grouped by topic.

Practise by topic

Solved overallStart 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 data validation in Excel?

ExcelEasy
Save to ▾
Show answer
Data → Data Validation restricts what can be entered in a cell (lists, number ranges, dates), reducing manual entry errors in shared sheets.
02

SUMIFS vs COUNTIFS?

ExcelEasy
Save to ▾
Show answer
SUMIFS adds values that meet several conditions; COUNTIFS counts rows that meet several conditions. Example: =SUMIFS(Sales, Region, "North", Month, "Jan")
03

What is the difference between COUNT, COUNTA and COUNTIF?

ExcelEasy
Save to ▾
Show answer
COUNT counts numbers, COUNTA counts non-empty cells and COUNTIF counts cells meeting one condition; COUNTIFS handles several conditions.
04

VLOOKUP vs INDEX-MATCH vs XLOOKUP?

ExcelEasy
Save to ▾
Show answer
VLOOKUP only looks to the right and breaks when columns are inserted. INDEX-MATCH looks in any direction and is robust. XLOOKUP is the modern option with exact match by default and left lookups.
05

What is a pivot table and when do you use it?

ExcelEasy
Save to ▾
Show answer
A pivot table summarises large data by dragging fields into rows, columns, values and filters — for example total sales by region and month — without writing formulas.
06

What does the IF function do and how do you nest it?

ExcelEasy
Save to ▾
Show answer
IF returns one result if a test is true and another if false. Nest IFs for multiple outcomes, or use IFS for cleaner formulas.
07

How do you remove duplicates in Excel?

ExcelEasy
Save to ▾
Show answer
Data tab, Remove Duplicates, pick the columns to compare. For a non-destructive way use UNIQUE() or conditional formatting to highlight repeats first.
08

What is conditional formatting?

ExcelEasy
Save to ▾
Show answer
It automatically formats cells based on rules such as colour scales, data bars or highlighting values above a target, making patterns visible at a glance.
09

Your FILTER formula shows #SPILL! instead of results. Why, and how do you fix it?

ExcelEasy
Save to ▾
Show answer
Dynamic array formulas such as FILTER, UNIQUE and SORT return several values that spill into neighbouring cells. #SPILL! appears when that spill range is blocked by existing data, merged cells, or when the formula sits inside an Excel Table, which does not allow spilling. Fix it by clearing the blocking cells, unmerging, or moving the formula outside the Table. Select the error cell to see the dashed border of the range it needs.
10

Write Excel formulas to get a sorted list of unique customer names and their count.

ExcelEasy
Save to ▾
Show answer
-- unique customers from column B, sorted A to Z =SORT(UNIQUE(B2:B1000)) -- number of unique customers =COUNTA(UNIQUE(B2:B1000)) -- unique customers sorted by total sales, highest first =SORTBY(UNIQUE(B2:B1000), SUMIFS(D2:D1000, B2:B1000, UNIQUE(B2:B1000)), -1)
11

How do you combine text from several cells with a separator in Excel?

ExcelEasy
Save to ▾
Show answer
-- join first and last name with a space =TEXTJOIN(" ", TRUE, A2, B2) -- all products of one order in a single cell, comma separated =TEXTJOIN(", ", TRUE, FILTER(C2:C500, A2:A500=F2)) -- older method =A2 & " " & B2 -- TRUE in TEXTJOIN skips empty cells, which CONCAT and & do not
12

What are named ranges in Excel and why are they useful?

ExcelEasy
Save to ▾
Show answer
A named range gives a cell or range a meaningful name, such as GST_Rate or SalesData, managed from the Name Manager. Formulas become readable, for example =Amount*GST_Rate instead of =B2*$H$1, and they do not break when you move the input cell. Names also work as data validation sources and in charts. For growing data, prefer Excel Tables, whose structured references expand automatically.
13

How do you calculate working days between two dates in Excel, excluding holidays?

ExcelEasy
Save to ▾
Show answer
-- A2 = start date, B2 = end date, Holidays = named range of holiday dates =NETWORKDAYS(A2, B2, Holidays) -- if the weekend is only Sunday (weekend code 11) =NETWORKDAYS.INTL(A2, B2, 11, Holidays) -- date that is 10 working days after A2 =WORKDAY(A2, 10, Holidays)
14

What does Flash Fill do in Excel, and when should you not rely on it?

ExcelEasy
Save to ▾
Show answer
Flash Fill (Ctrl+E) detects a pattern from one or two examples you type and fills the rest of the column, for example extracting first names or reformatting phone numbers. It is fast for one-time cleanup. However, it produces static values, not formulas, so it will not update when source data changes, and it can guess wrongly on irregular rows. For repeatable reports, use formulas or Power Query instead.
15

Tell me about a time you found an error in someone else's spreadsheet.

ExcelEasy
Save to ▾
Show answer
Situation: a teammate's incentive calculation sheet was about to go to payroll. Task: I was asked to review it. Action: I noticed totals did not match the sales extract, traced it to a SUM range that stopped at row 500 while data had grown to 640 rows, and told the teammate privately with the fix. Result: we converted the data to an Excel Table so ranges expand, and payouts went out correctly.
16

What is a PivotTable and when do you use it?

ExcelMedium
Save to ▾
Show answer
A tool that summarises large tables by dragging fields into rows, columns, values and filters, so you can slice sales by region or month without writing formulas.
17

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

How do you remove duplicates in Excel but keep the latest record?

ExcelMedium
Save to ▾
Show answer
Sort by date (newest first), then Data → Remove Duplicates on the key column — Excel keeps the first occurrence. Or use UNIQUE / a COUNTIFS helper column.
19

What is the difference between Goal Seek and Solver?

ExcelMedium
Save to ▾
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.
20

What are absolute and relative cell references?

ExcelMedium
Save to ▾
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).
21

Explain INDEX and MATCH together.

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

How do you create a dynamic dashboard in Excel?

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

How do you handle errors like #N/A and #DIV/0!?

ExcelMedium
Save to ▾
Show answer
Fix the root cause when possible, or wrap formulas with IFERROR or IFNA to return a clear fallback value.
24

How do SUMIFS and SUMPRODUCT differ?

ExcelMedium
Save to ▾
Show answer
SUMIFS sums values meeting several criteria and is fast. SUMPRODUCT multiplies arrays and adds them, handling more complex conditions and weighted totals.
25

What is the difference between a calculated field and a helper column in Excel?

ExcelMedium
Save to ▾
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.
26

What do the match_mode and search_mode arguments of XLOOKUP control?

ExcelMedium
Save to ▾
Show answer
match_mode decides how a value is matched: 0 exact (default), -1 exact or next smaller, 1 exact or next larger, and 2 wildcard. search_mode decides direction: 1 searches first to last, -1 searches last to first, and 2 or -2 use binary search on sorted data. Searching last to first is a quick way to fetch the latest entry for a customer without sorting, and next-smaller matching handles tax slabs or commission bands.
27

How does the FILTER function work in Excel? Write a formula to list all North region orders.

ExcelMedium
Save to ▾
Show answer
-- A2:D500 holds Order, Customer, Region, Amount =FILTER(A2:D500, C2:C500="North", "No orders") -- two conditions (AND) multiply the tests =FILTER(A2:D500, (C2:C500="North")*(D2:D500>10000), "No orders") -- OR conditions add the tests =FILTER(A2:D500, (C2:C500="North")+(C2:C500="East"))
28

What is the LET function in Excel and why would you use it?

ExcelMedium
Save to ▾
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.
29

What is Power Pivot and the Excel Data Model?

ExcelMedium
Save to ▾
Show answer
The Data Model is an in-memory columnar store inside Excel that holds several related tables, and Power Pivot is the interface to manage it. Instead of flattening data with VLOOKUPs, you load fact and dimension tables, create relationships and write DAX measures. It handles millions of rows that would not fit on a sheet, and PivotTables built on it can slice across tables. It is the same engine idea that Power BI uses.
30

Write an Excel formula to put invoices into ageing buckets (0-30, 31-60, 61-90, 90+ days).

ExcelMedium
Save to ▾
Show answer
-- C2 = invoice date, TODAY() = as-of date =LET(age, TODAY()-C2, IFS(age<=30, "0-30", age<=60, "31-60", age<=90, "61-90", TRUE, "90+")) -- then a PivotTable with bucket in rows and SUM of amount gives the ageing report
31

What is a Data Table in Excel's What-If Analysis?

ExcelMedium
Save to ▾
Show answer
A Data Table shows how a formula's result changes when one or two inputs vary. For example, place a loan EMI formula in a corner, list interest rates down the column and tenures across the row, then use What-If Analysis, Data Table to fill every combination. It is ideal for sensitivity analysis in pricing or budgeting. Large Data Tables slow recalculation, so set calculation to automatic except tables when needed.
32

You share a forecasting workbook and users keep overwriting formulas. How do you protect it?

ExcelMedium
Save to ▾
Show answer
Separate input cells from calculation cells and colour inputs clearly. Unlock only the input cells through Format Cells, Protection, then protect the sheet so formulas cannot be edited, and optionally hide formulas. Add data validation on inputs to block wrong values such as negative quantities. Protect workbook structure so sheets cannot be deleted, keep a version history, and add a short instructions sheet so users know where to type.
33

Tell me about a time you automated a manual Excel task.

ExcelMedium
Save to ▾
Show answer
Situation: our team spent about four hours every Monday copying sales exports from five branches into one summary sheet. Task: I was asked to cut that time. Action: I built a Power Query that read every file in a shared folder, cleaned headers and appended them, then a PivotTable and a few formulas produced the summary on refresh. Result: the report took ten minutes, copy-paste errors stopped, and two other teams reused the template.
34

Write an Excel formula to look up a price using two criteria, product and city.

ExcelMedium
Save to ▾
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
35

You inherit a monthly Excel report and suspect some numbers are hard-typed over formulas. How do you audit it?

ExcelMedium
Save to ▾
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.
36

How would you reconcile two large lists in Excel?

ExcelHard
Save to ▾
Show answer
Use XLOOKUP or COUNTIF to flag missing items in each list, or merge them in Power Query with a full outer join and filter unmatched rows.
37

Your workbook has 200,000 rows and hundreds of VLOOKUPs and takes minutes to recalculate. What do you do?

ExcelHard
Save to ▾
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.
38

What is the LAMBDA function in Excel and when would you use it?

ExcelHard
Save to ▾
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.
39

What is Power BI and its main components?

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

What are slicers and filters?

Save to ▾
Show answer
Slicers are on-page visual filters users click; filters can be set at visual, page or report level and may be hidden from users.
41

What is a date table in Power BI and why should you mark it as one?

Save to ▾
Show answer
A date table has one row per day with no gaps, covering the full range of your data, plus columns like year, month, quarter and week. Marking it as a date table tells Power BI to use it for time intelligence functions such as TOTALYTD and SAMEPERIODLASTYEAR. Without it, those functions can give wrong results and auto date tables bloat the model. Relate it to every fact table's date column.
42

What is the difference between Power BI Desktop, Power BI Service and Power BI Report Server?

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

A profit margin visual shows errors for products with zero sales. How do you fix it in DAX?

Save to ▾
Show answer
DIVIDE(numerator, denominator, alternate) handles division by zero or blank safely and returns blank or the alternate value you supply, so visuals do not show errors or infinity. The / operator returns an error or infinity in those cases. For example, DIVIDE([Profit], [Sales]) shows blank for a product with no sales. Use / only when you are certain the denominator can never be zero, because it is marginally faster.
44

How do you improve Power BI report performance?

Power BIMedium
Save to ▾
Show answer
Use a star schema, remove unused columns, prefer measures, reduce visuals per page, limit high-cardinality fields and use aggregations or incremental refresh.
45

What is Row-Level Security?

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

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

What does the CALCULATE function do?

Power BIMedium
Save to ▾
Show answer
It evaluates an expression after modifying the filter context, for example CALCULATE(SUM(Sales[Amount]), Region[Name] = "North").
48

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

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

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

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

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

Explain row context and filter context.

Power BIMedium
Save to ▾
Show answer
Row context evaluates one row at a time (calculated columns); filter context is the set of filters from slicers, visuals and relationships that a measure sees.
54

What is Power Query and why is it useful?

Power BIMedium
Save to ▾
Show answer
An ETL tool inside Excel for importing, cleaning and shaping data with recorded steps that refresh automatically when the source changes.
55

DAX: calculated column vs measure?

Power BIMedium
Save to ▾
Show answer
A calculated column is computed row by row and stored in the model. A measure is computed at query time using the filter context, e.g. Total Sales = SUM(Sales[Amount]). Prefer measures for aggregations.
56

What are bookmarks and drill-through in Power BI?

Power BIMedium
Save to ▾
Show answer
Bookmarks save a report view state for navigation; drill-through jumps to a detail page filtered to the item you picked.
57

What is a star schema?

Power BIMedium
Save to ▾
Show answer
A central fact table (sales, numeric events) linked to dimension tables (date, product, customer). It keeps Power BI models simple, fast and correct when filtering.
58

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

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

Write a DAX measure for year-over-year sales growth percentage.

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

Write a DAX measure for cumulative sales from the start of the data to the selected date.

Power BIMedium
Save to ▾
Show answer
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])
62

What is bidirectional cross-filtering in Power BI and when is it risky?

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

What are variables (VAR) in DAX and why are they useful?

Power BIMedium
Save to ▾
Show answer
VAR stores the result of an expression inside a measure, and RETURN gives the final output. Variables make measures readable, avoid evaluating the same expression twice, and are evaluated in the filter context where they are defined, which helps avoid subtle bugs inside CALCULATE. They also make debugging easy, because you can temporarily RETURN a variable to check its value before returning the final result.
64

What is a dataflow in Power BI?

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

What are field parameters in Power BI?

Power BIMedium
Save to ▾
Show answer
Field parameters let report users switch which measure or dimension a visual uses through a slicer. For example, one chart can switch between Sales, Profit and Quantity, or between grouping by Region, Category and Channel. This replaces many duplicate visuals or bookmark tricks, keeping the report lighter and easier to maintain. They are created from Modeling, New parameter, Fields, which generates a small table behind the slicer.
66

A stakeholder says the Power BI report shows different revenue than their Excel file. How do you troubleshoot?

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

What is the difference between a calculated table in DAX and a table created in Power Query?

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

A measure shows correct values per row in a table visual, but the grand total looks wrong. Why, and how do you fix it?

Power BIMedium
Save to ▾
Show answer
Totals in DAX are not the sum of visible rows; the measure is recalculated in the total's filter context. A measure like a distinct count, a ratio or an IF condition can give a different result at total level. If the business wants the sum of row values, iterate explicitly, for example SUMX ( VALUES ( Product[Name] ), [My Measure] ). Confirm with stakeholders which total is correct before changing it.
69

Users say a Power BI report still shows yesterday morning's data, although a refresh is scheduled every few hours. What do you check?

Power BIMedium
Save to ▾
Show answer
Open the semantic model's refresh history in the Service to see whether refreshes failed, for example due to expired credentials, an offline gateway or a timeout. Check whether the report is connected to a different copy of the model, whether the source itself was updated, and whether incremental refresh only covers some partitions. Also confirm the schedule time zone. Finally, add a 'data as of' timestamp on the report.
70

What does CALCULATE do in DAX?

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

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

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

What is a composite model in Power BI?

Save to ▾
Show answer
A composite model mixes storage modes in one dataset: some tables in Import, some in DirectQuery, and some in Dual. For example, a huge transaction table stays in DirectQuery for freshness while small dimension tables are imported for speed. Aggregation tables can be imported so most visuals hit memory, with DirectQuery used only for detailed drill-down. The trade-off is more complexity and relationship rules to manage.
73

Your Power BI dataset with five years of transactions takes two hours to refresh. How would you fix it?

Save to ▾
Show answer
Incremental refresh partitions a large table by date so that each refresh reloads only recent data, for example the last 7 days, while older partitions stay untouched. You create RangeStart and RangeEnd parameters, filter the table with them in Power Query, and set a policy such as store 5 years, refresh 7 days. Refresh becomes faster and lighter on the source. Query folding must work for the filter to be pushed to the database.
74

Two tables have a many-to-many relationship and your report totals look inflated. What do you do?

Save to ▾
Show answer
Inflated totals usually mean rows are being double counted through the many-to-many path. I would first check whether a proper dimension table can sit between them, turning it into two one-to-many relationships, for example a bridge table of unique customer IDs. I would set single filter direction, test totals against the source with a simple SQL query, and use measures with DISTINCTCOUNT where needed. Many-to-many should be a deliberate choice, not a default.
75

Your Sales table has OrderDate and ShipDate. How do you analyse sales by ship date when only OrderDate is the active relationship?

Save to ▾
Show answer
-- create an inactive relationship Sales[ShipDate] -> 'Date'[Date] in the model Sales by Ship Date = CALCULATE( [Total Sales], USERELATIONSHIP(Sales[ShipDate], 'Date'[Date]) ) -- the measure uses the inactive relationship only inside this CALCULATE -- the alternative is a second role-playing date table
76

Your manager wants a visual that always shows the top 5 products for whatever region is selected in a slicer. How do you build it?

Save to ▾
Show answer
The simplest way is a visual-level Top N filter on Product, by the Total Sales measure, set to 5; it recalculates for every slicer selection. If the number must be user selectable, create a numeric what-if parameter and a ranking measure using RANKX over ALLSELECTED(Product), then filter the visual where rank is less than or equal to the parameter. I would also add an Others row if the business wants totals to still match.
77

Write a DAX measure that counts new customers, meaning customers who bought in the selected period but never before it.

Save to ▾
Show answer
New Customers = VAR PeriodStart = MIN ( 'Date'[Date] ) VAR CurrentCustomers = VALUES ( Sales[CustomerID] ) VAR PastCustomers = CALCULATETABLE ( VALUES ( Sales[CustomerID] ), 'Date'[Date] < PeriodStart, REMOVEFILTERS ( 'Date' ) ) RETURN COUNTROWS ( EXCEPT ( CurrentCustomers, PastCustomers ) )
78

Tell me about a time you handled a request for access to sensitive data.

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

How do you read a CSV and inspect it in Pandas?

Save to ▾
Show answer
df = pd.read_csv('file.csv'); then head(), info(), describe() and isna().sum() for a quick health check.
80

How do you deal with missing or duplicate data?

Save to ▾
Show answer
Profile the data, remove exact duplicates using keys, impute missing values (mean/median/mode or a business rule), flag imputed rows and document every cleaning step.
81

Show a Pandas groupby example.

Save to ▾
Show answer
df.groupby('region')['sales'].agg(['sum', 'mean', 'count']) # splits by region, aggregates, combines the results
82

Which Python libraries do analysts use most?

Save to ▾
Show answer
Pandas for data handling, NumPy for numerics, Matplotlib and Seaborn for charts, and openpyxl or SQLAlchemy for files and databases.
83

What is the difference between loc and iloc in pandas?

Save to ▾
Show answer
loc selects by labels: row index labels and column names, for example df.loc[df['city'] == 'Pune', ['name', 'sales']]. iloc selects by integer position, for example df.iloc[0:5, 0:3] for the first five rows and three columns. With loc, slices include the end label; with iloc, the end position is excluded like normal Python. Use loc for readable filtering and iloc when position matters.
84

What is the difference between a list, tuple, dictionary and set in Python?

Save to ▾
Show answer
A list is ordered and mutable, good for sequences you change, like [10, 20, 30]. A tuple is ordered but immutable, useful for fixed records such as coordinates. A dictionary stores key-value pairs with fast lookup by key, like {'Delhi': 120}. A set holds unique unordered values and is great for removing duplicates or checking membership quickly. Analysts use dictionaries constantly for mappings and renaming columns.
85

What is the difference between concat, merge and join in pandas?

Save to ▾
Show answer
concat stacks DataFrames vertically (more rows) or side by side along an axis, for example appending monthly files with the same columns. merge combines DataFrames on key columns, like a SQL join, with how='inner', 'left', 'right' or 'outer'. join is a shortcut for merging on the index. Use concat to append and merge to look up related data. Always check row counts after a merge.
86

What is the difference between pd.cut and pd.qcut?

Save to ▾
Show answer
pd.cut bins values into intervals you define or into equal-width ranges, for example age groups 18-25, 26-35 and 36-50. pd.qcut bins by quantiles so each bin has roughly the same number of rows, for example dividing customers into spend quartiles. Use cut when business-defined boundaries matter and qcut when you want balanced groups such as deciles. Both return a categorical column you can group by.
87

What is a lambda function in Python and where do analysts use it?

Save to ▾
Show answer
A lambda is a small anonymous function written in one line, for example lambda x: x * 1.18 to add GST. Analysts use lambdas with sorted for custom sort keys, with pandas assign for computed columns, for example assign(margin=lambda d: d.profit / d.sales), and occasionally with apply. For anything longer than one expression or reused in several places, a normal def function is clearer and easier to test.
88

Write pandas code to extract month name, weekday and hour from a timestamp column.

Save to ▾
Show answer
df['ts'] = pd.to_datetime(df['ts'], errors='coerce') df['month'] = df['ts'].dt.month_name() df['weekday'] = df['ts'].dt.day_name() df['hour'] = df['ts'].dt.hour df['is_weekend'] = df['ts'].dt.dayofweek >= 5 # orders by weekday print(df['weekday'].value_counts())
89

Tell me about a time you had to learn a new tool or library quickly for a project.

Save to ▾
Show answer
Situation: in my internship the team needed survey results analysed in Python, which I had only used in coursework. Task: deliver cleaned data and charts within a week. Action: I spent two evenings on pandas and Seaborn tutorials using our real file, kept a notes document of commands, and asked a senior to review my first notebook. Result: I delivered on day five, and my notebook became the template for the next survey.
90

What does value_counts do in pandas, and how do you show percentages instead of counts?

Save to ▾
Show answer
value_counts returns how many times each unique value appears in a Series, sorted from most to least frequent. It is a quick way to check category distribution or spot typos. Pass normalize=True to get proportions, then multiply by 100 for percentages, for example df['city'].value_counts(normalize=True).mul(100).round(1). Use dropna=False to include missing values, which are excluded by default and are often important.
91

What is a virtual environment in Python and why should analysts use one?

Save to ▾
Show answer
A virtual environment is an isolated folder with its own Python interpreter and installed packages, created with python -m venv or tools like conda or uv. Each project can then pin its own pandas or library versions without breaking other projects. Saving the package list in requirements.txt or a lock file lets a colleague or a scheduled server reproduce the same setup, which avoids the classic works-on-my-laptop problem.
92

How do you merge two DataFrames on different column names?

Save to ▾
Show answer
pd.merge(df1, df2, left_on='cust_id', right_on='id', how='left') joins on differently named keys.
93

What is vectorisation and why does it matter?

Save to ▾
Show answer
Applying operations to whole arrays at once instead of Python loops, which is far faster because it runs in optimised compiled code.
94

How do you detect outliers?

Save to ▾
Show answer
Visually with a box plot, with the IQR rule (below Q1 − 1.5×IQR or above Q3 + 1.5×IQR) or a z-score above 3. Then decide with the business whether to cap, remove or keep them.
95

How do you handle outliers in Pandas?

Save to ▾
Show answer
Detect with IQR or z-score, then cap with clip, remove, transform with log, or keep and flag them depending on whether they are errors or real events.
96

How do you create a new column from conditions?

Save to ▾
Show answer
Use np.where for one condition, np.select for several, or df.apply with a function for custom logic.
97

How do you change data types and parse dates?

Save to ▾
Show answer
df['date'] = pd.to_datetime(df['date']); df['amount'] = pd.to_numeric(df['amount'], errors='coerce'); astype('category') for repeated strings.
98

How do you handle large CSV files that do not fit in memory in Python?

Save to ▾
Show answer
Read in chunks with chunksize, load only needed columns, set efficient dtypes or switch to Dask, Polars or a database.
99

How do you connect Python to a SQL database?

Save to ▾
Show answer
Create an engine with SQLAlchemy or a driver such as psycopg2, then run pd.read_sql(query, engine) to load results into a DataFrame.
100

Explain Matplotlib vs Seaborn.

Save to ▾
Show answer
Matplotlib is the low-level plotting library with full control; Seaborn builds on it with attractive statistical charts and simpler syntax.
101

What is the difference between melt and pivot?

Save to ▾
Show answer
melt converts wide data to long format; pivot converts long data to wide, and they are used to reshape data for analysis or charts.
102

What does apply do in pandas and why can it be slow?

Save to ▾
Show answer
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.
103

Write pandas code to calculate monthly revenue from an orders DataFrame.

Save to ▾
Show answer
import pandas as pd orders = pd.read_csv('orders.csv', parse_dates=['order_date']) orders['revenue'] = orders['qty'] * orders['unit_price'] monthly = ( orders .groupby(orders['order_date'].dt.to_period('M'))['revenue'] .sum() .reset_index(name='revenue') ) print(monthly)
104

Write pandas code for a pivot table of sales by region and category with sum and count.

Save to ▾
Show answer
import pandas as pd summary = pd.pivot_table( df, index='region', columns='category', values='sales', aggfunc=['sum', 'count'], fill_value=0, margins=True # adds an All row and column ) print(summary)
105

What is method chaining in pandas and why is it useful?

Save to ▾
Show answer
Method chaining writes a sequence of transformations as one expression, each step returning a new DataFrame, for example df.query('sales > 0').assign(margin=lambda x: x.profit / x.sales).groupby('region').agg(total=('sales', 'sum')). It avoids many temporary variables, reads top to bottom like a recipe, and reduces SettingWithCopy issues. Wrap the chain in brackets with one step per line, and use pipe for custom functions.
106

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

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
107

Your pandas script shows SettingWithCopyWarning and a new column does not update. What is happening and how do you fix it?

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

What is broadcasting in NumPy?

Save to ▾
Show answer
Broadcasting lets NumPy perform operations on arrays of different shapes without copying data, by virtually stretching the smaller array. For example, subtracting a 1D array of column means from a 2D array centres every column in one line: X - X.mean(axis=0). Shapes are compatible when trailing dimensions are equal or one of them is 1. It is a key reason vectorised code is much faster than Python loops.
109

Write a Python function that reads all Excel files in a folder and combines them into one DataFrame.

Save to ▾
Show answer
from pathlib import Path import pandas as pd def combine_excel(folder): frames = [] for f in Path(folder).glob('*.xlsx'): df = pd.read_excel(f) df['source_file'] = f.name frames.append(df) return pd.concat(frames, ignore_index=True) all_sales = combine_excel('monthly_reports')
110

Write pandas code to find the top 2 products by revenue in each region.

Save to ▾
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)
111

Your scheduled Python report failed silently and nobody noticed for a week. How do you make it reliable?

Save to ▾
Show answer
Add logging to a file or central log, wrap the main steps in try and except that send an alert by email or chat on failure, and exit with a non-zero code so the scheduler marks the job failed. Add data checks, such as row counts above a minimum and no missing dates, before sending output. Use a proper scheduler or orchestrator with retries, and include the data date in every report.
112

Write pandas code to convert daily sales into weekly totals, showing zero for days with no sales.

Save to ▾
Show answer
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
113

How would you automate a weekly report with Python?

Save to ▾
Show answer
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.
114

Your pandas script takes 40 minutes on 10 million rows. How do you speed it up?

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

Write Python code to fit a simple linear regression of sales on ad spend.

Save to ▾
Show answer
import pandas as pd import statsmodels.formula.api as smf df = pd.read_csv('marketing.csv') model = smf.ols('sales ~ ad_spend', data=df).fit() print(model.summary()) # coefficients, p-values, R-squared # predicted sales for a spend of 50,000 print(model.predict(pd.DataFrame({'ad_spend': [50000]})))
116

When would you choose Polars or DuckDB over pandas?

Save to ▾
Show answer
Choose them when data is large, roughly several gigabytes, or when pandas is slow or runs out of memory. DuckDB runs fast SQL directly on CSV and Parquet files on a laptop, ideal for analysts comfortable with SQL. Polars is a DataFrame library with lazy evaluation and multi-threading, often many times faster than pandas. pandas still wins for small data, its huge ecosystem and compatibility with most tutorials and libraries.
117

What is the difference between WHERE and HAVING?

SQLEasy
Save to ▾
Show answer
WHERE filters rows before grouping. HAVING filters groups after GROUP BY and can use aggregates like SUM() or COUNT().
118

INNER JOIN vs LEFT JOIN — with an example.

SQLEasy
Save to ▾
Show answer
INNER JOIN returns only matching rows. LEFT JOIN returns every row from the left table and NULLs where the right table has no match — e.g. all customers, including those with no orders.
119

Find the second highest salary in SQL.

SQLEasy
Save to ▾
Show answer
SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp); -- or: DENSE_RANK() OVER (ORDER BY salary DESC) = 2
120

What is a primary key vs a foreign key?

SQLEasy
Save to ▾
Show answer
A primary key uniquely identifies each row in its table; a foreign key references another table's primary key to enforce relationships.
121

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

Explain the types of SQL joins.

SQLEasy
Save to ▾
Show answer
INNER returns matching rows, LEFT keeps all left rows, RIGHT keeps all right rows, FULL keeps all rows from both, and CROSS returns every combination.
123

What do COALESCE and NULLIF do in SQL?

SQLEasy
Save to ▾
Show answer
COALESCE returns the first non-null value from its arguments, for example COALESCE(phone, alt_phone, 'Not given') fills missing phone numbers. NULLIF(a, b) returns NULL when a equals b, otherwise a. Its classic use is avoiding division by zero: revenue / NULLIF(orders, 0) returns NULL instead of an error when orders is zero. Together they handle most null-related reporting problems cleanly.
124

What is the difference between a fact table and a dimension table?

SQLEasy
Save to ▾
Show answer
A fact table stores measurable events at a defined grain, such as one row per order line, with numeric measures like quantity and amount plus foreign keys. Dimension tables describe the context of those events: customer, product, store and date, with descriptive attributes used for filtering and grouping. Facts are long and grow quickly; dimensions are wider and smaller. Together they form star or snowflake schemas.
125

What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?

SQLEasy
Save to ▾
Show answer
COUNT(*) counts all rows, including rows with NULLs. COUNT(column) counts only rows where that column is not NULL. COUNT(DISTINCT column) counts unique non-null values. For example, in an orders table, COUNT(*) gives number of orders, COUNT(coupon_code) gives orders that used a coupon, and COUNT(DISTINCT customer_id) gives number of unique buyers. Mixing these up is a common source of wrong KPIs.
126

Write SQL to count orders by week and by day of week.

SQLEasy
Save to ▾
Show answer
-- orders per week (PostgreSQL style) SELECT DATE_TRUNC('week', order_date) AS week_start, COUNT(*) AS orders FROM orders GROUP BY 1 ORDER BY 1; -- orders by day of week (0 = Sunday) SELECT EXTRACT(DOW FROM order_date) AS dow, COUNT(*) AS orders FROM orders GROUP BY 1 ORDER BY 1;
127

What is the difference between a primary key and a unique constraint?

SQLEasy
Save to ▾
Show answer
Both prevent duplicate values. A table can have only one primary key, it cannot contain NULLs, and it is the main identifier other tables reference through foreign keys. A table can have several unique constraints, for example on email and on PAN number, and most databases allow NULLs in a unique column. Use the primary key for the row identity and unique constraints for other business rules.
128

A report filters WHERE order_date = '2026-03-31' and misses almost all orders from that day. Why?

SQLEasy
Save to ▾
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.
129

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).
130

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

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

What is a window function?

SQLMedium
Save to ▾
Show answer
A function that calculates over a set of rows related to the current row without collapsing them, such as ROW_NUMBER, RANK, LAG or SUM() OVER (PARTITION BY ...).
133

Explain GROUP BY with an example.

SQLMedium
Save to ▾
Show answer
It groups rows sharing values so aggregates run per group, for example SELECT region, SUM(sales) FROM orders GROUP BY region.
134

What is a self join and when is it used?

SQLMedium
Save to ▾
Show answer
Joining a table to itself with aliases, useful for hierarchies such as employees and managers or comparing rows within the same table.
135

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

Write SQL for a running total of sales by date.

SQLMedium
Save to ▾
Show answer
SELECT order_date, SUM(amount) OVER (ORDER BY order_date) AS running_total FROM sales;
137

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

What is the difference between a clustered and a non-clustered index?

SQLMedium
Save to ▾
Show answer
A clustered index defines the physical order of rows (one per table); a non-clustered index is a separate structure with pointers to the rows.
139

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

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

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

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

How do you calculate a rolling average?

SQLMedium
Save to ▾
Show answer
df['sales'].rolling(window=7).mean() gives a 7-period moving average, useful to smooth daily noise.
144

How are NULLs handled in SQL?

SQLMedium
Save to ▾
Show answer
NULL means unknown, so comparisons with = fail; use IS NULL, COALESCE for defaults and remember most aggregates ignore NULLs except COUNT(*).
145

Difference between a CTE, a subquery and a temp table?

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

How do you find duplicate rows in a table?

SQLMedium
Save to ▾
Show answer
SELECT col, COUNT(*) FROM t GROUP BY col HAVING COUNT(*) > 1; use ROW_NUMBER() partitioned by the key to delete extras.
147

How would you find customers who never placed an order?

SQLMedium
Save to ▾
Show answer
LEFT JOIN customers to orders and keep rows where order_id IS NULL, or use NOT EXISTS, which also handles NULLs safely.
148

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

Write SQL using LAG to compare each month's revenue with the previous month.

SQLMedium
Save to ▾
Show answer
WITH monthly AS ( SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue FROM orders GROUP BY 1 ) SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_revenue, ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS mom_pct FROM monthly ORDER BY month;
150

Write SQL to show each category's percentage share of total revenue.

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

Your NOT IN query unexpectedly returns zero rows. Why might that happen, and how do EXISTS and IN differ?

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

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).
153

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

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
155

Tell me about a time a query you wrote produced wrong results.

SQLMedium
Save to ▾
Show answer
Situation: my weekly active users number jumped 30 percent overnight. Task: find out whether it was real before the report went out. Action: I traced the query and found a new join to a device table created duplicate users. I changed the count to COUNT(DISTINCT user_id), added a row-count check, and informed my manager before publishing. Result: the corrected number went out on time, and I added that check to every report.
156

What is a transaction and what do the ACID properties mean?

SQLMedium
Save to ▾
Show answer
A transaction is a group of SQL statements that succeed or fail together, such as debiting one account and crediting another. ACID: Atomicity means all or nothing; Consistency means rules and constraints stay valid; Isolation means concurrent transactions do not see each other's partial work; Durability means committed data survives crashes. Analysts mostly read data, but knowing ACID explains why reports should read committed data.
157

What is the difference between a data warehouse, a data lake and a lakehouse?

SQLMedium
Save to ▾
Show answer
A data warehouse stores cleaned, structured data in modelled tables optimised for SQL reporting. A data lake stores raw data of any type, such as logs, JSON and images, cheaply in files, with structure applied when read. A lakehouse combines them: open table formats on lake storage add transactions, schemas and fast SQL on top of files. Analysts typically query the warehouse or the curated layer of a lakehouse.
158

Write SQL to find the median order value overall and per city.

SQLMedium
Save to ▾
Show answer
-- 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;
159

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

Write SQL to find the number of days between each customer's consecutive orders and their average gap.

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

How would you calculate retention using SQL?

SQLHard
Save to ▾
Show answer
Find each user's first activity date as a cohort, join to later activity, and compute the share of each cohort active in day, week or month N.
162

How do you optimise a slow SQL query?

SQLHard
Save to ▾
Show answer
Read the execution plan, add or fix indexes, select only needed columns, filter early, avoid functions on indexed columns, and replace correlated subqueries with joins.
163

Find customers who ordered in January but not in February.

SQLHard
Save to ▾
Show answer
SELECT DISTINCT customer_id FROM orders WHERE month = 'Jan' EXCEPT SELECT customer_id FROM orders WHERE month = 'Feb';
164

Write SQL to find the top 3 products by revenue within each category.

SQLHard
Save to ▾
Show answer
WITH ranked AS ( SELECT category, product, SUM(amount) AS revenue, DENSE_RANK() OVER ( PARTITION BY category ORDER BY SUM(amount) DESC ) AS rnk FROM sales GROUP BY category, product ) SELECT category, product, revenue FROM ranked WHERE rnk <= 3 ORDER BY category, rnk;
165

What is a query execution plan and how do you read it?

SQLHard
Save to ▾
Show answer
An execution plan shows how the database will run a query: which indexes it uses, join methods such as nested loop or hash join, scan types and estimated rows. You get it with EXPLAIN, or EXPLAIN ANALYZE for actual timings. Look for full table scans on big tables, large gaps between estimated and actual rows, and expensive sorts. These point to missing indexes, stale statistics or non-sargable filters like functions on columns.
166

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

Write SQL to find users who logged in on at least 3 consecutive days.

SQLHard
Save to ▾
Show answer
WITH d AS ( SELECT DISTINCT user_id, CAST(login_time AS DATE) AS dt FROM logins ), g AS ( SELECT user_id, dt, dt - CAST(ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY dt) AS INT) AS grp FROM d ) SELECT DISTINCT user_id FROM g GROUP BY user_id, grp HAVING COUNT(*) >= 3; -- gaps and islands: consecutive dates share the same grp value
168

What is descriptive vs inferential statistics?

Save to ▾
Show answer
Descriptive statistics summarise the data you have; inferential statistics use a sample to draw conclusions about a larger population.
169

What is the difference between population and sample?

Save to ▾
Show answer
The population is the whole group of interest; a sample is a subset used to estimate population values.
170

Mean vs median vs mode — when do you use which?

Save to ▾
Show answer
Mean for symmetric data without outliers, median for skewed data (salaries, house prices), mode for categories (the most common city).
171

What are variance and standard deviation?

Save to ▾
Show answer
Variance is the average squared distance of values from their mean; standard deviation is its square root, in the same units as the data. They measure spread. Two stores may both average 50,000 rupees daily, but one with a standard deviation of 2,000 is stable while one with 20,000 is volatile. For samples, divide by n minus 1 instead of n to get an unbiased estimate.
172

What is a z-score and how is it used?

Save to ▾
Show answer
A z-score tells how many standard deviations a value is from the mean: z = (x minus mean) divided by standard deviation. A delivery time with z = 2.5 is unusually slow. Analysts use z-scores to flag outliers, often beyond plus or minus 3, to compare values on different scales, such as marks in different exams, and to look up probabilities when data is roughly normal.
173

Why does correlation not imply causation? Give a business example.

Save to ▾
Show answer
Two variables can move together because of a third factor, reverse causation or pure chance. Example: stores with more staff have higher sales, but busy locations get more staff because they already sell more, so hiring alone may not raise sales. Ice-cream sales and AC sales both rise in summer because of temperature. To claim causation you need experiments, natural experiments or careful control of confounders.
174

What is a percentile and what is the interquartile range?

Save to ▾
Show answer
A percentile is the value below which a given percentage of data falls; the 90th percentile delivery time means 90 percent of deliveries were faster. The interquartile range is the 75th percentile minus the 25th percentile, the spread of the middle half. IQR is robust to outliers, and values beyond 1.5 times the IQR from the quartiles are often flagged as outliers in box plots.
175

What is the normal distribution and the 68-95-99.7 rule?

Save to ▾
Show answer
The normal distribution is a symmetric, bell-shaped distribution described by its mean and standard deviation. The empirical rule says about 68% of values fall within one standard deviation of the mean, 95% within two and 99.7% within three. For example, if delivery times average 30 minutes with an SD of 5, about 95% arrive in 20 to 40 minutes. Many business metrics are skewed, so check before assuming normality.
176

What is regression to the mean? Give a business example.

Save to ▾
Show answer
Regression to the mean means extreme results tend to be followed by more average results, simply because part of the extreme was luck. For example, if you pick the 10 worst-performing stores this quarter and give them a training programme, many will improve next quarter even without training. To claim the programme worked, compare them with similar low performers who did not get it.
177

How do you decide the sample size for a survey?

Save to ▾
Show answer
It depends on margin of error, confidence level and expected variability; larger samples give smaller errors with diminishing returns.
178

What is the difference between mean and median for reporting salaries?

Save to ▾
Show answer
Salaries are right-skewed, so the median better represents the typical employee while the mean is pulled up by a few high earners.
179

Sales dropped 20% last month. How will you analyse it?

Save to ▾
Show answer
Confirm data accuracy first → break down by region, product, channel and customer segment → compare with seasonality and last year → check pricing, stock-outs, marketing changes and funnel conversion → present the root causes with clear charts.
180

How do you detect and handle skewed data?

Save to ▾
Show answer
Check histograms and skew(), then apply log, square-root or Box-Cox transforms, or use medians and robust statistics.
181

Explain correlation vs regression.

Save to ▾
Show answer
Correlation shows the strength and direction of association; regression builds an equation to predict one variable from others.
182

How would you investigate a sudden 20% drop in daily sales?

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

What is statistical significance vs practical significance?

Save to ▾
Show answer
A result can be statistically significant yet too small to matter; always report effect size and business impact together with the p-value.
184

What is cohort analysis?

Save to ▾
Show answer
Grouping users by a shared start date or trait and tracking behaviour over time, for example monthly retention per signup month.
185

What is hypothesis testing in simple words?

Save to ▾
Show answer
You assume no effect (null), collect evidence and decide whether the data is surprising enough to reject that assumption at a chosen significance level.
186

What is a p-value, explained simply?

Save to ▾
Show answer
A p-value is the probability of seeing a result at least as extreme as yours if the null hypothesis, usually no effect, were true. A p-value of 0.03 means that if the new page truly made no difference, results this strong would appear only about 3 percent of the time by chance. Below a chosen threshold such as 0.05 we reject the null. It is not the probability that the hypothesis is true.
187

What are Type I and Type II errors?

Save to ▾
Show answer
A Type I error is a false positive: concluding there is an effect when there is none, for example launching a feature that does not actually lift sales. Its probability is alpha, often 0.05. A Type II error is a false negative: missing a real effect, with probability beta. Power is 1 minus beta. Larger samples reduce Type II errors, and the business cost of each mistake should guide which one you guard against more.
188

What is a confidence interval?

Save to ▾
Show answer
A confidence interval gives a range of plausible values for a population parameter based on a sample. A 95 percent interval for average order value of 820 to 880 rupees means that if we repeated the sampling many times, about 95 percent of such intervals would contain the true average. Wider intervals mean more uncertainty; larger samples narrow them. Reporting the interval is more honest than reporting one number.
189

What is the Central Limit Theorem and why does it matter for analysts?

Save to ▾
Show answer
The Central Limit Theorem says that the distribution of sample means approaches a normal distribution as sample size grows, usually from about 30 observations, even if the underlying data is skewed. This is why we can use z-tests, t-tests and confidence intervals for averages such as order value or session time. It does not make the raw data normal, and very heavy-tailed data may need larger samples.
190

Checkout conversion fell from 3.2% to 2.6% the week after an app release. How do you investigate?

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

Tell me about a time you used data to challenge an assumption.

Save to ▾
Show answer
Situation: our team believed weekend discounts drove most sales. Task: I was preparing the monthly review. Action: I compared discount and non-discount weekends over six months and controlled for paydays and festivals. Discounts moved sales forward from the following week but added little overall. I presented it with one clear chart. Result: management trialled fewer discount weekends, and margin improved without a fall in monthly revenue.
192

When do you use a chi-square test?

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

What is the difference between a t-test and a z-test?

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

Estimate how many cups of tea are sold in a city of 1 crore people in a day.

Save to ▾
Show answer
State assumptions clearly. Of 1 crore people, assume 70 percent drink tea, so 70 lakh. Say half of their cups are bought outside, at an average of one bought cup per drinker every day for that half, giving about 35 lakh purchased cups, with more at offices and stations. So roughly 30 to 40 lakh cups. Interviewers judge the structure, segmentation and sanity check more than the exact number.
195

What is sampling bias? Name common types.

Save to ▾
Show answer
Sampling bias occurs when the sample does not represent the population, so conclusions do not generalise. Common types: selection bias, such as surveying only app users about website issues; survivorship bias, studying only customers who stayed; non-response bias, when unhappy customers skip surveys; and convenience sampling, using whoever is easiest to reach. Random sampling, stratification and weighting help reduce it.
196

Average delivery time improved by 4 minutes, but customer complaints about late deliveries went up. How do you investigate?

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

How would you segment customers?

Save to ▾
Show answer
Use RFM scoring or clustering on behaviour and value, validate segments with business teams and attach an action to each segment.
198

A marketing campaign shows more sales. How do you prove it worked?

Save to ▾
Show answer
Compare with a control group or use difference-in-differences, control for seasonality, and measure incremental sales against campaign cost.
199

Explain funnel analysis.

Save to ▾
Show answer
Track users through steps (visit → product view → cart → payment), compute conversion between each step, find the biggest drop, segment by device and source, and suggest fixes to test.
200

How would you design an A/B test for a new checkout button?

Save to ▾
Show answer
Define one primary metric, such as checkout conversion, plus guardrails like refund rate. State the hypothesis and minimum detectable effect, then calculate sample size and duration for chosen power and significance. Randomise users, not sessions, into control and variant, check the split is balanced, and run for full weeks to cover weekday patterns. Do not peek and stop early. Finally analyse with a proportion test and check key segments.
201

What is Simpson's paradox? Give an example.

Save to ▾
Show answer
Simpson's paradox happens when a trend in several groups reverses when the groups are combined, because group sizes differ. Example: a new sales process has higher conversion in both small and large stores, yet overall conversion looks lower because it was mostly rolled out to large stores, which convert worse in general. The lesson is to check segments and confounding variables before concluding from aggregate numbers.
202

A retail chain asks you which of its 120 stores it should close. How do you approach it?

Save to ▾
Show answer
Clarify the goal first: profit, cash or market share. Build a store-level dataset with revenue, contribution margin after rent and staff, trend over 24 months, footfall, catchment demographics and cannibalisation with nearby stores. Separate structural losers from stores that are new or temporarily disrupted. Estimate how much revenue would shift to nearby stores or online if a store closes. Present a shortlist with scenarios and risks, not a single answer.
203

How do you calculate a simple customer lifetime value?

Save to ▾
Show answer
A simple formula is CLV = average order value x purchase frequency per year x gross margin x expected lifetime in years. Example: 800 rupees x 6 orders x 30 percent margin x 3 years = 4,320 rupees. Expected lifetime can be estimated as 1 divided by annual churn rate. A better version discounts future value and calculates CLV by cohort or segment, because averages hide very different customer groups.
204

An A/B test shows a 2% lift with p = 0.04, but it only ran for 3 days. Would you ship it?

Save to ▾
Show answer
Not yet. Three days misses weekly patterns, and early results are often inflated by novelty effects or by stopping once the p-value looked good. I would check whether the planned sample size was reached, whether the split was balanced, and how guardrail metrics behaved. I would recommend running to the pre-planned duration, at least one or two full weeks, and then deciding, considering the cost of being wrong.
205

What is Tableau and what are its main products?

Save to ▾
Show answer
Tableau is a visual analytics tool for building interactive charts and dashboards by dragging fields onto shelves. Tableau Desktop is used to author workbooks, Tableau Prep cleans and shapes data, Tableau Server and Tableau Cloud host and share dashboards with security and scheduled refresh, and Tableau Public is a free version where work is saved publicly. Analysts usually build in Desktop and publish to Server or Cloud.
206

What is the difference between dimensions and measures in Tableau?

Save to ▾
Show answer
Dimensions are qualitative fields such as region, product or date that slice data into groups and create headers. Measures are quantitative fields such as sales or profit that get aggregated, by default with SUM. Dragging a dimension to rows creates one row per member; dragging a measure creates an axis. A numeric field like customer ID should be converted to a dimension so it is not summed.
207

What is the difference between discrete and continuous fields in Tableau?

Save to ▾
Show answer
Discrete fields, shown as blue pills, have distinct values and create headers, for example month names as separate columns. Continuous fields, shown as green pills, form an unbroken range and create an axis, for example a continuous date line chart. The same date can be either: discrete months give a bar per month label, continuous months give a time-series axis. Colour also changes: discrete gives separate colours, continuous a gradient.
208

What is the difference between a live connection and an extract in Tableau?

Save to ▾
Show answer
A live connection queries the source database every time a user interacts, so data is always current but performance depends on the database. An extract is a compressed snapshot stored in Tableau's hyper format, usually much faster, refreshed on a schedule and usable offline. Use live for near real-time needs on a fast warehouse, and extracts for speed, slow sources or when you want to reduce load on production systems.
209

How do you create a dual-axis chart in Tableau, for example sales bars with a profit margin line?

Save to ▾
Show answer
Drag Order Date to Columns as a continuous or discrete month, put Sales on Rows, then drag Profit Ratio to Rows next to it. Right-click the second pill and choose Dual Axis. On the Marks card set Sales to Bar and Profit Ratio to Line. Decide whether to synchronise axes: do not synchronise when units differ, like rupees and percentage. Label the axes clearly so readers know which is which.
210

Write a Tableau calculated field for profit ratio and a profit category.

Save to ▾
Show answer
// Profit Ratio (aggregate first, then divide) SUM([Profit]) / SUM([Sales]) // Profit Category IF SUM([Profit]) < 0 THEN 'Loss' ELSEIF [Profit Ratio] < 0.1 THEN 'Low margin' ELSE 'Healthy' END // avoid AVG([Profit]/[Sales]): it averages row ratios and gives a wrong total
211

What is Tableau Prep used for?

Save to ▾
Show answer
Tableau Prep is a visual tool for cleaning and shaping data before analysis. You build a flow of steps: connect inputs, clean fields, split columns, group similar values, pivot, join, union and aggregate, and see the data change at every step. The output can be an extract or published data source that dashboards use. It suits analysts who need repeatable preparation without writing SQL or Python.
212

What is a story in Tableau?

Save to ▾
Show answer
A story is a sequence of sheets or dashboards, called story points, that walk an audience through an analysis step by step, like slides. Each point can keep its own filters and selections and has a caption explaining the insight. It is useful for presenting findings such as why churn rose, moving from the headline number to segments and recommendations. For everyday monitoring, a regular dashboard is better than a story.
213

How do you handle null values in Tableau calculations?

Save to ▾
Show answer
// treat missing sales as zero ZN(SUM([Sales])) // replace a missing value with a default IFNULL([Region], 'Unknown') // test explicitly IF ISNULL([Discount]) THEN 0 ELSE [Discount] END // right-click a null indicator on an axis to filter or show nulls at default position
214

Your Tableau map shows a large number of unknown locations. How do you fix it?

Save to ▾
Show answer
Click the unknown indicator at the bottom right of the map and choose Edit Locations. Usually Tableau cannot match names because of spelling variants, such as 'Bengaluru' versus 'Bangalore', or an ambiguous city that needs the state and country set. Make sure fields have the correct geographic role, add a country or state field to the view, clean names in the data source, or use latitude and longitude columns.
215

What is the difference between relationships, joins and data blending in Tableau?

TableauMedium
Save to ▾
Show answer
Relationships, the default logical layer, connect tables flexibly and Tableau decides join type and level per view, avoiding duplicated measures. Joins physically merge tables into one at a fixed level, which can duplicate rows if grains differ. Blending combines data from separate data sources at the view level using a primary and secondary source, aggregating the secondary first. Prefer relationships, use joins for specific row-level needs, and blending as a last resort.
216

Write a Tableau calculation to find each customer's first purchase date and flag new versus repeat orders.

TableauMedium
Save to ▾
Show answer
// First Purchase Date { FIXED [Customer ID] : MIN([Order Date]) } // Order Type IF [Order Date] = [First Purchase Date] THEN 'New' ELSE 'Repeat' END // Cohort Month for retention analysis DATETRUNC('month', [First Purchase Date])
217

What are table calculations in Tableau?

TableauMedium
Save to ▾
Show answer
Table calculations are computed on the aggregated results already in the view, not on the underlying rows. Examples are running total, percent of total, difference from previous, rank and moving average. Their result depends on the direction and partition you set with Compute Using, for example table across or by specific dimensions. They are quick to add from Quick Table Calculation, but incorrect Compute Using settings are a frequent cause of wrong numbers.
218

What are parameters in Tableau and how do you use them?

TableauMedium
Save to ▾
Show answer
A parameter is a single user-controlled value, such as a number, date or string, that is independent of the data. On its own it does nothing; you reference it in calculations, filters, reference lines or bins. Common uses are a Top N selector, a what-if growth rate, or a measure switcher where a CASE statement picks Sales, Profit or Quantity based on the parameter. Parameter actions let users set it by clicking a mark.
219

What is the difference between sets and groups in Tableau?

TableauMedium
Save to ▾
Show answer
A group combines dimension members into higher-level categories, such as merging small states into Others; it is static and each member belongs to one group. A set splits members into In and Out based on a selection or condition, such as top 10 customers by sales, and can be dynamic and recalculate with data. Sets can be combined, used in calculations and changed by set actions, making them more powerful for interactive analysis.
220

Your Top 10 products filter in Tableau ignores the category filter. Why, and how do you fix it?

TableauMedium
Save to ▾
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.
221

What types of dashboard actions does Tableau offer?

TableauMedium
Save to ▾
Show answer
Filter actions filter other sheets when a user selects or hovers on a mark, such as clicking a region to filter a product table. Highlight actions emphasise related marks without removing others. URL actions open a web page with field values in the link. Go to Sheet navigates to another dashboard. Parameter and set actions update a parameter or set based on selection, enabling advanced interactions like dynamic drill-downs and comparisons.
222

Write a Tableau table calculation for year-over-year sales growth.

TableauMedium
Save to ▾
Show answer
// with YEAR(Order Date) in the view, Compute Using: Table (across) (ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Sales])), -1)) // the built-in Quick Table Calculation 'Year over Year Growth' generates similar logic // format the field as a percentage
223

How do you let users choose Top N customers with a parameter in Tableau?

TableauMedium
Save to ▾
Show answer
Create an integer parameter Top N with a range of 5 to 50 and show its control. Drag Customer to the Filters shelf, choose the Top tab, select By field, and set it to top Top N by SUM(Sales). Alternatively create a set based on the parameter so you can show Top N versus Others in one chart. If the Top N must respect a category filter, add that category filter to context.
224

Tell me about a time you migrated or rebuilt reports in a new BI tool.

TableauMedium
Save to ▾
Show answer
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.
225

Tell me about a time you had to prioritise several dashboard requests at once.

TableauMedium
Save to ▾
Show answer
Situation: three managers asked for new dashboards in the same week. Task: I could not finish all of them on time. Action: I asked each about the decision the dashboard would support and its deadline, estimated effort, and shared a proposed order with my manager. I gave one requester a quick interim extract. Result: the most urgent dashboard, for a pricing review, shipped first, and nobody was left without any data.
226

A stakeholder wants a 40-column crosstab in Tableau that looks exactly like their Excel sheet. How do you handle it?

TableauMedium
Save to ▾
Show answer
I would ask what they do with it. If they export and work on it in Excel, a crosstab view with a download option or a scheduled extract is fine, and Tableau should not fight Excel. If they actually scan it for exceptions, I would suggest a summary view with highlights and a drill-down to the detail table. Often both: a short visual summary on top and the detailed table on a second tab.
227

What is the difference between extract filters and data source filters in Tableau?

TableauMedium
Save to ▾
Show answer
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.
228

Why would you publish a Tableau data source separately instead of embedding it in each workbook?

TableauMedium
Save to ▾
Show answer
A published data source lives on Tableau Server or Cloud and can be reused by many workbooks. Calculations, joins, field names and extract refresh schedules are defined once, so every report uses the same revenue logic and the database is queried once per refresh instead of once per workbook. You can certify it and set permissions. Embedded sources are fine for a quick personal analysis but drift over time.
229

Write a Tableau calculated field that keeps only sales from the last 30 days.

TableauMedium
Save to ▾
Show answer
// Calculated field: Last 30 Days Flag DATEDIFF('day', [Order Date], TODAY()) <= 29 AND [Order Date] <= TODAY() // Put this field on Filters and keep True. // For a sales measure instead of a filter: // IF DATEDIFF('day', [Order Date], TODAY()) <= 29 THEN [Sales] END
230

Tell me about a time a stakeholder rejected your first dashboard design.

TableauMedium
Save to ▾
Show answer
Situation: I built a Tableau operations dashboard, and the warehouse head said it was too complex to use on the floor. Task: make it useful for them. Action: I watched how supervisors worked, learned they needed three numbers on a tablet every hour, and rebuilt it with large KPI tiles and one drill-down. Result: daily usage went from a handful of users to every shift supervisor within two weeks.
231

Explain FIXED, INCLUDE and EXCLUDE LOD expressions in Tableau.

Save to ▾
Show answer
Level of Detail expressions compute aggregates at a level different from the view. FIXED computes at the dimensions you specify regardless of the view, for example { FIXED [Customer] : SUM([Sales]) } for lifetime sales per customer. INCLUDE adds a dimension to the view's level, such as average of per-customer sales by region. EXCLUDE removes a dimension, useful for comparing each month to the yearly total. FIXED ignores normal dimension filters unless they are context filters.
232

What is the order of operations in Tableau and why does it matter?

Save to ▾
Show answer
Tableau applies filters in a fixed order: extract filters, data source filters, context filters, then FIXED LOD calculations, dimension filters, INCLUDE and EXCLUDE LODs, measure filters, and finally table calculations and table calc filters. It matters because a calculation only sees data filtered before it. For example, a FIXED LOD ignores a normal region filter unless that filter is added to context. Many wrong totals come from not knowing this order.
233

A Tableau dashboard takes 30 seconds to load. How do you make it faster?

Save to ▾
Show answer
Start with the Performance Recorder to see whether time goes into queries, calculations or rendering. Then reduce work: use an extract with only needed columns and rows, aggregate it to the dashboard's grain, and hide unused fields. Cut the number of sheets and high-mark-count views, replace many quick filters with fewer or with action filters, and avoid show only relevant values. Simplify heavy string and LOD calculations or move them to the database.
234

How do you implement row-level security in Tableau?

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

Your Tableau numbers change unexpectedly when a filter is applied to a view that uses a FIXED LOD. Why?

Save to ▾
Show answer
Usually it is the order of operations. FIXED LODs are computed before dimension filters, so they ignore a normal region filter, while other measures in the view respect it. That mix makes percentages and totals look inconsistent. Decide what the business wants: if the LOD should respect the filter, add the filter to context; if it should not, keep it and label it clearly, for example share of national sales.
236

Tableau or Power BI: how would you decide which one a team should use?

Save to ▾
Show answer
Both are capable, so the decision usually depends on context. Power BI fits teams already on Microsoft 365 and Azure, has lower per-user cost, and strong modelling with DAX. Tableau is often preferred for flexible visual exploration, very polished custom visuals and mixed-platform environments. I would also weigh existing skills, data sources, governance needs, licensing and embedding requirements, then run a short pilot on a real use case.
237

What is data cleaning and why does it take so much of an analyst's time?

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

Numbers in an Excel column are stored as text and SUM returns zero. How do you fix it?

Save to ▾
Show answer
Signs are left-aligned numbers, green triangles, and COUNT returning less than COUNTA. Fix options: select the column and use the warning menu Convert to Number; use Data, Text to Columns and Finish; or a helper column with =VALUE(TRIM(A2)). If there are hidden characters, use =VALUE(SUBSTITUTE(TRIM(A2), CHAR(160), "")) to remove non-breaking spaces from web exports. In Power Query, simply change the column type to number.
239

Write pandas code to trim whitespace and lower-case every text column in a DataFrame.

Save to ▾
Show answer
text_cols = df.select_dtypes(include='object').columns for col in text_cols: df[col] = ( df[col] .astype('string') .str.strip() .str.lower() .replace('', pd.NA) ) # also tidy column names df.columns = df.columns.str.strip().str.lower().str.replace(' ', '_')
240

What is data profiling?

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

How do you unpivot a wide monthly sales sheet into a long table in Power Query?

Save to ▾
Show answer
Load the table into Power Query. Select the identifier columns such as Region and Product, then choose Transform, Unpivot Other Columns. The month columns become two columns, Attribute and Value; rename them Month and Sales. Change Month to a date type and Sales to a decimal number. Using Unpivot Other Columns rather than Unpivot Columns means new months added later are picked up automatically on refresh.
242

What is tidy data?

Save to ▾
Show answer
Tidy data follows three rules: each variable is a column, each observation is a row, and each type of observational unit is a table. A sheet with months as columns is not tidy; unpivoting it to Region, Month and Sales columns makes it tidy. Tidy data is easy to filter, group, join and chart in pandas, SQL, Power BI and Tableau, so reshaping into this form is a core cleaning step.
243

How do you split a full name column into first and last name in Excel?

Save to ▾
Show answer
-- A2 = 'Priya Ramesh Iyer' -- first name =TEXTBEFORE(TRIM(A2), " ") -- last name (text after the last space) =TEXTAFTER(TRIM(A2), " ", -1) -- older Excel: first name =LEFT(TRIM(A2), FIND(" ", TRIM(A2)&" ")-1) -- or use Data > Text to Columns or Flash Fill (Ctrl+E) for a one-off task
244

Pin codes in your CSV lost their leading zeros after opening in Excel. Why and how do you prevent it?

Save to ▾
Show answer
Excel guessed the column was a number and dropped leading zeros; the same happens with long IDs turning into scientific notation. Identifiers that are not used in arithmetic should be stored as text. Import through Data, From Text/CSV or Power Query and set the column type to Text, or read it in pandas with dtype={'pincode': str}. To repair, use =TEXT(A2, "000000") when the original length is known.
245

How do you handle a dataset with 30% missing values in a key column?

Save to ▾
Show answer
Understand why it is missing, consider imputation or a missing flag, test sensitivity, and talk to data owners about fixing collection if it is systematic.
246

How do you clean messy text data in Excel?

Save to ▾
Show answer
Use TRIM, CLEAN, UPPER or PROPER, Text to Columns, Flash Fill, SUBSTITUTE, and Power Query for repeatable cleaning steps.
247

What are the main dimensions of data quality?

Save to ▾
Show answer
Common dimensions are accuracy, values reflect reality; completeness, required fields are present; consistency, the same fact matches across systems; validity, values follow formats and rules such as a 6-digit pin code; uniqueness, no unwanted duplicates; and timeliness, data is fresh enough for its use. Measuring these with simple checks, like percent missing or duplicate rate, turns vague complaints about bad data into fixable issues.
248

Write pandas code to standardise inconsistent city names such as 'delhi ', 'New Delhi' and 'DELHI'.

Save to ▾
Show answer
city_map = { 'delhi': 'Delhi', 'new delhi': 'Delhi', 'bombay': 'Mumbai', 'mumbai': 'Mumbai', 'bengaluru': 'Bengaluru', 'bangalore': 'Bengaluru', } clean = df['city'].str.strip().str.lower().str.replace(r'\s+', ' ', regex=True) df['city_clean'] = clean.map(city_map).fillna(clean.str.title()) print(df['city_clean'].value_counts())
249

When would you drop missing values, and when would you impute with mean or median?

Save to ▾
Show answer
Drop rows when few are missing, roughly under 5 percent, and missingness looks random. Impute when dropping would lose too much data. Use the median for skewed numbers such as income or order value, the mean for roughly symmetric data, and the mode or an Unknown category for categoricals. Group-wise imputation, such as median by city, is better than one global value. Always add a flag column showing what was imputed.
250

A file has dates in mixed formats, some dd/mm/yyyy and some mm/dd/yyyy. How do you handle it?

Save to ▾
Show answer
Find out which system or region produced each format, since a source column often explains it. Values with a day above 12 reveal the format unambiguously. Parse with an explicit format per source, for example pd.to_datetime(col, format='%d/%m/%Y', errors='coerce'), rather than letting the tool guess. Check failed or suspicious parses, such as future dates, and ask the source team to send ISO format yyyy-mm-dd going forward.
251

Write SQL to flag rows that fail basic validation rules in a customer table.

Save to ▾
Show answer
SELECT customer_id, CASE WHEN email IS NULL OR email NOT LIKE '%_@_%._%' THEN 1 ELSE 0 END AS bad_email, CASE WHEN LENGTH(pincode) <> 6 THEN 1 ELSE 0 END AS bad_pincode, CASE WHEN signup_date > CURRENT_DATE THEN 1 ELSE 0 END AS future_signup, CASE WHEN age NOT BETWEEN 18 AND 100 THEN 1 ELSE 0 END AS bad_age FROM customers; -- sum each flag to report the error rate per rule
252

What is the difference between an outlier and a data error?

Save to ▾
Show answer
An outlier is a genuine but unusual value, such as a bulk corporate order of 50 laptops; it is real and may be important. A data error is an incorrect value, such as an age of 250 or a price entered in paise instead of rupees. Errors should be fixed or removed. Outliers should be investigated and often kept, perhaps analysed separately or with median-based metrics so they do not distort averages.
253

What steps would you apply in Power Query to clean a messy system export?

Save to ▾
Show answer
Typical steps: remove top junk rows and promote the real header row, remove blank rows and columns, set correct data types, trim and clean text, split combined columns such as Name - City, replace error values, fill down for grouped labels, remove duplicates on the business key, and unpivot wide month columns. Rename steps meaningfully so others can follow them, and keep the query foldable when the source is a database.
254

An inner join between orders and customers returns fewer orders than the orders table has. What could be wrong?

Save to ▾
Show answer
Orphan records are child rows whose foreign key has no matching parent, such as orders with a customer_id that does not exist in the customer table. They break joins silently: an inner join drops them and totals fall short. Find them with a LEFT JOIN from the child to the parent and filter where the parent key IS NULL. Then fix the source, add an Unknown member, or report them separately.
255

How should you document data cleaning decisions?

Save to ▾
Show answer
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.
256

Write Python code to extract valid 10-digit Indian mobile numbers from a messy text column.

Save to ▾
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)
257

Customer names in your file show strange characters like 'é' instead of accented letters. What is wrong and how do you fix it?

Save to ▾
Show answer
This is an encoding mismatch: a UTF-8 file was read as a different encoding such as Latin-1 or Windows-1252. Re-read it with the right encoding, for example pd.read_csv(path, encoding='utf-8') or 'utf-8-sig' for files with a byte order mark, or set the file origin in Excel and Power Query import. Avoid re-saving through tools that change encoding, and agree on UTF-8 for all exchanges, especially for Hindi or other Indian language text.
258

Tell me about a time you found a serious data quality issue.

Save to ▾
Show answer
Situation: while building a returns report I noticed one warehouse had zero returns for three months. Task: confirm whether it was real. Action: I compared with the source system and found its return codes had changed after a software update, so my filter missed them. I informed the data owner, updated the mapping and back-filled the history. Result: the corrected report showed that warehouse had the highest return rate, which led to a packaging review.
259

Your sales data contains negative quantities. What do you do with them?

Save to ▾
Show answer
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.
260

Write pandas code to fill missing values in a column with the median of each group.

Save to ▾
Show answer
# Fill missing delivery_days with the median for that city df['delivery_days'] = ( df['delivery_days'] .fillna(df.groupby('city')['delivery_days'].transform('median')) ) # Rows in a city with no values at all stay NaN; fall back to overall median df['delivery_days'] = df['delivery_days'].fillna(df['delivery_days'].median())
261

What is referential integrity and how do orphan records appear in analytics data?

Save to ▾
Show answer
Referential integrity means every foreign key value points to an existing row in the parent table, for example every order's customer_id exists in customers. Orphan records break this rule. They appear when source systems load tables at different times, records are deleted without cascading, or IDs are formatted differently across systems. Detect them with a LEFT JOIN where the parent key is NULL, and decide whether to fix, map or flag them.
262

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?

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

What are MCAR, MAR and MNAR missing data?

Save to ▾
Show answer
MCAR, missing completely at random, means missingness is unrelated to anything, like a random sensor glitch; dropping rows is mostly safe. MAR, missing at random, means missingness depends on other observed variables, for example older customers skipping the email field; impute using those variables. MNAR, missing not at random, means it depends on the missing value itself, like high earners hiding income; this biases results and needs careful handling or extra data.
264

The customer table has near-duplicates like 'Rahul Sharma, 98xxxx1234' and 'Rahul Sharma, +91 98xxxx1234'. How do you deduplicate?

Save to ▾
Show answer
First normalise: trim and lower-case names, collapse spaces, and strip country codes and symbols from phones and emails. Then match on strong keys such as phone or email. For remaining records use fuzzy matching on names with a similarity score, restricted to the same city or pin code to limit comparisons. Review borderline scores manually, keep a mapping from old IDs to a master ID, and fix entry at the source.
265

You loaded a month of sales into the warehouse. How do you check the load is complete and correct?

Save to ▾
Show answer
Reconcile against the source: compare row counts, total amount, and counts by day and by store between source and target. Check for duplicate keys, nulls in mandatory columns, and dates outside the expected month. Compare with the previous month to spot big unexplained jumps. Spot-check a few individual transactions end to end. Automate these checks so each future load produces a small pass or fail report.
266

Sales data from different countries mixes currencies and some weights are in grams while others are in kilograms. How do you handle it?

Save to ▾
Show answer
Never add mixed units. Identify the unit for each row from a currency or unit column, or infer it from the source system. Convert to one standard, for example INR using a dated exchange rate table joined on transaction date, and kilograms using a conversion factor. Keep the original value and unit columns for audit. Document which rates were used, and flag rows where the unit could not be determined.
267

The column names in a monthly vendor file keep changing. How do you make your cleaning pipeline robust?

Save to ▾
Show answer
Agree a data contract with the vendor if possible. In the pipeline, normalise headers by trimming, lower-casing and replacing spaces, then map known variants to standard names through a lookup table you maintain. Validate that all required columns exist after mapping and fail with a clear message if not, instead of producing silent blanks. Select columns by name, never by position, and log any unexpected new columns for review.
268

Where should data cleaning happen: Excel, SQL, Python or Power Query?

Save to ▾
Show answer
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.
269

How can you automate data quality checks in a reporting pipeline?

Save to ▾
Show answer
Define tests as code that run after every load: not-null and unique checks on keys, accepted value lists, referential integrity, row count within an expected range compared with recent days, and freshness of the latest timestamp. Store results in a table and alert the team on failures before dashboards refresh. Start with checks on the few tables behind key KPIs, then expand. This catches issues before stakeholders do.
270

What is the difference between a metric and a KPI?

Save to ▾
Show answer
A metric is any quantity you measure, such as page views, tickets closed or average order value. A KPI, key performance indicator, is a metric chosen because it directly reflects progress towards an important business goal and usually has a target and owner. Every KPI is a metric, but most metrics are not KPIs. A good dashboard leads with a few KPIs and keeps supporting metrics for diagnosis.
271

What is a north star metric?

Save to ▾
Show answer
A north star metric is the single measure that best captures the value a product delivers to customers and predicts long-term growth, such as weekly active learners for an education app or orders delivered for a delivery service. It aligns teams around one outcome. It should be supported by input metrics that teams can influence, such as activation rate and repeat usage, and balanced with guardrails like cost or quality.
272

What are strategic, operational and analytical dashboards?

Save to ▾
Show answer
Strategic dashboards serve leadership with a few high-level KPIs and trends, updated daily to monthly. Operational dashboards monitor live or near-live activity for teams on the ground, such as orders pending dispatch or call queue length, often with alerts. Analytical dashboards let analysts and managers explore data with filters and drill-downs to find causes. Knowing which type you are building decides the level of detail, refresh rate and design.
273

How should you lay out a dashboard page?

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

What are vanity metrics?

Save to ▾
Show answer
Vanity metrics look impressive but do not help decisions or reflect real value, such as total app downloads, total registered users or social media likes. They usually only go up. Better alternatives are actionable metrics such as active users, retention by cohort, conversion rate and revenue per user. A quick test: if the number changed, would anyone do anything differently? If not, it probably does not belong on the main dashboard.
275

How do you show actual versus target on a dashboard?

Save to ▾
Show answer
Use a KPI card with actual, target and variance as both value and percentage, coloured by status. For trends, a bar or line for actual with a target line or band works well. Bullet charts compare actual against target and ranges compactly in tables. Make the period clear, such as month to date versus prorated monthly target, because comparing half a month of sales to a full month target looks falsely bad.
276

What is RAG status and how should thresholds be set?

Save to ▾
Show answer
RAG means red, amber, green, a traffic-light status that shows at a glance whether a KPI is on track. Thresholds should be agreed with the business owner in advance, for example green at or above 100 percent of target, amber from 90 to 99 percent, red below 90 percent. Do not rely only on colour; add icons or text for colour-blind users, and avoid so many reds that nobody reacts.
277

Tell me about a time you were at risk of missing a reporting deadline.

Save to ▾
Show answer
Situation: a month-end report was due at 10 am, and at 8 am the source data had not loaded. Task: deliver something reliable. Action: I informed my manager immediately, checked with the data team for an estimate, and prepared the report on the previous day's data, clearly labelled. Result: leadership got the report on time with a caveat, and I sent the final version by noon. We later added a data arrival alert.
278

A KPI on the leadership dashboard jumps 35% overnight. Before anyone celebrates, what do you check?

Save to ▾
Show answer
First check the data: did the refresh load duplicate rows, did a filter or definition change, did late data from the previous day arrive, or did a new source or currency get added? Compare the raw count with the source system. Then check business reasons, such as a big promotion, a bulk order or a holiday. Only after the number is confirmed should it be communicated, with the reason attached.
279

How do you calculate month-over-month growth?

Save to ▾
Show answer
Aggregate revenue by month, use LAG(revenue) OVER (ORDER BY month), then (revenue - prev) / prev x 100.
280

What is the difference between a dashboard and a report?

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

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

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

How do you define and choose KPIs?

Save to ▾
Show answer
Pick metrics tied to a business goal that are measurable, actionable and owned by someone, and pair a lagging metric with a leading one.
283

What are leading and lagging indicators? Give examples.

Save to ▾
Show answer
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.
284

A stakeholder wants 20 more visuals added to an already crowded dashboard page. How do you respond?

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

Which KPIs would you include in an HR attrition dashboard?

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

What KPIs would you track for a customer support call centre?

Save to ▾
Show answer
Volume and staffing: calls offered, answered, abandoned rate and average speed of answer. Efficiency: average handle time and occupancy. Quality and outcome: first contact resolution, customer satisfaction or CSAT score, and repeat contacts within 7 days. Service level, for example 80 percent of calls answered in 20 seconds, ties them together. Balance speed metrics with quality, otherwise agents rush calls to cut handle time.
287

You launched a dashboard a month ago and usage logs show almost nobody opens it. What do you do?

Save to ▾
Show answer
Talk to the intended users before redesigning. Find out whether they know it exists, whether it answers their real questions, whether they trust the numbers, and whether it fits their routine. Common fixes are fewer, sharper KPIs, faster loading, a scheduled email snapshot, or embedding it in a meeting that already happens. Then measure usage again. Sometimes the honest answer is that the dashboard is not needed and can be retired.
288

Tell me about a time you had to say no to a stakeholder's request.

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

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

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
290

A manager asks for a dashboard that refreshes every minute. How do you decide the right refresh frequency?

Save to ▾
Show answer
Match refresh to how quickly decisions are made. A monthly board pack needs daily refresh at most, while a warehouse dispatch board may need 15-minute or live updates. Faster refresh costs more in source load, licensing and complexity, and can show incomplete data. Ask how quickly someone will act on a change, check when upstream systems finish loading, and always display the last refreshed time on the dashboard.
291

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

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

What questions do you ask a stakeholder before building a dashboard?

Save to ▾
Show answer
Who will use it and what decisions will they make with it? Which three to five questions must it answer? What are the KPI definitions, targets and comparison periods? Which data sources are trusted? How often will they look at it, and on what device? Who needs access and are there security restrictions? What does success look like in a month? I summarise the answers in a short brief and get sign-off before building.
293

Tell me about a time a dashboard you built changed a business decision.

Save to ▾
Show answer
Situation: our operations team assumed late deliveries were evenly spread. Task: I built a delivery performance dashboard. Action: I added a breakdown by courier partner and pin code cluster with a weekly trend, and reviewed it with the operations manager. Result: it showed one partner caused 60 percent of delays in two zones. The company shifted those zones to another partner, and on-time delivery rose from 84 to 92 percent in two months.
294

Why do ratio metrics need extra care on a dashboard compared with count metrics?

Save to ▾
Show answer
A ratio like conversion rate or average order value can move because the numerator or the denominator changed, so always show both components nearby. Ratios can look dramatic on small bases, such as 2 of 4 users. They also cannot be averaged across segments; you must divide total numerator by total denominator, otherwise small segments get too much weight. Counts are simpler but need context like trends or targets.
295

Tell me about a time you defined a new metric for a team.

Save to ▾
Show answer
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.
296

Tell me about a time you retired or consolidated reports that nobody used.

Save to ▾
Show answer
Situation: our team maintained over 60 scheduled reports and spent two days a month fixing them. Task: reduce the load without upsetting users. Action: I checked usage logs, listed reports not opened in 90 days, contacted owners and merged overlapping ones into four dashboards with filters. I archived the rest with a 30-day notice. Result: we retired 38 reports, only two were requested back, and maintenance time halved.
297

Design a dashboard for CXOs — what principles do you follow?

Save to ▾
Show answer
Start from the business questions, show 4–6 top KPIs with trend vs target, allow drill-downs, keep colours consistent, remove clutter, add time/region filters and give one clear insight per visual.
298

How would you design a sales dashboard for executives?

Save to ▾
Show answer
Start with KPIs (revenue, margin, growth), add trend and comparison to target, allow drill-down by region and product, keep it to one screen and test with the audience.
299

How do you make sure a metric like active customer means the same thing across all teams?

Save to ▾
Show answer
Write a clear definition in a shared metric dictionary: formula, filters, time window, grain, owner and source table, for example a customer with at least one paid order in the last 90 days. Implement it once, in a governed dataset or semantic layer, and have all dashboards reuse it rather than recalculating. Version changes, announce them and show the definition in the dashboard tooltip. Review new metrics before they are published.
300

The finance dashboard and the sales dashboard show different revenue for last month. How do you resolve it?

Save to ▾
Show answer
Start by listing both definitions: gross or net of returns and discounts, with or without tax, booking date or invoice date, and which entities are included. Compare at a detailed level for one region to find exactly where the difference arises. Usually both are correct for their purpose. Agree with both owners which is the official revenue KPI, rename the other clearly, such as booked sales, and document it in the metric dictionary.
301

Write SQL to calculate monthly customer churn rate.

Save to ▾
Show answer
WITH active AS ( SELECT DISTINCT customer_id, DATE_TRUNC('month', order_date) AS month FROM orders ) SELECT a.month, COUNT(*) AS active_customers, COUNT(*) - COUNT(n.customer_id) AS churned_next_month, ROUND(100.0 * (COUNT(*) - COUNT(n.customer_id)) / COUNT(*), 1) AS churn_pct FROM active a LEFT JOIN active n ON n.customer_id = a.customer_id AND n.month = a.month + INTERVAL '1 month' GROUP BY a.month ORDER BY a.month;
302

What is a KPI tree and how does it help analysis?

Save to ▾
Show answer
A KPI tree breaks a top-level metric into the drivers that multiply or add up to it. Revenue = traffic x conversion rate x average order value, and each branch splits further, such as traffic by channel. When revenue drops, the tree shows which driver moved, so investigations are structured rather than random. It also links teams to the branch they own and helps choose leading indicators for a dashboard.
303

Business teams have built hundreds of their own reports, each with its own revenue logic. How do you balance self-service with governed dashboards?

Save to ▾
Show answer
A good model is a governed core plus self-service on top. The central team builds certified datasets with agreed metric definitions and a small set of official dashboards for key KPIs. Business users then build their own views on those certified datasets, not on raw extracts. This gives speed and flexibility without twenty versions of revenue. Training, naming conventions and periodic clean-up of unused content keep it healthy.
304

Write SQL to calculate daily active users and the DAU/MAU stickiness ratio.

Save to ▾
Show answer
WITH dau AS ( SELECT CAST(event_ts AS DATE) AS day, COUNT(DISTINCT user_id) AS dau FROM events GROUP BY CAST(event_ts AS DATE) ) SELECT d.day, d.dau, (SELECT COUNT(DISTINCT e.user_id) FROM events e WHERE CAST(e.event_ts AS DATE) BETWEEN d.day - INTERVAL '29 days' AND d.day) AS mau, ROUND(100.0 * d.dau / (SELECT COUNT(DISTINCT e.user_id) FROM events e WHERE CAST(e.event_ts AS DATE) BETWEEN d.day - INTERVAL '29 days' AND d.day), 1) AS stickiness_pct FROM dau d ORDER BY d.day;
305

How do you choose the right chart?

Save to ▾
Show answer
Line for trends, bar for comparisons, scatter for relationships, histogram for distributions and pie only for few parts of a whole.
306

What is data storytelling?

Save to ▾
Show answer
Data storytelling is communicating insights by combining three things: data, the evidence; visuals, charts that make patterns obvious; and narrative, the context and so-what that explains why it matters and what to do. A table of numbers informs, but a story persuades someone to act. For an analyst, it means leading with the key finding, showing just enough evidence, and ending with a clear recommendation.
307

What is a good structure for a data story?

Save to ▾
Show answer
A simple structure is context, conflict, resolution. Context sets up what the audience already knows, such as our target was 10 percent growth. Conflict shows the surprising finding or problem, such as growth is only 3 percent because repeat buyers dropped. Resolution gives the recommendation and expected impact. Another option is situation, complication, question, answer. Either way, the audience should know the main message within the first minute.
308

A stakeholder asks for a pie chart of 12 product categories. When is a pie chart acceptable, and what would you suggest?

Save to ▾
Show answer
A pie chart works only for showing parts of a whole with very few categories, two to four, where one share clearly dominates, such as 70 percent of orders are prepaid. Avoid it when there are many slices, when values are close, or when comparing across periods, because people judge angles poorly. A sorted bar chart is almost always easier to read and compare. Never use 3D pies.
309

What are pre-attentive attributes and how do you use them in charts?

Save to ▾
Show answer
Pre-attentive attributes are visual properties the brain notices in a fraction of a second before conscious thought, such as colour, size, position, length and bold weight. Use them to direct attention: make all bars grey and highlight only the declining region in a strong colour, or bold the one number that matters. Using too many highlights cancels the effect, so emphasise one or two things per chart.
310

How do you declutter a chart?

Save to ▾
Show answer
Remove everything that does not help the message: heavy gridlines, borders, background colour, 3D effects, redundant legends and too many decimal places. Label data directly instead of using a legend, sort bars meaningfully, round numbers, and use one highlight colour with grey for the rest. Shorten axis labels and keep consistent fonts. Ask whether removing each element would lose information; if not, remove it.
311

How can a chart mislead even when the numbers are correct?

Save to ▾
Show answer
Common tricks are a truncated y-axis on bar charts, which exaggerates small differences; inconsistent scales across side-by-side charts; dual axes chosen to imply a correlation; cherry-picked time windows; cumulative charts that always rise; and area or 3D effects that distort size. Bars should start at zero, while line charts may zoom in if clearly labelled. Always check that the visual impression matches the actual magnitude.
312

Tell me about a time you worked with messy data under a tight deadline.

Save to ▾
Show answer
Situation: for a college placement project, I received survey data from three forms with different column names two days before submission. Task: combine and analyse it. Action: I mapped columns to a standard list, cleaned obvious issues like duplicate responses and mixed date formats in Power Query, and noted assumptions. I focused analysis on the five questions that mattered most. Result: we submitted on time, and the faculty praised the documented cleaning steps.
313

Why do you want to work as a data analyst?

Save to ▾
Show answer
A good answer links genuine interest, evidence and the role. For example: I enjoy turning messy questions into clear answers. In my final-year project I analysed two years of canteen sales in Excel and Python and showed that changing the menu on exam weeks could cut waste. I liked that the analysis led to an actual change. I want a role where my SQL, Power BI and communication skills help teams make better decisions.
314

How do you present insights to non-technical stakeholders?

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

What is an action title and why should you use one?

Save to ▾
Show answer
An action title states the insight of a chart or slide as a sentence instead of a label. Instead of Monthly Sales by Region, write South region drove 70 percent of the decline since July. Busy executives may read only the titles, so each one should carry the message and, ideally, the so-what. The chart below then proves the title. Read only your titles in order: they should tell the whole story.
316

During a review, an executive asks a question about your numbers that you cannot answer on the spot. What do you do?

Save to ▾
Show answer
Do not guess. Say clearly that you do not have that cut ready, restate the question to confirm it, and give a time by which you will reply, for example by end of day. If you know a partial answer or a reasonable expectation, share it with a caveat. Then follow up on time with a short, precise answer. Being honest and reliable builds more trust than a confident wrong number.
317

How would you present the same churn analysis to the CEO and to the customer support team?

Save to ▾
Show answer
For the CEO, lead with the headline and money: churn rose 2 points, costing about 3 crore annually, mainly from one segment, with two recommended actions and their expected impact, all on one or two slides. For the support team, go deeper into drivers they control: ticket types, resolution times and specific customer pain points, with operational next steps and how progress will be tracked. Same data, different level and call to action.
318

How would you write a short email summarising an analysis for a manager?

Save to ▾
Show answer
Use a clear subject that states the finding, such as Q3 repeat purchase rate fell 4 points, mainly in Tier 2 cities. Open with one or two sentences of the key insight and the recommendation. Follow with three bullet-style lines of supporting evidence with numbers, then any caveats about data, and a clear next step or decision needed with a date. Attach or link the detailed workings instead of pasting large tables.
319

Your findings deck has 30 slides and you have 10 minutes with leadership. How do you cut it down?

Save to ▾
Show answer
Write the single key message first, then keep only slides that directly support it, usually three to five: the headline finding, the main driver, the impact in money, and the recommendation with next steps. Merge related charts, use action titles, and move everything else to an appendix for questions. Rehearse to fit in about seven minutes, leaving time for discussion, because the discussion is where decisions are made.
320

Tell me about a time your analysis was challenged in a meeting.

Save to ▾
Show answer
Situation: while presenting regional sales, a manager said my numbers were wrong because his team's tracker showed more. Task: handle it without derailing the meeting. Action: I acknowledged the concern, explained my source and definition briefly, and offered to reconcile after the meeting. That afternoon we found his tracker counted orders before cancellations. Result: we agreed on net orders as the standard, and he later asked me to rebuild his tracker.
321

Tell me about a time you found a mistake in a report after it had already been shared.

Save to ▾
Show answer
Situation: a day after sending a weekly report, I noticed one region's numbers included test orders. Task: correct it before decisions were made. Action: I quantified the impact, a 3 percent overstatement, and emailed the same audience with the corrected figures, a short explanation and the fix I had added to prevent repeats. Result: my manager appreciated the quick ownership, and the test-order filter became part of our shared dataset.
322

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

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

You must present to the sales team that they missed their quarterly target by 18%. How do you handle it?

Save to ▾
Show answer
Lead with the fact clearly, then show where the gap came from, such as which regions, products or deal stages, so it becomes actionable rather than a blame session. Separate controllable causes like lower conversion from uncontrollable ones like a delayed product launch. Highlight what did work, and end with two or three specific actions and the metrics to track them. Share the numbers with sales leaders privately first, to avoid surprises.
324

Tell me about a time you had to present findings when the data was incomplete.

Save to ▾
Show answer
Situation: leadership wanted the impact of a new store format after three weeks, but two stores had missing POS data. Task: give a useful answer on time. Action: I presented results for the complete stores, showed the range of outcomes if missing stores performed like the best and worst cases, and clearly marked what was unknown. Result: they approved a limited extension, and the final four-week analysis confirmed the early direction.
325

Your analysis shows that a flagship initiative championed by leadership is not working. How do you present it?

Save to ▾
Show answer
Prepare carefully and stay factual. Share the finding early with the initiative owner privately so they are not surprised in a big meeting. Lead with the question asked, the method and the evidence, acknowledge limitations and what is working, and avoid blaming anyone. Offer options, such as fixing a specific step, narrowing the scope or stopping, with estimated impact. Bad news delivered with solutions is received far better.
326

What is the pyramid principle and how does it apply to presenting analysis?

Save to ▾
Show answer
The pyramid principle means starting with the answer, then giving the few key arguments that support it, then the detailed data under each argument. Analysts are tempted to present in the order they worked, from data to conclusion, but leaders want the conclusion first. Example: we should cut the free-delivery threshold, because it lifts order frequency, margin impact is small and competitors already did it, each backed by evidence.
327

Leadership wants a single revenue number for next quarter, but your forecast is uncertain. How do you present it?

Save to ▾
Show answer
Show ranges, not just a single number: a forecast line with a shaded prediction interval, or a statement like revenue will likely be between 48 and 55 crore, most likely 51. Explain the main assumptions and what would change the outcome, perhaps with best, base and worst scenarios. Use plain words such as likely or unlikely consistently. Hiding uncertainty makes a forecast look precise but leads to bad decisions when reality differs.
328

A senior stakeholder insists the data supports their conclusion, but your analysis does not. How do you handle it?

Save to ▾
Show answer
Stay respectful and focus on evidence, not opinions. Ask what they are seeing that leads to their view, as they may know context you lack. Walk through your method transparently, show the specific numbers, and test their hypothesis together on a cut they suggest. If the data still disagrees, present both views with their assumptions to the decision maker. Never adjust the analysis to fit a preferred answer.
329

Describe a time you influenced a decision without having formal authority.

Save to ▾
Show answer
Situation: the marketing team planned to spend most of its budget on a channel with high clicks. Task: as the analyst, I believed the channel brought low-quality customers. Action: I built a cohort view showing 90-day repeat purchase and revenue per customer by channel, and walked the marketing lead through it before the planning meeting. Result: they shifted 30 percent of that budget to better channels, and the next quarter's cost per retained customer fell noticeably.
330

Tell me about a time you mentored or guided a junior analyst.

Save to ▾
Show answer
Situation: a new analyst on my team kept getting review comments for inconsistent numbers. Task: help him become independent. Action: I paired with him on two reports, shared a checklist covering definitions, row-count checks and chart standards, and set a weekly 30-minute review where he presented his work. Result: within six weeks his reports passed review with minor edits, and the checklist became part of team onboarding.
331

What is dimensional modelling?

Save to ▾
Show answer
Dimensional modelling is a way of designing analytical tables around business processes. Numeric events such as sales or payments go into fact tables, and descriptive context such as customer, product, store and date goes into dimension tables. The design is easy for business users to understand and fast for BI tools to aggregate. For example, a retail model might have one sales fact joined to date, product, store and customer dimensions.
332

What is a snowflake schema and when would you choose it over a star schema?

Save to ▾
Show answer
A snowflake schema normalises dimensions into several related tables, for example product linked to subcategory linked to category, instead of keeping one flat product dimension. It saves some storage and avoids repeating attribute values, but needs more joins and is harder for users and BI tools. Most analytics teams prefer a star schema; snowflaking makes sense when a hierarchy is huge, shared by many dimensions or maintained by a separate team.
333

What is the difference between a surrogate key and a natural key?

Save to ▾
Show answer
A natural key comes from the source system and has business meaning, such as a customer mobile number or product SKU. A surrogate key is a meaningless integer or hash generated in the warehouse. Surrogate keys protect the model when source keys change, get reused or clash across systems, and they are essential for slowly changing dimensions where one customer has several historical rows. Keep the natural key as an attribute for lookups.
334

What is a degenerate dimension?

Save to ▾
Show answer
A degenerate dimension is a dimension key stored directly in the fact table with no separate dimension table, because it has no other attributes worth storing. Invoice number, order number and transaction ID are typical examples. They are useful for grouping order lines back into an order, counting distinct orders or tracing a row to the source system. Creating a separate table just for the number would add a useless join.
335

What is the difference between ETL and ELT?

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

What is the difference between OLTP and OLAP systems?

Save to ▾
Show answer
OLTP systems run day-to-day operations, such as billing or order entry. They handle many small inserts and updates, use normalised tables and need fast single-row access. OLAP systems support analysis, scanning millions of rows to aggregate sales by month or region. They use denormalised star schemas, columnar storage and are mostly read-heavy. Analysts should query OLAP copies so heavy reports do not slow down the live application.
337

Write a CREATE TABLE statement for a sales fact table linked to date, product and store dimensions.

Save to ▾
Show answer
CREATE TABLE fact_sales ( date_key INT NOT NULL REFERENCES dim_date(date_key), product_key INT NOT NULL REFERENCES dim_product(product_key), store_key INT NOT NULL REFERENCES dim_store(store_key), invoice_no VARCHAR(20) NOT NULL, -- degenerate dimension quantity INT NOT NULL, net_amount DECIMAL(12,2) NOT NULL, discount_amount DECIMAL(12,2) DEFAULT 0 );
338

What is a data mart?

Save to ▾
Show answer
A data mart is a subset of the warehouse built for one department or subject, such as sales, finance or HR. It contains the facts and dimensions that team needs, often pre-aggregated and with business-friendly names. Marts make reporting faster and simpler for that team. Ideally they are built from the central warehouse using conformed dimensions, so the finance mart and the sales mart still agree on revenue.
339

What is the grain of a fact table and why should you decide it first?

Save to ▾
Show answer
The grain is the exact meaning of one row in a fact table, such as one order line, one daily store total or one monthly account balance. You decide it first because it controls which dimensions can join, which measures are valid and how totals behave. Mixing grains in one table, for example order lines and order-level shipping charges, causes double counting. Always state the grain in one sentence before adding columns.
340

What are additive, semi-additive and non-additive facts?

Save to ▾
Show answer
Additive facts, like sales amount or quantity, can be summed across every dimension. Semi-additive facts, like account balance or stock on hand, can be summed across accounts or stores but not across time; for time you take the closing or average value. Non-additive facts, like percentages, ratios or unit prices, cannot be summed at all, so you store their components (profit and revenue) and calculate the ratio at query time.
341

What is a conformed dimension?

Save to ▾
Show answer
A conformed dimension is one dimension table with the same keys, attributes and meanings shared by several fact tables. For example, one date and one product dimension used by sales, returns and inventory facts. Because they conform, you can compare sales and returns by the same product category in one report without mismatched definitions. Conformed dimensions are the main way an enterprise warehouse stays consistent across departments.
342

What is a role-playing dimension?

Save to ▾
Show answer
A role-playing dimension is one physical dimension used several times by the same fact table in different roles. The classic case is the date dimension: an orders fact has order date, ship date and delivery date keys, all pointing to the same date table. In SQL you join it three times with different aliases; in Power BI you keep one active relationship and use USERELATIONSHIP or create separate date views for each role.
343

Write SQL to generate a simple date dimension for 2026.

Save to ▾
Show answer
-- PostgreSQL syntax CREATE TABLE dim_date AS SELECT TO_CHAR(d, 'YYYYMMDD')::INT AS date_key, d::DATE AS full_date, EXTRACT(YEAR FROM d) AS year, EXTRACT(QUARTER FROM d) AS quarter, EXTRACT(MONTH FROM d) AS month_no, TO_CHAR(d, 'Mon') AS month_name, TO_CHAR(d, 'Dy') AS weekday, EXTRACT(ISODOW FROM d) IN (6, 7) AS is_weekend FROM GENERATE_SERIES(DATE '2026-01-01', DATE '2026-12-31', INTERVAL '1 day') AS d;
344

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

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

A query on a 2-billion-row fact table in a cloud warehouse scans the whole table even when you filter one week. How do you fix it?

Save to ▾
Show answer
Partition the table by date so the engine can prune partitions outside the filtered week, and cluster or sort by frequently filtered columns such as store or customer. Make sure the filter is on the partition column directly, not wrapped in a function like CAST or DATE_TRUNC that blocks pruning. Select only needed columns, because storage is columnar. For dashboards, also consider a pre-aggregated daily summary table.
346

What is columnar storage and why do analytical warehouses use it?

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

What is the medallion architecture (bronze, silver, gold)?

Save to ▾
Show answer
Medallion architecture organises a lakehouse into three layers. Bronze holds raw data exactly as ingested, for audit and reprocessing. Silver holds cleaned, deduplicated, typed and joined data. Gold holds business-ready tables such as star schemas and aggregates used by dashboards. Each layer is built from the previous one by repeatable jobs, so if a cleaning rule changes you can rebuild silver and gold from bronze.
348

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?

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

Tell me about a time you designed or improved a data model.

Save to ▾
Show answer
Situation: our sales reports used five separate Excel extracts and numbers never matched. Task: build one reliable model. Action: I listed the business questions, fixed the grain at invoice line, built conformed date, product and branch dimensions and moved logic into SQL views, then rebuilt the Power BI reports on top. Result: refresh time dropped from 40 to 6 minutes and month-end reconciliation queries fell sharply.
350

What is a junk dimension?

Save to ▾
Show answer
A junk dimension combines several low-cardinality flags and indicators, such as payment type, is_gift, is_express and order channel, into one small dimension table. Each row is a unique combination of those values, and the fact table stores a single key to it. This keeps the fact table narrow and avoids many tiny dimensions or loose flag columns. It works best when the combinations are limited, typically a few hundred rows.
351

What is a factless fact table? Give an example.

Save to ▾
Show answer
A factless fact table records that an event or relationship happened but has no numeric measures, only dimension keys. Examples are student attendance (student, class, date) or which products were on promotion in which store each day. You analyse it by counting rows. A promotion coverage table is useful for questions like which promoted products did not sell, by comparing it with the sales fact.
352

What are transaction, periodic snapshot and accumulating snapshot fact tables?

Save to ▾
Show answer
A transaction fact stores one row per event, such as each payment. A periodic snapshot stores the state at regular intervals, such as daily stock per store or month-end loan balance. An accumulating snapshot has one row per process instance, like an order, updated as it reaches milestones such as placed, packed, shipped and delivered, with a date for each. Choose based on whether the question is about events, states or process durations.
353

What is an enterprise bus matrix?

Save to ▾
Show answer
A bus matrix is a planning grid with business processes as rows, such as orders, shipments and returns, and conformed dimensions as columns, such as date, customer, product and warehouse. A tick shows which dimensions each process uses. It helps prioritise which fact tables to build, shows which dimensions must be shared and conformed first, and gives leaders a one-page roadmap of the warehouse without technical detail.
354

Write SQL to get each order's customer city as it was on the order date, using an SCD Type 2 customer dimension.

Save to ▾
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');
355

What is a bridge table and when do you need one?

Save to ▾
Show answer
A bridge table resolves a many-to-many relationship between a fact and a dimension. For example, one bank account can have several holders and one customer can hold several accounts, so a bridge table stores account_key, customer_key and often an allocation weight. Without it, joining facts to customers duplicates amounts. Use weights to split balances, or report clearly that totals across customers will exceed the true total.
356

Write SQL to incrementally load new and changed products into a dimension using MERGE.

Save to ▾
Show answer
MERGE INTO dim_product t USING stg_product s ON t.product_id = s.product_id WHEN MATCHED AND (t.name <> s.name OR t.category <> s.category) THEN UPDATE SET name = s.name, category = s.category, updated_at = CURRENT_TIMESTAMP WHEN NOT MATCHED THEN INSERT (product_id, name, category, updated_at) VALUES (s.product_id, s.name, s.category, CURRENT_TIMESTAMP);
357

Leadership wants sales by store manager, but managers move between stores every few months. How would you model it?

Save to ▾
Show answer
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.
358

Actual sales are at order-line level but targets are set per region per month. How do you model them together?

Save to ▾
Show answer
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.
359

What is an A/B test and why is random assignment so important?

Save to ▾
Show answer
An A/B test is a controlled experiment where users are split into a control group that sees the current version and a treatment group that sees a change, and you compare a metric such as conversion. Random assignment makes the groups similar in every way except the change, including things you cannot measure. That is why a difference in results can be attributed to the change itself, not to user mix or seasonality.
360

How do you write a good hypothesis for an experiment?

Save to ▾
Show answer
A good hypothesis names the change, the expected effect, the metric and the reason. For example: showing delivery date on the product page will increase add-to-cart rate by at least 2% because uncertainty about delivery stops purchases. It should be written before the test starts, be measurable, and state the null hypothesis of no difference. A vague hypothesis like 'the new page will be better' leads to cherry-picking metrics afterwards.
361

What are primary metrics and guardrail metrics in an A/B test?

Save to ▾
Show answer
The primary metric is the one decision metric the test is designed to move, such as checkout conversion. Guardrail metrics are ones that must not get worse, such as page load time, refund rate, unsubscribe rate or revenue per user. A change that lifts conversion but increases cancellations should not ship. Define both before launch, so the decision rule is clear and nobody picks the favourable metric afterwards.
362

What are novelty and primacy effects in experiments?

Save to ▾
Show answer
A novelty effect is when users engage more with a change simply because it is new, so the early lift fades over time. A primacy effect is the opposite: existing users resist a change at first because they are used to the old design, so results start poor and improve. Both mean short tests mislead. Run tests for at least one or two full weeks and compare new versus returning users.
363

Write SQL to calculate users, conversions and conversion rate for each test variant.

Save to ▾
Show answer
SELECT a.variant, COUNT(DISTINCT a.user_id) AS users, COUNT(DISTINCT o.user_id) AS converters, ROUND(100.0 * COUNT(DISTINCT o.user_id) / COUNT(DISTINCT a.user_id), 2) AS conversion_pct FROM experiment_assignments a LEFT JOIN orders o ON o.user_id = a.user_id AND o.order_ts >= a.assigned_ts WHERE a.experiment_id = 'checkout_v2' GROUP BY a.variant;
364

When would you use a one-tailed test instead of a two-tailed test?

Save to ▾
Show answer
A two-tailed test checks for a difference in either direction and is the safer default for A/B tests. A one-tailed test checks only one direction, such as treatment being better, and needs fewer users. Use it only if you decided beforehand that you would act the same way whether the change has no effect or a negative effect. Otherwise you may miss harm, which is why most teams stay two-tailed.
365

What is a holdout group and why do teams keep one?

Save to ▾
Show answer
A holdout group is a small share of users, often 5-10%, kept out of a feature or of all marketing campaigns for a long time. Comparing them with everyone else shows the cumulative long-term impact, which individual short tests miss, and checks whether many small wins really add up. For example, a notification holdout shows whether push messages raise monthly retention or just shift activity earlier.
366

What is the difference between an A/B test, an A/B/n test and a multivariate test?

Save to ▾
Show answer
An A/B test compares one change with control. An A/B/n test compares several versions of one element, such as three headlines, with control. A multivariate test changes several elements at once, such as headline and button colour, and tests every combination to see each element's effect and their interactions. Multivariate tests need much more traffic because users are spread across many combinations.
367

Your A/B test ends with no significant difference. How do you report it?

Save to ▾
Show answer
A flat result is still useful. Report the observed difference with its confidence interval, for example -0.3% to +0.8%, which shows the range of effects still possible. State whether the test had enough power for the planned MDE. If it did, conclude that any effect is probably small and the team can choose based on cost or simplicity. Share learnings so the same idea is not retested blindly.
368

What are statistical power and minimum detectable effect (MDE)?

Save to ▾
Show answer
Power is the probability that the test detects a real effect of a given size, commonly set at 80%. The minimum detectable effect is the smallest lift you care about and can reliably detect with your sample size. Smaller MDEs, lower baseline rates and higher power all need more users. If you can only get 10,000 users a week, you may only be able to detect large lifts, which should shape what you test.
369

Write Python code to calculate the sample size per group for a conversion test with a 4% baseline and a 10% relative lift.

Save to ▾
Show answer
from statsmodels.stats.power import NormalIndPower from statsmodels.stats.proportion import proportion_effectsize baseline = 0.04 target = baseline * 1.10 # 4.4% effect = proportion_effectsize(target, baseline) n = NormalIndPower().solve_power( effect_size=effect, alpha=0.05, power=0.8, alternative='two-sided' ) print(round(n)) # roughly 39,000 users per group
370

What is the unit of randomisation in an experiment, and how do you choose it?

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

What is an A/A test and why would you run one?

Save to ▾
Show answer
An A/A test splits users into two groups that both see the same version. Since nothing differs, you expect no significant difference except about 5% of the time by chance. It checks that randomisation, tracking and the analysis pipeline work correctly, and it reveals sample ratio mismatches or metric variance before real tests depend on the platform. Running many A/A tests also confirms your false positive rate is near 5%.
372

Write Python code to test whether conversion differs between control and treatment.

Save to ▾
Show answer
from statsmodels.stats.proportion import proportions_ztest, proportion_confint conversions = [1180, 1295] # control, treatment visitors = [30000, 30100] z, p = proportions_ztest(conversions, visitors) print(f'z = {z:.2f}, p = {p:.4f}') for c, n in zip(conversions, visitors): low, high = proportion_confint(c, n, method='wilson') print(f'rate {c/n:.3%} 95% CI {low:.3%} to {high:.3%}')
373

Your test increased conversion rate by 3% but average order value dropped by 5%. What do you recommend?

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

On day 4 of a planned 14-day test, the product manager wants to stop because the dashboard shows treatment winning. What do you say?

Save to ▾
Show answer
I would explain that early results swing a lot and stopping on a good day inflates false positives, and that four days miss weekly patterns like weekend shopping. I would show how the lift has moved day by day and agree to the planned end date. If speed really matters, we can agree a sequential testing rule for future tests, but changing the rule mid-test after seeing data is not valid.
375

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

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

Marketing wants to A/B test two email subject lines on a list of 2,000 subscribers. What do you advise?

Save to ▾
Show answer
With 1,000 per group and a typical click rate of 3%, only a very large difference, roughly 70% relative or more, would be detectable, so a small real lift would look like noise. I would suggest testing on a larger send, pooling several campaigns, or using open-to-click rate with a bigger effect. If they still run it, report the confidence interval and treat the result as directional.
377

What is the peeking problem in A/B testing?

Save to ▾
Show answer
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.
378

Your 50/50 test has 52,400 users in control and 47,600 in treatment. What does this tell you?

Save to ▾
Show answer
This is a sample ratio mismatch. A chi-square test on these counts gives a tiny p-value, so the gap is not chance. Something is filtering users unevenly, such as the treatment page crashing on some devices, a redirect losing tracking, bots in one arm or a bug in assignment. The results cannot be trusted until the cause is found. Check splits by browser, platform and day to locate it.
379

Why is testing many metrics or many variants at once a problem, and how do you handle it?

Save to ▾
Show answer
Each comparison at alpha 0.05 has a 5% false positive chance, so checking 20 metrics will probably flag one as significant by luck. This is the multiple comparisons problem. Handle it by choosing one primary metric in advance, treating others as exploratory, and adjusting p-values with Bonferroni or Benjamini-Hochberg when several comparisons matter. With many variants, compare each to control and adjust, or use a larger sample.
380

Why are A/B tests tricky in two-sided marketplaces such as ride-hailing or food delivery?

Save to ▾
Show answer
In marketplaces, groups share supply, so the treatment can affect control. If treatment riders get a discount and book more, fewer drivers remain for control riders, which makes treatment look better than it would at full rollout. This violates the independence assumption. Teams use cluster randomisation by city or region, switchback tests that alternate treatment by time slots, or model the spillover when estimating impact.
381

An A/B test is flat overall but significant for mobile users in Tier 2 cities. Do you ship it only for that segment?

Save to ▾
Show answer
Not straight away. Slicing results into many segments makes a chance finding likely, so this is a hypothesis, not a conclusion. Check whether the segment was planned in advance, how many segments were examined, and whether there is a plausible reason, such as slower networks benefiting from a lighter page. If it still looks promising, run a follow-up test targeted at that segment with enough sample to confirm it.
382

What is the difference between frequentist and Bayesian A/B testing?

Save to ▾
Show answer
Frequentist testing asks how surprising the data would be if there were no difference, and gives p-values and confidence intervals with a fixed sample size. Bayesian testing combines a prior with the data to give a probability that treatment beats control and the expected loss of choosing it. Bayesian results are easier for business users to read, but still depend on priors and still need sensible stopping rules.
383

What is CUPED and how does it speed up experiments?

Save to ▾
Show answer
CUPED is a variance reduction method that uses each user's pre-experiment behaviour, such as spend in the previous four weeks, as a covariate. You adjust the outcome by removing the part explained by past behaviour, which is unrelated to the treatment because assignment was random. The lower variance gives tighter confidence intervals, so you can detect the same effect with fewer users or a shorter test, often 20-50% faster.
384

How do you measure impact when a proper A/B test is not possible?

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

Write Python code for a simple difference-in-differences estimate of a store pilot.

Save to ▾
Show answer
import pandas as pd # df columns: store_type ('pilot' or 'control'), period ('pre' or 'post'), sales avg = df.pivot_table(index='store_type', columns='period', values='sales', aggfunc='mean') pilot_change = avg.loc['pilot', 'post'] - avg.loc['pilot', 'pre'] control_change = avg.loc['control', 'post'] - avg.loc['control', 'pre'] did = pilot_change - control_change print(f'Estimated pilot effect per store: {did:.1f}')
386

Write Python code to bootstrap a 95% confidence interval for the difference in revenue per user between two groups.

Save to ▾
Show answer
import numpy as np rng = np.random.default_rng(42) control = df.loc[df.variant == 'A', 'revenue'].to_numpy() treat = df.loc[df.variant == 'B', 'revenue'].to_numpy() diffs = [] for _ in range(5000): c = rng.choice(control, size=len(control), replace=True) t = rng.choice(treat, size=len(treat), replace=True) diffs.append(t.mean() - c.mean()) low, high = np.percentile(diffs, [2.5, 97.5]) print(f'95% CI for difference: {low:.2f} to {high:.2f}')
387

What is the difference between users, sessions and events in Google Analytics 4?

Save to ▾
Show answer
GA4 uses an event-based model: every interaction, such as page_view, scroll, add_to_cart or purchase, is an event with parameters. A session is a group of events from one user within a period of activity, ending after 30 minutes of inactivity by default. A user is identified by a device cookie, app instance or a logged-in user ID. One user can have many sessions, and each session many events.
388

What is the difference between bounce rate and engagement rate in GA4?

Save to ▾
Show answer
In GA4, an engaged session lasts longer than 10 seconds, has a conversion (key event) or has at least two page or screen views. Engagement rate is engaged sessions divided by total sessions. Bounce rate is simply the opposite: the share of sessions that were not engaged. This is more useful than the old single-page bounce definition, because a user who reads a full blog post for three minutes is no longer counted as a bounce.
389

What are UTM parameters and why do they matter?

Save to ▾
Show answer
UTM parameters are tags added to a URL so analytics tools know where a visit came from. The main ones are utm_source (instagram, newsletter), utm_medium (paid_social, email, cpc), utm_campaign (diwali_sale_2026), plus optional utm_content and utm_term. Without them, traffic from emails, messaging apps and many social links shows up as direct or referral, and you cannot measure which campaign drove sign-ups or sales.
390

What do CTR, CPC, CPA and ROAS mean in digital marketing?

Save to ▾
Show answer
CTR, click-through rate, is clicks divided by impressions. CPC, cost per click, is spend divided by clicks. CPA, cost per acquisition, is spend divided by conversions such as orders or leads. ROAS, return on ad spend, is revenue from ads divided by ad spend, so a ROAS of 4 means 4 rupees of revenue per rupee spent. ROAS ignores product margin, so a high ROAS can still lose money.
391

Write a UTM-tagged URL for a paid Instagram campaign promoting a summer sale, and give two naming rules.

Save to ▾
Show answer
https://www.example.com/sale?utm_source=instagram&utm_medium=paid_social&utm_campaign=summer_sale_2026&utm_content=reel_video_a -- Rule 1: always lower case with underscores; 'Instagram' and 'instagram' become separate rows -- Rule 2: keep a shared UTM sheet with allowed source and medium values so every team tags the same way
392

Write SQL to calculate sessions, orders and conversion rate by traffic source.

Save to ▾
Show answer
SELECT s.traffic_source, COUNT(*) AS sessions, COUNT(o.order_id) AS orders, ROUND(100.0 * COUNT(o.order_id) / COUNT(*), 2) AS conversion_pct FROM sessions s LEFT JOIN orders o ON o.session_id = s.session_id GROUP BY s.traffic_source ORDER BY sessions DESC; -- assumes at most one order per session; otherwise use COUNT(DISTINCT o.session_id)
393

Write Excel formulas to calculate ROAS and CPA for each campaign and flag campaigns below a ROAS target.

Save to ▾
Show answer
-- B = Spend, C = Conversions, D = Revenue, target ROAS in H1 E2 (ROAS): =IFERROR(D2/B2, 0) F2 (CPA): =IF(C2=0, "No conversions", B2/C2) G2 (Flag): =IF(E2<$H$1, "Below target", "OK") -- Copy down, then add conditional formatting on G to highlight 'Below target'
394

A campaign landing page gets heavy traffic but very few sign-ups. How would you diagnose it?

Save to ▾
Show answer
Check traffic quality first: is the right audience coming, and does the ad message match the page? Then look at page behaviour by device: load time, scroll depth, form start versus form submit, and which form field users abandon. Session recordings or heatmaps help. Test the form on mobile for errors. Common fixes are faster pages, fewer form fields, a clear call to action above the fold and matching the ad's offer.
395

Explain the common marketing attribution models.

Save to ▾
Show answer
Attribution decides how credit for a conversion is shared across touchpoints. Last click gives all credit to the final touch, first click to the first one. Linear splits credit equally, time decay gives more to recent touches, and position-based gives most to first and last. Data-driven attribution uses an algorithm on your own paths to estimate each channel's contribution. Each model answers a different question, so state which one a report uses.
396

Write SQL to find the first-touch and last-touch channel for every user who converted.

Save to ▾
Show answer
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;
397

Write Python code to extract UTM source, medium and campaign from a column of landing page URLs.

Save to ▾
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)
398

Write SQL to calculate open rate and click-through rate for each email campaign.

Save to ▾
Show answer
SELECT s.campaign_id, COUNT(DISTINCT s.user_id) AS delivered, COUNT(DISTINCT o.user_id) AS opened, COUNT(DISTINCT c.user_id) AS clicked, ROUND(100.0 * COUNT(DISTINCT o.user_id) / COUNT(DISTINCT s.user_id), 1) AS open_rate, ROUND(100.0 * COUNT(DISTINCT c.user_id) / COUNT(DISTINCT s.user_id), 1) AS ctr FROM email_sends s LEFT JOIN email_opens o ON o.campaign_id = s.campaign_id AND o.user_id = s.user_id LEFT JOIN email_clicks c ON c.campaign_id = s.campaign_id AND c.user_id = s.user_id WHERE s.status = 'delivered' GROUP BY s.campaign_id;
399

Write pandas code to calculate customer acquisition cost and payback period in months for each channel.

Save to ▾
Show answer
# spend: channel, spend # customers: channel, customer_id, monthly_margin new_cust = customers.groupby('channel').agg( new_customers=('customer_id', 'nunique'), avg_margin=('monthly_margin', 'mean')) summary = spend.groupby('channel')['spend'].sum().to_frame().join(new_cust) summary['cac'] = summary['spend'] / summary['new_customers'] summary['payback_months'] = summary['cac'] / summary['avg_margin'] print(summary.round(1).sort_values('payback_months'))
400

Organic search traffic fell 40% overnight. How do you investigate?

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

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

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

Analytics shows 1,820 purchases last month but the order database shows 2,150. Which is right, and how do you explain the gap?

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

Site conversion rate rose from 2.0% to 2.4%, but total revenue stayed flat. What would you check?

Save to ▾
Show answer
Check the components: sessions may have fallen, for example after cutting paid campaigns, which removes low-intent traffic and lifts the rate without new sales. Average order value may have dropped because of discounts or a mix shift towards cheaper products. Also check whether conversion definitions or tracking changed. Revenue equals sessions times conversion rate times AOV, so break down the change across those three factors by channel.
404

Email open rates suddenly jumped from 22% to 45% without any campaign change. Should you celebrate?

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

Tell me about a time you worked with a marketing team to improve a campaign using data.

Save to ▾
Show answer
Situation: a lead generation campaign had a high cost per lead and sales said leads were poor. Task: find what to change. Action: I joined ad data with CRM outcomes and found two audience segments produced most leads but almost no closed deals, while one smaller segment converted well. Result: we moved budget to that segment and changed the form questions; cost per qualified lead fell about 30% in six weeks.
406

Tell me about a time tracking broke and you had to fix the reporting.

Save to ▾
Show answer
Situation: after a checkout redesign, purchases in our analytics fell 35% overnight while orders were normal. Task: find the cause and protect the weekly report. Action: I compared analytics with the order table by page and device, found the purchase tag no longer fired on the new UPI payment success page, and worked with developers to fix it. Result: I backfilled the gap from order data, added a footnote and set up a daily mismatch alert.
407

What is the difference between marketing mix modelling and multi-touch attribution?

Save to ▾
Show answer
Multi-touch attribution tracks individual user journeys and assigns credit to clicks and views, but it misses offline channels like TV and outdoor, and it is weakened by cookie loss and privacy rules. Marketing mix modelling uses aggregated weekly data, such as spend per channel, sales, price and seasonality, in a regression to estimate each channel's contribution and diminishing returns. MMM is better for budget planning; MTA is better for quick digital optimisation.
408

What is incrementality testing in marketing?

Save to ▾
Show answer
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.
409

How do cookie restrictions, consent rules and server-side tagging affect web analytics data in 2026?

Save to ▾
Show answer
Browser tracking prevention, ad blockers and consent banners mean many visits are not fully tracked, so analytics undercounts users and conversions and breaks multi-session journeys. Under India's DPDP Act and similar laws, you need valid consent for non-essential tracking. Teams respond with consent mode modelling, first-party and server-side tagging, logged-in user IDs and reconciling analytics with backend orders, and they treat analytics numbers as estimates rather than exact counts.
410

Write SQL to group raw page events into sessions using a 30-minute inactivity rule.

Save to ▾
Show answer
WITH ordered AS ( SELECT user_id, event_ts, LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts) AS prev_ts FROM page_events ), flagged AS ( SELECT *, CASE WHEN prev_ts IS NULL OR event_ts - prev_ts > INTERVAL '30 minutes' THEN 1 ELSE 0 END AS new_session FROM ordered ) SELECT user_id, event_ts, SUM(new_session) OVER (PARTITION BY user_id ORDER BY event_ts) AS session_no FROM flagged;
411

After a website migration, the share of direct traffic jumped from 15% to 45%. What could cause it?

Save to ▾
Show answer
Direct is the default bucket when the source is unknown, so a jump usually means attribution broke. Common causes are redirects that strip UTM parameters, a new referrer policy hiding the referring site, the analytics tag missing on landing pages so sessions start later on the site, payment or login domains not added to the cross-domain or referral exclusion list, and HTTPS to HTTP transitions. Compare landing pages and campaign traffic before and after.
412

Brand keyword ads show a ROAS of 15, and the team wants to double the budget. How do you respond?

Save to ▾
Show answer
Brand search ROAS is usually inflated because people searching the brand name were already looking for you and would often click the free organic result anyway. Before increasing spend, I would propose an incrementality test, such as pausing brand ads in some matched cities or time periods and measuring the drop in total brand-search orders. The incremental ROAS is often far lower, so the extra budget may work better elsewhere.
413

The CMO asks which marketing channel should lose 20% of its budget. How do you approach it?

Save to ▾
Show answer
I would compare channels on incremental return, not last-click ROAS. Gather spend, attributed conversions under two models, customer quality such as repeat rate and margin, and any lift test or mix model results. Look at diminishing returns: the last rupee in a saturated channel is worth less than the average. Then recommend cutting where marginal return is lowest, ideally phased with a holdout to confirm the impact.
414

Describe a time you explained the limits of attribution data to a senior stakeholder.

Save to ▾
Show answer
Situation: a director wanted to cut social ads because last-click reports showed almost no sales. Task: give a fairer view. Action: I showed with simple path examples that social often started journeys that ended in search, compared first-click and data-driven views, and proposed a two-city holdout test. Result: the test showed meaningful incremental sales, so the budget was trimmed modestly instead of cut, and attribution caveats were added to the dashboard.

Frequently asked questions

What is asked in a data analyst interview in India?

Most processes have three to four rounds. A screening test or live round on SQL and Excel, a technical round on a BI tool such as Power BI or Tableau along with Python and statistics basics, a case study or project discussion, and an HR or managerial round. Freshers face more fundamentals and aptitude, while experienced candidates get deeper questions on data modelling, KPI design and stakeholder management.

Is SQL or Python more important for data analyst interviews?

SQL is more important for most data analyst roles in India, because almost every company tests it and analysts use it daily to pull data. Python is a strong advantage and is expected in product companies and analytics teams that automate reports or do deeper analysis. A good plan is to become very comfortable with SQL first, then learn pandas well enough to clean, analyse and visualise data.

Should I learn Power BI or Tableau for data analyst jobs?

Power BI appears in more Indian job postings, especially in companies using Microsoft tools, and it has a free desktop version, so it is a safe first choice. Tableau is popular in consulting, IT services and some global teams. The concepts transfer well: data modelling, calculations, filters and dashboard design. Learn one deeply with a portfolio project, and understand the other well enough to discuss it confidently.

Can a fresher without experience get a data analyst job?

Yes. Many companies hire freshers as data analysts, MIS analysts or analyst interns. Recruiters look for strong Excel and SQL, one BI tool, basic statistics and two or three genuine projects using real-world data, such as sales, HR or public datasets, explained clearly with business impact. Internships, a clean portfolio on a public profile and practice with interview-style SQL problems greatly improve your chances.

What kind of projects should I show in a data analyst interview?

Show projects that answer a business question end to end: data cleaning, analysis in SQL or Python, a dashboard, and clear recommendations. Good examples are a sales performance dashboard, customer churn or cohort analysis, HR attrition analysis, or an A/B test evaluation. Be ready to explain the data source, cleaning decisions, KPI definitions, one surprising insight and what you would do differently. Depth beats having many shallow projects.

How should I prepare for the case study round?

Practise structuring open questions before jumping to numbers. Clarify the goal and definitions, break the problem into drivers such as traffic, conversion and order value, list hypotheses, decide what data would test each, and end with a recommendation. Practise common cases like a sudden sales drop, choosing KPIs for a product, evaluating a campaign and guesstimates. Think aloud, state assumptions and sanity-check your final numbers.