Data analyst interview questions

Model answer. WHERE filters rows before they are grouped; HAVING filters groups after the aggregates are computed. So a condition on a plain column (status = 'completed') goes in WHERE, and a condition on an aggregate (COUNT(*) >= 20) goes in HAVING. Filtering early in WHERE is also cheaper, because fewer rows reach the GROUP BY.

Common wrong answer. "They do the same thing, HAVING is just used with GROUP BY." A HAVING condition on a grouped column still runs, but an aggregate in WHERE is an error, and the interviewer wants to hear why.

Practise: SQL · Our most loyal customersPractise: SQL · Why does this query fail? SQL cheat sheet: Group and count

Model answer. COUNT(*) counts rows. COUNT(column) counts rows where that column is not NULL. COUNT(DISTINCT column) counts the different non-NULL values. On the customers table, COUNT(*) and COUNT(referral_source) differ by the number of customers with no referral source.

SELECT COUNT(*) AS customers,
       COUNT(referral_source) AS with_source,
       COUNT(DISTINCT referral_source) AS sources
FROM customers;

Common wrong answer. "They are the same unless there are duplicates." The NULL behaviour is the point of the question.

Practise: SQL · How many customers came from referrals?

Model answer. An INNER JOIN keeps only rows that match on both sides. A LEFT JOIN keeps every row of the left table; where nothing matches, the right table's columns come back as NULL. Use a LEFT JOIN when the left side must stay complete, for example every customer, including those who never ordered.

Common wrong answer. Drawing a Venn diagram and stopping there. It hides the fact that one left row can match many right rows and come back several times.

Practise: SQL · Revenue by categoryPractise: SQL · Customers who never ordered SQL cheat sheet: Join two tables

Model answer. Logically the query runs FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, ORDER BY and LIMIT. WHERE runs before SELECT, so the alias does not exist yet. ORDER BY runs after SELECT, which is why the alias works there.

Common wrong answer. "Aliases only work in ORDER BY because of a syntax rule." The run order is the actual reason.

Practise: SQL · Why does this query fail?

Model answer. Both stack the rows of two queries with the same number and types of columns. UNION removes duplicate rows from the combined result, which costs a sort or hash; UNION ALL keeps every row. Use UNION ALL unless you need duplicates removed, and say why when you do.

SELECT channel AS source FROM marketing_spend
UNION
SELECT source FROM web_sessions
ORDER BY source;

Common wrong answer. "UNION joins two tables side by side." That is a JOIN; UNION stacks rows.

Model answer. DDL (data definition) changes structure: CREATE, ALTER, DROP. DML (data manipulation) changes rows: INSERT, UPDATE, DELETE. DELETE removes the rows a WHERE clause picks and can be rolled back inside a transaction; TRUNCATE empties the whole table quickly and keeps its structure; DROP removes the table itself, structure included.

Common wrong answer. "DELETE without WHERE is the same as DROP." The table is still there after DELETE, just empty.

Model answer. LEFT JOIN customers to orders and keep the rows where the order side is NULL (an anti-join), or use NOT EXISTS. Test the NULL on the join key or primary key of the right table, which can only be NULL when there was no match.

SELECT c.customer_id, c.email
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

Common wrong answer. WHERE customer_id NOT IN (SELECT customer_id FROM orders). It works here, but returns nothing at all as soon as the subquery holds one NULL.

Practise: SQL · Customers who never orderedPractise: Python · Customers who never ordered

Model answer. A condition on the right table sits in the WHERE clause. For unmatched rows those columns are NULL, the condition is not true, and the rows are dropped, so the LEFT JOIN behaves like an INNER JOIN. Move the condition into the ON clause to filter what gets joined while keeping every left row.

SELECT c.customer_id, COUNT(o.order_id) AS app_orders
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id AND o.channel = 'app'
GROUP BY c.customer_id;

Common wrong answer. "Use a RIGHT JOIN instead" or "add DISTINCT". Neither touches the cause.

Practise: SQL · App orders per business customer

Model answer. Rank prices with DENSE_RANK in descending order and keep rank 2. DENSE_RANK handles ties: if two products share the top price, the next price is still rank 2. Say how you treat ties before you write the query.

WITH ranked AS (
  SELECT name, unit_price,
         DENSE_RANK() OVER (ORDER BY unit_price DESC) AS rnk
  FROM products
)
SELECT name, unit_price
FROM ranked
WHERE rnk = 2;

