Autocoder

Data Analyst Interview Questions and Answers

Last updated 2026-11-19

A data analyst interview usually has four parts: SQL on a few tables, a spreadsheet or Python data-cleaning task, a statistics or metrics discussion, and a case where you explain a number going up or down. The questions below follow that pattern and each answer is worked through with real numbers or runnable code.

Read each question, write your own answer first, then compare. Interviewers care less about the final query than about how you check the data, state assumptions and explain the result to a non-technical person. For the full process and rounds, see how to crack the data analyst interview.

Typical roundsSQL test, case or metrics round, tools round, managerial or HR
Core skillsSQL, Excel, pandas, statistics, visualisation
Questions here14 worked questions

What gets tested most

How to answer a metrics case

Start by confirming the definition of the metric and the time window. Then split it: a revenue number is visits times conversion times average order value, and each part can be cut by channel, device, region or product. Check data quality and recent releases before you propose business causes. End with what you would do next and what would change your mind.

Data Analyst Interview Questions and Answers: questions with worked answers

  1. 1

    What is the difference between WHERE and HAVING?

    easy

    WHERE filters individual rows before grouping and cannot use aggregate functions. HAVING filters groups after GROUP BY and can use aggregates. The query below keeps only orders above 500 (row filter), then keeps only customers whose total is above 5,000 (group filter). Putting a row-level condition in WHERE is also faster, because fewer rows reach the aggregation.

    sql
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    WHERE amount > 500
    GROUP BY customer_id
    HAVING SUM(amount) > 5000;
  2. 2

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

    easy

    COUNT(*) counts all rows, including rows with NULLs. COUNT(column) counts rows where that column is not NULL. COUNT(DISTINCT column) counts unique non-NULL values. For a column holding 10, 10, NULL, 20 you get 4, 3 and 2 respectively. Mixing these up is a classic cause of wrong customer counts after a join.

  3. 3

    INNER JOIN versus LEFT JOIN: how would you find customers who never placed an order?

    easy

    INNER JOIN keeps only matching rows. LEFT JOIN keeps every row from the left table and fills the right side with NULL where nothing matches. To find customers with no orders, left join and keep rows where the right key is NULL. This is called an anti-join. NOT EXISTS gives the same result and is safe when the right column can itself contain NULLs.

    sql
    SELECT c.customer_id, c.name
    FROM customers c
    LEFT JOIN orders o ON o.customer_id = c.customer_id
    WHERE o.order_id IS NULL;
  4. 4

    Write a query for the second highest salary in each department.

    medium

    Use DENSE_RANK over a partition by department, ordered by salary descending, then keep rank 2. DENSE_RANK is the right choice because two people on the top salary should not push the second distinct salary to rank 3, which is what RANK would do. ROW_NUMBER would wrongly pick one of two tied people. If a department has only one distinct salary it returns no row, which you should mention.

    sql
    SELECT dept, name, salary
    FROM (
      SELECT dept, name, salary,
             DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rk
      FROM employees
    ) t
    WHERE rk = 2;
  5. 5

    How do you compute month-over-month revenue growth in SQL?

    medium

    Aggregate revenue to one row per month, then use LAG to bring in the previous month and compute (current minus previous) divided by previous. If revenue goes from 200 to 230 the growth is 30 / 200 = 15 percent. The first month has no previous value, so LAG returns NULL and the growth is NULL, which is correct. Multiply by 100.0 to avoid integer division.

    sql
    SELECT month, revenue,
           ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
                 / LAG(revenue) OVER (ORDER BY month), 1) AS mom_pct
    FROM monthly_revenue;
  6. 6

    Mean or median: which would you report for salaries, and why?

    easy

    Median, when the data is skewed. Take salaries of 30, 32, 35, 38 and 300 thousand. The sum is 435 so the mean is 87, a figure that describes nobody in the group. The median is the middle value, 35. One extreme value drags the mean but not the median. Report the mean when the distribution is roughly symmetric and you need totals, and show both when they differ a lot, because the gap itself says the data is skewed.

  7. 7

    How do you detect and treat outliers?

    medium

    First check whether the value is an error or a real extreme. A common rule is the IQR fence. If Q1 is 10 and Q3 is 18, then IQR = 8, the lower fence is 10 - 1.5 x 8 = -2 and the upper fence is 18 + 1.5 x 8 = 30, so any value above 30 is flagged. Then decide: fix a data-entry error, cap (winsorise) the value, analyse with and without it, or keep it if it is genuine, for example a real bulk order. Never delete silently; record what you did.

    python
    q1, q3 = df['amount'].quantile([0.25, 0.75])
    iqr = q3 - q1
    mask = (df['amount'] < q1 - 1.5 * iqr) | (df['amount'] > q3 + 1.5 * iqr)
    outliers = df[mask]
  8. 8

    Walk through cleaning a messy dataset in pandas.

    medium

    Inspect first: shape, dtypes, null counts and duplicates. Then fix types, standardise text, handle nulls with a stated rule and drop exact duplicates. Decide on nulls by column: drop rows if few and random, fill a numeric column with the median if it is skewed, or fill a category with "Unknown". Validate at the end with simple assertions, such as no negative prices and unique order ids.

    python
    import pandas as pd
    df = pd.read_csv('orders.csv')
    print(df.info(), df.isna().sum(), df.duplicated().sum())
    
    df['order_date'] = pd.to_datetime(df['order_date'], errors='coerce')
    df['city'] = df['city'].str.strip().str.title()
    df['amount'] = df['amount'].fillna(df['amount'].median())
    df = df.drop_duplicates()
    assert df['order_id'].is_unique and (df['amount'] >= 0).all()
  9. 9

    Explain the difference between correlation and causation with an example.

    easy

    Correlation measures how two variables move together, from -1 to +1. Causation means a change in one produces a change in the other. Ice-cream sales and drowning incidents are positively correlated because both rise in summer: temperature is a confounder. To argue causation you need a randomised experiment, or a careful design that controls for confounders. In analysis reports, say "is associated with" unless you have run an experiment.

  10. 10

    A conversion funnel has 10,000 visits, 800 sign-ups and 200 paid users. Compute the rates and say where you would look.

    easy

    Visit to sign-up is 800 / 10,000 = 8 percent. Sign-up to paid is 200 / 800 = 25 percent. Overall visit to paid is 200 / 10,000 = 2 percent, which equals 0.08 x 0.25. The biggest relative drop is at the first step, where 92 percent leave, so test landing page and traffic quality there, but a one-point gain at the second step may be cheaper. Segment by channel before concluding, since a low-intent channel can drag the first step down.

  11. 11

    Calculate a 7-day moving average of daily sales in SQL and in pandas.

    medium

    In SQL use a window frame of the current row and the six rows before it. In pandas use rolling with a window of 7. For both, the first six days use fewer than seven values, so state whether you want partial windows or NULL. Make sure there is exactly one row per day first, otherwise a row-based window will not equal seven calendar days.

    sql
    SELECT sale_date, sales,
           AVG(sales) OVER (ORDER BY sale_date
                            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
    FROM daily_sales;
    python
    daily = daily.set_index('sale_date').asfreq('D')
    daily['ma7'] = daily['sales'].rolling(7, min_periods=7).mean()
  12. 12

    Orders dropped 20 percent last week. How would you investigate?

    hard

    Confirm it is real: check the data pipeline, a tracking change or a late-loading table. Then decompose orders = visits x conversion x orders per buyer, and see which part moved. Cut the drop by channel, device, city and product to find where it is concentrated, and compare with the same week last year to remove seasonality. Check releases, price changes, stock-outs, payment failures and competitor or marketing changes. Report the likely cause, your confidence and the next check, not just a list of ideas.

  13. 13

    How do you choose a chart for a given question?

    easy

    Match the chart to the comparison. Use a line for change over time, bars for comparing categories, a histogram for one distribution, a scatter plot for the relationship of two numeric variables and a box plot to compare spread across groups. Avoid pie charts beyond three or four slices, avoid truncated bar axes, and label units. Every chart should have a title that states the takeaway, not just the metric name.

  14. 14

    What is a pivot table and how do you build the same thing in pandas?

    easy

    A pivot table summarises data by putting one field on rows, another on columns and an aggregate in the cells. In pandas use pivot_table. The example gives total sales for each region and product with a grand total row and column, which is what a spreadsheet pivot shows by default.

    python
    pt = df.pivot_table(index='region', columns='product',
                        values='sales', aggfunc='sum',
                        fill_value=0, margins=True)

Frequently asked questions

How much SQL is needed for a data analyst interview?

Expect joins, GROUP BY with HAVING, subqueries, CTEs and window functions such as ROW_NUMBER, RANK and LAG. Practise on the SQL interview guide and the window functions page.

Do data analyst interviews ask for Python?

Many do, mostly pandas for cleaning, grouping and merging, and basic plotting. Some roles use only SQL and Excel or a BI tool, so read the job description.

How should I present a case answer?

State the metric definition, break it into drivers, say what data you would pull, and finish with a recommendation and the risk in it. Keep the structure visible so the interviewer can follow.

What statistics topics come up?

Mean, median, standard deviation, percentiles, correlation, sampling bias and basic hypothesis tests. Deeper material is on the statistics interview page.

Related guides

More in Role-wise Interview Questions by Job and Tech Stack

See all in this section