Data Analyst interview questions

Try each question out loud before opening the answer. Shared questions (tagged by skill) also appear on other roles that use the same skill.

Concepts

1. Beginner · PythonWhen would you use a dictionary instead of a list in Python?

   Use a dictionary when you look things up by a key (customer id to name, word to count); lookups are constant time on average. Use a list for ordered collections you iterate or index by position. Counting word frequencies with a list is slow and awkward; with a dictionary (or collections.Counter) it is one pass.

2. Beginner · SQLWhat is the difference between WHERE and HAVING?

   WHERE filters rows before grouping; HAVING filters groups after aggregation. So WHERE fare_amount > 0 removes refunds before you count, while HAVING count(*) > 100000 keeps only busy zones after counting. Aggregates such as count(*) cannot appear in WHERE. Filtering in WHERE when you can is also faster, because fewer rows reach the grouping step.

3. Beginner · SQLWhat is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?

   COUNT(*) counts rows. COUNT(column) counts non-null values in that column. COUNT(DISTINCT column) counts unique non-null values. In the January 2025 NYC taxi data, COUNT(*) is 3,475,226 but COUNT(passenger_count) is 540,149 lower, because those rows have no passenger count. Mention NULLs explicitly: it is what the interviewer is checking.

4. Beginner · SQLWhen can a LEFT JOIN return more rows than the left table has?

   When the right table has more than one match for a key. Each left row is repeated once per match. Check the key is unique on the right side (count(*) versus count(distinct key)) and compare row counts before and after the join. Duplicating a single zone in a lookup table, for example, inflated a 3.48 million row taxi join by about 170,000 rows.

5. Beginner · SQLYou wrote a LEFT JOIN but the result looks like an INNER JOIN. What is the likely cause?

   A condition on the right table in the WHERE clause, such as WHERE c.country = 'India'. Unmatched rows have NULL there, so the filter removes them. Move the condition into the ON clause to keep unmatched left rows, or keep it in WHERE deliberately if you really want only matches.

6. Beginner · ExcelWhy might VLOOKUP return a wrong value or

   Wrong values usually come from the default approximate match (no FALSE as the last argument) on unsorted data. #N/A usually means the keys differ invisibly: text versus number, or trailing spaces. XLOOKUP defaults to exact match, can look left, does not break when columns are inserted, and has a built-in "not found" value. Check keys with TRIM, VALUE and =A2=B2.

7. Beginner · ExcelHow do you count distinct customers in an Excel pivot table?

   Tick "Add this data to the Data Model" when creating the pivot, then set the value field to Distinct Count. Without the Data Model, pivots offer only Count, which counts rows. Alternatively use =COUNTA(UNIQUE(range)) in Excel 365.

8. Beginner · Data modelling, Power BIWhy does Power BI work best with a star schema?

   Filters flow from dimension tables to fact tables along one-to-many relationships, and DAX, time intelligence and the storage engine are optimised for that shape. A star keeps facts narrow (keys and numbers) and puts descriptions in small dimensions, which compresses well and avoids ambiguous filter paths. A single flat table works for a demo but breaks with a second fact table or date comparisons.

9. Beginner · Power BIWhat is the difference between a calculated column and a measure?

   A calculated column is computed per row at refresh and stored, so it uses memory and does not react to slicers. A measure is computed at query time in the current filter context, so it changes with every visual and slicer. Use columns for things you filter or relate by (a date key, a category); use measures for anything you aggregate (revenue, share of total).

10. Beginner · TableauIn Tableau, what is the difference between dimensions and measures, and between discrete and continuous?

    Dimensions are categorical fields that split the view; measures are numbers that get aggregated. Discrete fields (blue) create headers; continuous fields (green) create axes. A date can be either: discrete month headers for a table, a continuous month axis for a trend line that handles gaps properly.

11. Beginner · StatisticsWhen should you report the median instead of the mean?

    When the data is skewed or has outliers: incomes, order values, delivery times, fares. In January 2025 NYC taxi data the mean fare was 17.08 but the median 12.11, and one erroneous fare of 863,372 inflated the standard deviation to 463. Report the median with percentiles (for example the middle half), and state how many extreme values you excluded.