Common wrong answer. ORDER BY unit_price DESC LIMIT 1 OFFSET 1. With a tie at the top it returns the top price again.

Practise: SQL · ROW_NUMBER vs RANK vs DENSE_RANKPractise: SQL · Page two of the price list

Model answer. All three number rows in the window order. ROW_NUMBER gives every row a unique number, breaking ties arbitrarily unless you add a tie-breaker. RANK gives tied rows the same number and then skips (1, 2, 2, 4). DENSE_RANK gives ties the same number without gaps (1, 2, 2, 3).

Common wrong answer. "They are the same, just different names." Or using ROW_NUMBER for "top 3 prices" when ties should share a place.

Practise: SQL · ROW_NUMBER vs RANK vs DENSE_RANK SQL cheat sheet: Window functions

Model answer. Decide what makes two records the same person, clean that key (LOWER and TRIM on the email), then GROUP BY the key and keep groups with COUNT(*) > 1. Return the ids in each group so someone can decide which record to keep.

SELECT LOWER(TRIM(email)) AS email_key,
       COUNT(*) AS records,
       MIN(customer_id) AS first_id
FROM customers
GROUP BY 1
HAVING COUNT(*) > 1;

Common wrong answer. SELECT DISTINCT *. Duplicates in real data differ in an id, a timestamp or the casing, so DISTINCT removes nothing.

Practise: SQL · Find duplicate customer recordsPractise: Python · Find duplicate customers

Model answer. A CTE (WITH name AS (...)) names an intermediate result you can query like a table. It reads top to bottom, each step can be checked on its own, and it can be referenced more than once in the query. In most engines it performs the same as the equivalent subquery; the gain is readability and fewer mistakes.

Common wrong answer. "CTEs are faster." Sometimes, often not; claiming it without knowing the engine is a red flag.

Practise: SQL · Customer lifetime valuePractise: SQL · Shipping and merchandise revenue per channel SQL cheat sheet: Subqueries and CTEs

Model answer. SUM(value) OVER (ORDER BY date). With ORDER BY in the window and no frame, the default frame runs from the first row to the current one, peers included. Add PARTITION BY to restart the total per group, for example per year.

Common wrong answer. A correlated subquery that re-sums all earlier rows for every row. It works, but it is slow and hard to read.

Practise: SQL · Year-to-date revenue

Model answer. OLTP systems run the business: many small reads and writes (an order being placed), normalised tables, current data. OLAP systems serve analysis: large scans and aggregates over history, often a star schema in a data warehouse loaded from the OLTP systems. Analysts usually query the OLAP side so reports do not slow down the shop.

Common wrong answer. Describing them as two vendors or two query languages.

Model answer. Number the rows inside each category with ROW_NUMBER() OVER (PARTITION BY category ORDER BY unit_price DESC), then filter on that number in an outer query. Window functions are computed after WHERE, so the filter has to sit one level up, in a CTE or subquery.

WITH ranked AS (
  SELECT category, name, unit_price,
         ROW_NUMBER() OVER (PARTITION BY category
                            ORDER BY unit_price DESC, product_id) AS rn
  FROM products
)
SELECT category, name, unit_price
FROM ranked
WHERE rn <= 3
ORDER BY category, rn;

Common wrong answer. GROUP BY category with ORDER BY and LIMIT 3, which returns three rows in total, not three per category.

Practise: SQL · Top 3 products in every category

Model answer. Shipping fee is stored once per order, but the join returns one row per order line, so each fee is repeated for every line (a fan-out). Sum order-level columns at the order grain: aggregate the lines to one row per order first, then join, or sum the fee before joining.

WITH merch AS (
  SELECT order_id, SUM(quantity * unit_price) AS merch
  FROM order_items
  GROUP BY order_id
)
SELECT o.channel,
       SUM(o.shipping_fee) AS shipping,
       ROUND(SUM(m.merch), 2) AS merchandise
FROM orders AS o
JOIN merch AS m ON m.order_id = o.order_id
GROUP BY o.channel;

Common wrong answer. SUM(DISTINCT shipping_fee). It also drops different orders that happen to have the same fee, like every 6.95.

Practise: SQL · Lines per order without double countingPractise: SQL · Shipping and merchandise revenue per channel

