Data Analyst interview prep

Short answer: A window function computes an aggregate over a "window" of rows but does not collapse them — every source row survives and gets its own value. GROUP BY instead folds each group into a single row.

In depth:

1. GROUP BY — N rows in a group → 1 result row. Detail is lost.
2. Window function — N rows stay N rows, with an aggregate (sum, rank, average, lag) added alongside.
3. Syntax — func() OVER (PARTITION BY ... ORDER BY ...). PARTITION BY defines the groups, ORDER BY sets order within the window (needed for running totals and ranks).

-- Running total of sales per user
SELECT
  user_id,
  order_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY user_id
    ORDER BY order_date
  ) AS running_total
FROM orders;

⚠️ Common mistake: trying to filter on a window function's result in WHERE — windows are evaluated after WHERE/GROUP BY, so wrap the query in a CTE/subquery and filter on the outside.

Short answer: The mean is the sum divided by the count and is sensitive to outliers. The median is the middle value of the ordered data and is robust to outliers. The mode is the most frequent value. For skewed data the median better reflects the "typical" observation.

In depth:

1. Mean — good for symmetric data and downstream math (variance, regression), but a single outlier shifts it.
2. Median — the 50th percentile; splits the data in half and barely reacts to extremes.
3. Mode — the only one of the three that works for categorical data ("most common plan").

⚠️ Common mistake: reporting the mean for income or latency. A long right tail drags the mean upward, so it no longer describes the typical user — use the median.

Short answer: An A/B test is a randomized experiment: users are split at random into control and treatment, shown different versions, and compared on a single pre-chosen primary metric under a fixed decision rule.

In depth:

1. Hypothesis — what you change, which metric it should move, and why.
2. Metric and MDE — one primary metric plus a minimum detectable effect (MDE).
3. Randomization — a random, stable split into control/treatment.
4. Sample size and duration — compute and fix them before launch.
5. Run to N — run until the sample is reached, without peeking at interim results.
6. Analysis — compare the metric, compute the p-value and the confidence interval of the lift.
7. Decision — ship or roll back per the pre-defined rule.

Hypothesis → Randomize → Run to N → Analyze → Decision
              │                                  │
       control / treatment              ship ✓ / rollback ✗

⚠️ Common mistake: changing the UI mid-test or stopping the moment it first looks 'significant' — both break the conclusions.

Short answer: A good metric is measurable, comparable, understandable and hard to game, and above all actionable: it can drive a decision. A KPI is the same kind of metric but tied to a goal and a target value for a period; a KPI is a subset of metrics, not a synonym.

In depth:

1. Actionable, not vanity — a metric should change someone's decision. If the number goes up but nobody knows what to do, it's a vanity metric.
2. Comparability — prefer rates and ratios (conversion, retention %) over raw counts: they compare across periods and segments.
3. Hard to game — ask "how could this be inflated without creating value?". "Clicks" are easy to game; "activated users" are harder.
4. A precise definition — one numerator, one denominator, a fixed window and filters, or two teams will count it differently.

⚠️ Common mistake: calling everything a KPI. KPIs are the 3–5 metrics a team is actually judged on; the rest are diagnostic metrics.

Short answer: The chart type is dictated not by taste but by the kind of relationship in the data: comparing categories — bars, change over time — a line, correlation between two variables — a scatter plot, share of a few parts in a whole — a pie. When torn between a pie and anything else, almost always pick bars.

In depth:

First state which relationship you are showing, and only then choose the visual:

• Bars — the workhorse for categories; the Y axis starts at zero, otherwise the height difference lies.
• Line — only for an ordered continuous axis (time, days); never connect unrelated categories with a line.
• Scatter — for "are X and Y related"; add a trend line if needed.

⚠️ Common mistake: a pie chart with 8 slices — the shares become indistinguishable; replace it with horizontal bars sorted by value.