12. Beginner · Data visualisation and storytellingWhich chart would you use to show monthly revenue by region, and what would you avoid?

    A line chart for the trend, one line per region if there are few, or small multiples if there are many. Highlight the region that matters in colour and grey the rest; label lines directly instead of a legend. Avoid pie charts, 3D effects, dual axes and truncated bar axes. Mark partial months so a drop is not misread.

13. Beginner · Data cleaning and qualityHow do you ensure the data in your reports is accurate?

    Check completeness, validity, consistency, uniqueness and timeliness, and decide for each problem whether to fix, exclude, flag or escalate, with counts documented. Reconcile totals with a trusted source. Then automate: data tests on every refresh with thresholds, and alerts that reach a person. Give a real example, such as taxi totals that did not equal the sum of their parts, which you escalated instead of patching.

14. Intermediate · StatisticsExplain a p-value to a non-technical stakeholder.

    "If there were really no difference, we would see a gap at least this big only about 3 times in 100." It is not the probability that the result is true, and it says nothing about how large or important the effect is. Always pair it with the size of the difference and a confidence interval.

15. Intermediate · SQLExplain ROW_NUMBER, RANK and DENSE_RANK.

    For values 10, 9, 9, 7 ordered descending: row_number gives 1, 2, 3, 4 (always unique); rank gives 1, 2, 2, 4 (ties share, then a gap); dense_rank gives 1, 2, 2, 3 (ties share, no gap). Use row_number for "exactly one row per group", dense_rank for "Nth distinct value".

16. Intermediate · Power BIExplain filter context and what CALCULATE does.

    Filter context is the set of filters applied when a measure is evaluated: from rows and columns of the visual, slicers and page or report filters. CALCULATE evaluates an expression in a modified filter context: a column filter replaces the existing filter on that column, ALL removes filters and KEEPFILTERS intersects. Example: CALCULATE([Revenue], Customer[Country] = "United Kingdom") shows UK revenue on every row, even the Netherlands row.

17. Intermediate · TableauWhat does a FIXED level of detail expression do, and how does it interact with filters?

    { FIXED [Customer ID] : MIN([Order Date]) } computes a value per customer regardless of the dimensions in the view, for example each customer's first order date for cohort analysis. FIXED is computed before dimension filters, so a normal quick filter does not change it; add the filter to context if it should. Data source and context filters do apply.

18. Intermediate · StatisticsWhat does a 95% confidence interval mean?

    It is a range produced by a method that captures the true value in 95% of repeated samples. In practice: "the conversion rate is 14.9%, and plausible values run from 14.5% to 15.3%". Wider intervals mean less certainty, usually from smaller samples. It does not mean there is a 95% probability the true value is in this particular interval, though that loose reading is usually acceptable in a business conversation.

19. Intermediate · pandasWhat is the SettingWithCopyWarning and how do you avoid it?

    It warns that you may be modifying a copy of a DataFrame slice, so the change may not reach the original, as in df[df.qty > 0]["revenue"] = .... Use .loc[mask, "revenue"] = ... on the original, or build a new DataFrame explicitly with .assign() or .copy() and work on that. Recent pandas versions with copy-on-write make the behaviour consistent, but explicit code is still clearer.

20. Advanced · SQLA dashboard query takes two minutes. How do you speed it up?

    Read the plan with EXPLAIN ANALYZE. Look for full scans of large tables, non-sargable filters (functions on indexed columns), joins on unindexed keys and row estimates far from reality. Fixes: filter early, select only needed columns, add or fix indexes, rewrite date filters as ranges, pre-aggregate into a summary table, and on warehouses use partitioning and clustering to cut bytes scanned. Measure before and after.

21. Advanced · StatisticsHow do you interpret a coefficient in a multiple linear regression?

    It is the expected change in the outcome for a one-unit change in that variable, holding the other variables in the model constant. Convert units: in the UCI bike data, temperature is normalised, so a coefficient of 5,108 means about 125 more daily rentals per degree Celsius. It describes association, not cause, unless the design supports causal claims, and it can change when correlated variables are added.

Coding