Model answer. If the subquery returns a NULL, x NOT IN (1, 2, NULL) means x <> 1 AND x <> 2 AND x <> NULL. The last comparison is unknown, so the whole condition is never true. Use NOT EXISTS, or filter NULLs out of the subquery.

SELECT e.employee_id, e.first_name
FROM employees AS e
WHERE NOT EXISTS (
  SELECT 1 FROM employees AS r WHERE r.manager_id = e.employee_id
);

Common wrong answer. "Because of a bug in the database." This is standard three-valued logic.

Practise: SQL · Customers who returned somethingPractise: SQL · The NULL comparison trap

Model answer. Aggregate to one row per month first, then use LAG to bring the previous month onto the same row and compute (this − previous) / previous. Multiply by 100.0, not 100: in SQL Server and PostgreSQL a whole number divided by a whole number is integer division and rounds the growth to zero.

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS month, COUNT(*) AS orders
  FROM orders
  GROUP BY 1
)
SELECT month, orders,
       ROUND(100.0 * (orders - LAG(orders) OVER (ORDER BY month))
             / LAG(orders) OVER (ORDER BY month), 1) AS mom_pct
FROM monthly
ORDER BY month;

Common wrong answer. A self-join on month = month − 1 written on raw dates, which breaks across years and at month ends.

Practise: SQL · Month-over-month growthPractise: SQL · Monthly revenue for 2025

Model answer. loc selects by label: index labels and column names, and accepts boolean masks. iloc selects by integer position. Slices differ too: loc includes the end label, iloc excludes the end position like a Python slice.

Common wrong answer. "loc is for rows and iloc is for columns." Both take rows and columns.

Practise: Python · Rows by position, columns by name

Model answer. Single brackets with one name return a Series (one column, with the index). A list of names returns a DataFrame, even when the list has one name. It matters because methods and merges behave differently on each.

Common wrong answer. "Both return the column." They return different types.

Practise: Python · Series or DataFrame?Practise: Python · Pick the columns you need

Model answer. Python's and asks each side for a single True/False, and a Series of many booleans has no single truth value. Use the element-wise operators & | ~ and put each comparison in brackets, because & binds tighter than >.

products[(products["category"] == "Decor") & (products["unit_price"] < 50)]

Common wrong answer. "Use and with brackets." Brackets alone do not fix it; the operator must be &.

Practise: Python · Combine conditions with & and |

Model answer. A line chart for change over time; a sorted horizontal bar chart for comparing categories; a histogram or box plot for a distribution; a scatter plot (with a trend line if useful) for two numeric variables. In Python: plt.plot, sns.barplot or df.plot.barh, sns.histplot or sns.boxplot, sns.scatterplot or sns.regplot.

monthly = orders.groupby(orders["order_date"].dt.to_period("M")).size()
monthly.tail(12)

Common wrong answer. A pie chart with many slices, or a line chart across categories that have no order.

Model answer. size() counts rows in each group, missing values included, and returns one Series. count() counts non-missing values per column, so it can differ column by column. It mirrors COUNT(*) against COUNT(column) in SQL.

g = customers.groupby("segment")
pd.DataFrame({"rows": g.size(), "with_source": g["referral_source"].count()})

Common wrong answer. "size is the number of columns." That is .shape[1] on a DataFrame.

Practise: Python · Average shipping fee per channel

Model answer. merge joins two DataFrames on key columns, like a SQL join (how="left", "inner" …). join is a shortcut for joining on the index. concat stacks frames on top of each other (rows) or side by side (columns) without matching keys.

Common wrong answer. Using concat with axis=1 to combine two tables, which pairs rows by index position or label and silently misaligns them.

Practise: Python · Add product names to order linesPractise: Python · Revenue by category, completed orders only pandas cheat sheet: Join two tables (merge)

Model answer. First measure them (isna().sum()) and find out why they are missing. Then decide per column: drop rows when they are few and random, fill with a label for categories ("unknown"), leave them as NaN when an average should skip them, and say what you did in the write-up. Filling with 0 changes means and totals.

customers.isna().sum()

Common wrong answer. "df.dropna()" on the whole frame. It drops every row with any missing value, often most of the data.

Practise: Python · Where do customers come from?Practise: Python · What does mean() do with missing costs?

Model answer. duplicated(subset=...) flags repeats of a key, and drop_duplicates(subset=..., keep="first") removes them. Clean the key first (strip, lower) so near-duplicates are caught, and look at the flagged rows before dropping anything.

key = customers["email"].str.strip().str.lower()
customers[key.duplicated(keep=False)]

Common wrong answer. df.drop_duplicates() with no subset, which only removes rows identical in every column, so near-duplicates stay.

Practise: Python · Find duplicate customers

Model answer. apply calls the Python function once per row. Vectorised operations work on the whole column in compiled code: arithmetic on columns, np.where for if/else, pd.cut for bands, .str and .dt methods. Reach for apply only when no vectorised form exists.

np.where(products["unit_price"] >= 200, "premium", "standard")

Common wrong answer. "Use a for loop with iterrows." That is slower still.

Practise: Python · Compute line revenuePractise: Python · Tag orders by size

Model answer. You assigned to something that may be a view of another DataFrame, so pandas cannot promise the original changes or that it does not. Select and assign in one step with .loc[rows, column] = value, or take an explicit .copy() when you mean to work on a separate frame.

Common wrong answer. "Ignore it, it is just a warning." Sometimes the assignment did not happen.

pandas cheat sheet: New columns

Model answer. The right table has repeated keys, so each left row matches several right rows. Check that the key is unique on the side you expect (right["key"].is_unique), and pass validate="many_to_one" so the merge raises an error instead of quietly multiplying rows.

lines = order_items.merge(products[["product_id", "category"]],
                         on="product_id", how="left", validate="many_to_one")
len(lines) == len(order_items)

Common wrong answer. "Call drop_duplicates() after the merge." It hides the symptom and can delete real rows.

Practise: Python · Why did shipping revenue double?Practise: Python · Shipping fees on lighting orders

Model answer. A1 is relative: both column and row shift when you copy the formula. $A$1 is absolute and never moves. A$1 locks the row only, $A1 the column only. Mixed references let one formula fill a whole table, for example a rate across the top and items down the side.

Common wrong answer. "The $ is for currency." Or locking everything with $ so the formula cannot be filled.

Practise: Excel · Prices with tax, one rate for every rowPractise: Excel · Line totals with cell references Excel cheat sheet: Formulas, references, fill down

Model answer. XLOOKUP when it is available: exact match by default, looks left or right, has a not-found argument. INDEX/MATCH works in every version and does not break when columns are inserted. VLOOKUP only looks to the right, uses a fixed column number, and defaults to approximate match.

Common wrong answer. "VLOOKUP, it is the standard." Without saying FALSE for exact match.

Practise: Excel · List prices with VLOOKUP and IFERRORPractise: Excel · Product names with XLOOKUP Excel cheat sheet: Join two tables: lookups

Model answer. IFS(test1, value1, test2, value2, …, TRUE, fallback) or nested IFs. Order the tests so the first true one is the right one (for bands, from the lowest bound up), and end with a catch-all. For several conditions at once, put AND() or OR() inside IF.

Common wrong answer. Several separate IF columns added together, which double counts when two tests are true.

Practise: Excel · Shipping fees with IFPractise: Excel · Three shipping bands with IFS

Model answer. SUMIF takes one condition, and the range to add comes last: SUMIF(range, criterion, sum_range). SUMIFS takes one or more conditions, and the range to add comes first: SUMIFS(sum_range, range1, criterion1, …). Many analysts use SUMIFS everywhere so the order never changes.

Common wrong answer. Mixing up the argument order between the two, which sums the wrong column without any error.

Practise: Excel · App orders with COUNTIF and SUMIFPractise: Excel · Several conditions with SUMIFS and COUNTIFS Excel cheat sheet: Group and count: COUNTIFS, SUMIFS

Model answer. Data › Data Validation: allow only a list (a drop-down of channels), whole numbers in a range, dates in a period or a maximum text length, with an input message and a custom error alert. Keep the allowed list in its own range or Excel Table so it can grow, and protect the sheet if needed.

Common wrong answer. "Add a note asking people to be careful." Prevention beats cleaning afterwards.

Model answer. The fourth argument is missing or TRUE, so VLOOKUP does an approximate match: on unsorted data it returns whichever row it lands on. Pass FALSE (or 0) for an exact match. Other causes: keys stored as text on one side and numbers on the other, or trailing spaces.

Common wrong answer. "Excel is buggy, retype the formula."

Practise: Excel · List prices with VLOOKUP and IFERRORPractise: Excel · Product names with XLOOKUP