1. Intermediate · SQLReturn the second highest salary in each department. How do you handle ties?

   select department, salary
   from (
     select department, salary,
            dense_rank() over (partition by department order by salary desc) as r
     from employees
   ) ranked
   where r = 2;

   dense_rank treats equal salaries as one rank, so "second highest" means the second distinct value. row_number would pick an arbitrary row among ties, and rank would skip rank 2 if two people tie for first. Say which interpretation you chose.

2. Intermediate · SQLWrite a query for daily revenue and a running total per month.

   with daily as (
     select cast(ordered_at as date) as day, sum(amount) as revenue
     from orders group by 1
   )
   select day, revenue,
          sum(revenue) over (partition by date_trunc('month', day) order by day
                             rows between unbounded preceding and current row) as mtd
   from daily order by day;

   Mention missing days: generate a calendar and left join if the report needs every date.

3. Intermediate · SQLHow would you compute week-over-week growth in daily active users?

   Aggregate to one row per day, then compare with the same weekday a week earlier using lag(dau, 7) over (order by day), and divide with nullif to avoid division by zero. Comparing with the previous day mixes weekday and weekend patterns; with daily data, same-weekday comparison is usually the honest one. Check for missing days first, or lag compares the wrong dates.

4. Intermediate · SQLDescribe how you would build a monthly cohort retention table in SQL.

   1. One row per customer per active month (date_trunc('month', order_date), grouped).

   2. Each customer's first month (min(month)), their cohort.

   3. Join, compute months since the cohort month, and count distinct customers per cohort and offset.

   4. Divide by the month 0 count for retention. Mention edge effects: the last cohorts have had less time, and customers active before the data starts are misassigned (left censoring).

5. Intermediate · Power BIWrite a DAX measure for each country's share of total revenue.

   Share of Total =
   DIVIDE ( [Revenue], CALCULATE ( [Revenue], ALL ( Customer[Country] ) ) )

   ALL(Customer[Country]) removes only the country filter, so date and product slicers still apply. ALL(Sales) would remove every filter and change the meaning. DIVIDE returns blank instead of an error when the denominator is zero.