Model answer. For exploring and summarising: totals by category and month, quick filters, drill-down into the rows behind a number. Formulas (SUMIFS) are better when the summary must sit in a fixed report layout, feed other calculations, or update without a refresh.

Common wrong answer. "Pivot tables are for people who cannot write formulas." Both have a place; say when each fits.

Excel cheat sheet: Shortcuts and menus

Model answer. Numbers stored as text: they sit left-aligned, often with a green triangle, and SUM skips them. Check with ISNUMBER, convert with VALUE, Text to Columns, or multiplying by 1. Also look for stray spaces and non-breaking spaces from exports.

Common wrong answer. "Retype the numbers by hand." It does not scale and hides the cause.

Excel cheat sheet: Clean and split text

Model answer. Add a helper column with =COUNTIF($J$2:$J$41,J2)>1 to flag every copy, or =COUNTIF($J$2:J2,J2)=1 to mark the first occurrence. Conditional formatting shows them visually; Data › Remove Duplicates deletes them, so keep a copy first. Clean the key with TRIM and LOWER before comparing.

Common wrong answer. Sorting and scanning by eye, which misses near-duplicates with different case or spaces.

Excel cheat sheet: Missing values, errors, duplicates

Model answer. A Table grows with new rows, carries formulas and formats down automatically, keeps headers visible, and lets formulas use column names (structured references such as Orders[Quantity]) instead of A1 ranges that go stale. PivotTables, slicers and Power Query read it as one named source.

Common wrong answer. "It just adds colours and banded rows."

Model answer. A calculated column is computed once per row when the data refreshes and stored in the model, like a new column in the table; use it to slice or filter by (a price band). A measure is computed at query time for whatever filters the visual applies (a total, a rate); use it for numbers you aggregate. Most KPIs should be measures.

Common wrong answer. "They are the same, a measure is just a column with SUM." Calculated columns used for totals bloat the model and ignore the visual's filters.

Model answer. Merge joins two queries side by side on a key, like a SQL JOIN or pandas merge (left outer, inner, anti …). Append stacks queries with the same columns on top of each other, like UNION ALL or pd.concat, for example monthly files into one table.

Common wrong answer. Swapping the two, or using Merge to combine twelve monthly files.

Practise: Python · Add product names to order linesPractise: SQL · Revenue by category

Model answer. CALCULATE evaluates an expression in a modified filter context: it adds, replaces or removes filters before computing. For example, CALCULATE([Revenue], Orders[Channel] = "web") gives web revenue in every cell of a visual, and CALCULATE([Revenue], ALL(Products)) ignores product filters to give a total for a share-of-total.

Common wrong answer. "It is like SUMIF." It changes the filter context for any expression, which is why time intelligence and percent-of-total are built on it.

Model answer. One fact table of events with numbers (order lines: quantity, price, keys) in the middle, joined one-to-many to dimension tables that describe them (products, customers, dates). Filters flow from dimensions to the fact table, measures stay simple, and the engine compresses it well. One big flat table or a web of tables both make DAX harder and slower.

Common wrong answer. "Put everything in one table so no relationships are needed."

Practise: SQL · Revenue by categoryPractise: SQL · Lines per order without double counting

Model answer. Time-intelligence functions such as SAMEPERIODLASTYEAR, DATESYTD and TOTALYTD need a continuous calendar with one row per day, marked as the date table and related to the fact table's date. Dates with no sales still need a row, otherwise the previous-year comparison has gaps.

Common wrong answer. "Use the order date column directly." It skips days without orders and breaks time intelligence.

Practise: Casework · Holiday season review in SQLPractise: SQL · Month-over-month growth

Model answer. Row-level security: define a role with a DAX filter on the region dimension (static, or dynamic with USERPRINCIPALNAME() matched to a user-to-region table), publish to the Power BI Service and assign users or groups to the role. Test with "View as" before sharing.

Common wrong answer. "Make a copy of the report per region." It multiplies maintenance and still shares the full dataset.

Model answer. Import loads a compressed copy into the model: fastest visuals and full DAX, but data is only as fresh as the last refresh and size is limited. DirectQuery sends queries to the source each time: near-real-time and no size limit, but slower visuals, more load on the source and some DAX limits. Default to Import; choose DirectQuery when freshness or size demands it.

Common wrong answer. "DirectQuery is always better because it is live."

Model answer. When the data is skewed or has outliers: order values, salaries, time on site. A few very large business orders pull the mean up, while the median shows the typical order. Report both when the gap between them is itself the story.

lines = order_items.assign(rev=order_items["quantity"] * order_items["unit_price"])
order_value = lines.groupby("order_id")["rev"].sum()
order_value.agg(["mean", "median"]).round(2)

Common wrong answer. "The median is more accurate." Neither is more accurate; they answer different questions.

Practise: Casework · Frame the headcount and pay questionPractise: Casework · Check and summarise headcount and pay in SQL

Model answer. Not necessarily. Email reaches people who already know the store, so the audience differs (selection). Correlation shows the two move together; causation needs a comparison where the only difference is the email, ideally a randomised test with a holdout group.

SELECT source,
       ROUND(AVG(CASE WHEN converted THEN 1.0 ELSE 0 END) * 100, 2) AS conversion_pct
FROM web_sessions
GROUP BY source
ORDER BY conversion_pct DESC;

Common wrong answer. "Yes, so we should move the paid budget into email."

Practise: Casework · Cost per conversion in SQLPractise: Casework · Recommend: keep, roll back or extend?

Model answer. Look first (box plot, histogram), then use a rule such as values beyond 1.5 × IQR from the quartiles. Then investigate: a data error gets fixed or removed, a real but rare value (a 100-unit business order) stays, and you may report results with and without it. Never drop outliers silently.

q1, q3 = order_items["quantity"].quantile([0.25, 0.75])
iqr = q3 - q1
order_items[(order_items["quantity"] < q1 - 1.5 * iqr) | (order_items["quantity"] > q3 + 1.5 * iqr)]

Common wrong answer. "Remove anything more than 3 standard deviations away" as an automatic step.

Practise: SQL · Boss: data quality sweepPractise: Python · Boss: the clean 2025 report

Model answer. Assuming there is no real effect (the null hypothesis), the p-value is the probability of seeing a result at least as extreme as the one observed. A small p-value means the data would be surprising if there were no effect. It is not the size of the effect and not the chance the result is real.

Common wrong answer. "The probability that the null hypothesis is true", or "the probability the result happened by chance". Both are the classic mistake.

Practise: Statistics · What p = 0.03 actually saysPractise: Statistics · Test the test with an A/A split

Model answer. If you repeated the sampling many times and built an interval the same way each time, about 95% of those intervals would contain the true value. It expresses how precise the estimate is: a wide interval means you know little.

Common wrong answer. "There is a 95% probability that the true value is inside this interval." The true value is fixed; the interval is what varies.

Practise: Statistics · Boss: how big should the sample be?Practise: Statistics · How sure is the mobile rate?

Model answer. A Type I error is a false positive: you conclude there is an effect when there is none (its rate is alpha, often 5%). A Type II error is a false negative: you miss an effect that is there (its rate is beta; power = 1 − beta). Lowering one usually raises the other unless you collect more data.

Common wrong answer. Swapping the two, or saying a larger sample reduces only Type I errors.

Practise: Statistics · Boss: plan the banner test

Model answer. Pick one primary metric (checkout conversion) and a few guardrails (order value, errors). Decide the smallest effect worth detecting and compute the sample size and duration before starting. Randomise users, not sessions, run full weeks, do not stop early when it looks good, then compare with a significance test and a confidence interval.

Common wrong answer. "Run it until the result is significant." Peeking inflates false positives.

Practise: Excel · Average pages per visit, mobile onlyPractise: Statistics · Boss: how big should the sample be?

Model answer. Standard deviation measures how spread out individual values are. Standard error measures how precise an estimate (usually a mean) is: SD divided by the square root of the sample size. More data shrinks the standard error, not the standard deviation.

Common wrong answer. "They are the same thing", or using SD for error bars on a mean.

Practise: Excel · Same average, different days with STDEV.SPractise: Excel · STDEV.S or STDEV.P?

Model answer. A trend that appears in every group reverses or disappears when the groups are combined, because the groups have different sizes and different base rates. A made-up example: the app converts better than the web on both mobile and desktop, yet worse overall, because most app traffic is mobile, where everything converts less. Always check the mix behind an overall rate.

Common wrong answer. Describing it as "a rare statistical curiosity". It shows up in channel and segment reports all the time.