6. Intermediate · Power BIHow do you calculate year-over-year growth in DAX, and what can go wrong?

   Revenue LY = CALCULATE ( [Revenue], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
   YoY % = DIVIDE ( [Revenue] - [Revenue LY], [Revenue LY] )

   It needs a marked date table with contiguous dates related to the fact table. Common problems: using the fact table's date column, comparing a partial current month with a full month last year, and a blank prior year when the data starts mid-year.

7. Intermediate · pandasCompute each customer's number of orders and total revenue, then attach their country, in pandas.

   per_customer = (lines.groupby("customer_id")
                        .agg(orders=("invoice", "nunique"), revenue=("revenue", "sum"))
                        .reset_index())
   result = per_customer.merge(customers[["customer_id", "country"]],
                               on="customer_id", how="left", validate="many_to_one")

   Count invoices with nunique, not lines. validate makes pandas raise an error if customers has duplicate ids instead of silently multiplying rows.

Case studies

1. Beginner · pandasYou receive a new CSV for a take-home task. What do you check before any analysis?

   Shape, column types (dtypes), a sample of rows, missing values per column, duplicates, value ranges (negative quantities, impossible dates) and key uniqueness. Read ids as strings to keep prefixes and leading zeros. Write down every exclusion with row counts. Then check the extremes: the largest values often reveal errors, like a cancelled 80,995-unit order in the UCI Online Retail data.

2. Intermediate · StatisticsHow do you decide how long to run an A/B test?

   Before starting, choose the primary metric, the baseline rate, the minimum detectable effect, the significance level (often 5%) and power (often 80%), and compute the sample size per group. Divide by daily eligible traffic, then round up to at least one full week to cover weekly cycles. Do not stop early when the p-value dips below 0.05; peeking inflates false positives.

3. Intermediate · ExcelYou rebuild the same Excel report from a raw export every month. How would you automate it?

   Use Power Query: connect to a folder, record the cleaning steps once, load to the Data Model and build the pivots on it. Next month, drop the new file in the folder and refresh. Add a check (row count or total that must match the source) and put the folder path in a parameter so a colleague can refresh it too.

4. Intermediate · StatisticsCustomers contacted on mobile converted more than those called on landlines. Should we call only mobiles?

   Not on this evidence alone. The data is observational: the bank chose who to call and how. Landline customers were also older on average, so age or other differences may explain the gap. A significance test shows the gap is unlikely to be noise, not that the channel caused it. To decide, run a randomised test of channel, or at least compare within age bands and control for other factors.

5. Intermediate · Business acumen, StatisticsAn A/B test shows a 3% lift in conversion. Would you ship it?

   First check the test is trustworthy: pre-registered metric and sample size, full weekly cycle, sample ratio as planned, no bugs in assignment. Then read the effect: is 3% absolute or relative, and what is the confidence interval? Check guardrail metrics (refunds, latency, support contacts) and segments for harm. Finally, is the lift worth the cost and risk of the change? Then recommend.

6. Intermediate · Data visualisation and storytelling, Stakeholder managementHow do you design a dashboard that people actually use?

   Start from the user and the decision: who opens it, how often, and what they decide. Pick three to five KPIs, each with a comparison. Lay out KPIs at the top, then the main trend, then breakdowns. Keep filters to what users think in. Keep load time under a few seconds, show a "data as of" time and link definitions. Test with a real user before sharing widely, and watch where they hesitate.

7. Intermediate · Data cleaning and quality, Stakeholder managementFinance says your revenue number is 4% higher than theirs. What do you do?

   Treat it as a definitions problem until proven otherwise. Compare definitions (gross or net, cancellations, returns, taxes, currency, date basis such as order date versus invoice date) and time zones. Reconcile on a small slice, one day or one region, line by line. Document the differences, agree a single definition with finance, and label the other clearly if both are needed.

8. Intermediate · Business acumenSales dropped 12% last week. How do you investigate?

   Clarify the metric, comparison and segment first. Check the data (tracking, pipeline delays, definition changes). Decompose revenue into customers, orders per customer and order value, and by region, channel and platform to find where the drop concentrates. Then test internal causes (releases, pricing, stock-outs, marketing) and external ones (seasonality, holidays, competitors). Give an answer with confidence and a next step.

9. Intermediate · Business acumenHow would you measure the success of a new "reorder" button in a grocery app?

   Primary metric: reorder rate among eligible users (users with a past order) per week. Supporting: time to checkout, orders per user, share of orders placed via the button. Guardrails: average order value (the button may reduce discovery), cancellations and support contacts. Define each precisely and measure with a randomised test if possible.

10. Advanced · Power BIHow would you make sure each regional manager sees only their region's data?

    Dynamic row-level security: a mapping table of user email to region, a role whose filter on the region dimension keeps rows where the mapping matches USERPRINCIPALNAME(), and security groups assigned to the role in the service. Test with "View as" for several users, including someone with two regions and someone with none. Share through an app or Viewer access, because workspace Members, Contributors and Admins bypass RLS.

Behavioural

1. Beginner · Communication, Data cleaning and qualityTell me about a mistake you made with data and what you did.

   Pick a real, modest mistake. Explain how you found it, how quickly you told the people affected, how you fixed it, and what check you added so it cannot happen again. Interviewers look for ownership and prevention, not perfection.

2. Intermediate · CommunicationHow do you explain a technical finding to a non-technical audience?

   Lead with the decision or finding, use one number with context, and replace method with an analogy ("we scored customers on how recently, how often and how much they buy"). Show one clear chart titled with the takeaway. Keep methods in an appendix and check understanding by asking what they would do next.

3. Intermediate · Communication, Stakeholder managementTell me about a time a stakeholder disagreed with your analysis.

   Use situation, task, action, result. Show that you first understood their view (often a different definition or context), checked your own work, found the source of the difference together and agreed on the next step. End with the outcome and what you changed in how you work, for example writing definitions into every brief.

4. Advanced · Stakeholder managementThree teams need analysis from you this week. How do you prioritise?

   Ask each team what decision depends on the work and by when. Rank by impact and deadline, share the proposed order with the requesters and your manager, and make trade-offs explicit ("I can do A and B this week, C next Tuesday"). Offer smaller versions where a rough answer is enough. Agree it in writing so nobody is surprised.