Practise: Casework · Frame the mobile conversion questionPractise: Casework · Check and build the web funnel in SQL

Model answer. Make it a SMART goal: specific (which product, channel or customers), measurable (which metric and baseline), achievable (based on past growth), relevant (tied to a business priority) and time-bound (by when). For example: grow repeat orders from app customers by 10% this quarter compared with last. Then agree the metric definition before pulling data.

Common wrong answer. Starting to pull every sales table and building charts before the question is pinned down.

Model answer. Revenue, broken into orders × average order value; conversion rate (orders ÷ sessions); new vs returning customers; return or refund rate; and marketing cost per order. Each with the same week last year for context, and one owner per metric. Fewer metrics, clearly defined, beat a wall of charts.

Common wrong answer. Listing twenty metrics with no definitions and no comparison point.

Practise: SQL · Boss: performance snapshotPractise: Casework · Holiday season review in SQL

Model answer. First check the data: is the week complete, did tracking or a pipeline change? Then split revenue into orders × average order value and see which moved. Break the one that moved down by channel, device, category, new vs returning customers, and compare with the same week last year for seasonality. Report the biggest driver with a number, and what you ruled out.

Common wrong answer. Jumping to a cause ("the price change") before checking the data and decomposing the metric.

Practise: Casework · Holiday season review in SQLPractise: Casework · Holiday season review in pandas

Model answer. Group customers into cohorts by the month of their first order, then measure the share who order again within a set window (say 90 days), or in each later month. Only count customers who have had the full window to come back (right-censoring), and compare cohorts rather than one overall number.

Common wrong answer. "Repeat customers divided by all customers", mixing customers who joined last week with customers from three years ago.

Practise: Casework · Customer retention in SQLPractise: Casework · Customer retention in pandas

Model answer. Fewer sessions (a traffic drop) with a better rate can still mean fewer orders; or more orders at a lower average order value (discounts, cheaper mix); or more cancellations and refunds after purchase. Decompose revenue into sessions × conversion × AOV × (1 − refunds) and check which term moved.

Common wrong answer. "The data must be wrong." Check it, but this is a normal mix effect.

Practise: SQL · Cancellation rate by channelPractise: Python · Boss: who converts on the website?

Model answer. Lead with the answer and the number ("Returns cost us $X in Q3, most of it Furniture damaged in transit"), then the one chart that shows it, then what you recommend and how sure you are. Methods and caveats go after, briefly. Use their words: orders and customers, not rows and joins.

Common wrong answer. Walking through the queries in the order you wrote them, ending with the result.

Practise: Python · Boss: where do returns come from?

Model answer. Know the grain of every table you join and check row counts before and after each join. Reconcile one total against a trusted source (finance revenue, a dashboard). Spot-check a few records end to end, look for NULLs and duplicates in the keys, and make sure filters such as status and dates match the question.

Common wrong answer. "I trust the query if it runs without errors."

Practise: SQL · Lines per order without double countingPractise: Python · Boss: where do returns come from?

Model answer. Cost per conversion (spend ÷ converted sessions) per channel over the same period, alongside conversion rate and the value of those orders, so cheap but low-value conversions do not win by default. Aggregate spend and sessions separately before joining them, or the join multiplies spend.

WITH spend AS (
  SELECT channel, SUM(amount) AS spend
  FROM marketing_spend
  GROUP BY channel
), conv AS (
  SELECT source AS channel, COUNT(*) FILTER (WHERE converted) AS conversions
  FROM web_sessions
  GROUP BY source
)
SELECT s.channel, s.spend, c.conversions,
       ROUND(s.spend / NULLIF(c.conversions, 0), 2) AS cost_per_conversion
FROM spend AS s
LEFT JOIN conv AS c ON c.channel = s.channel;

Common wrong answer. Ranking channels by total conversions, which rewards the channel with the biggest budget.

Practise: Casework · Cost per conversion in SQLPractise: Casework · Cost per conversion in pandas

Model answer. Calendars shift: Black Friday falls on a different date, and the period may not have the same number of weekends. Align by event (Black Friday to Cyber Monday) or by weekday, make sure both years are complete, and keep status rules the same (completed orders only, or net of refunds) in both years.

Common wrong answer. Comparing 1–30 November across years without checking where Black Friday falls.

Practise: Casework · Holiday season review in SQLPractise: Casework · Holiday season review in pandas