Data Analysis and Requirements Translation Questions

Focuses on translating ambiguous business questions into concrete, actionable data analysis plans. Candidates should identify what data is needed to answer the question, define the metrics or KPIs that would settle it, state and validate the assumptions behind those definitions, and lay out the concrete analysis steps or queries that would produce an answer. Strong answers connect analysis choices back to the business decision at stake: what would change stakeholder behavior or strategy, what data quality or data availability issues could undermine the conclusion, and what additional data collection, reporting, or systems changes would be needed to answer the question reliably going forward.

EasyTechnical

42 practiced

When estimating Weekly Active Users (WAU) from event data, list the common assumptions analysts make (for example: uniqueness of user_id, event coverage, timezone consistency, bot traffic filtering) and describe concrete validation queries or tests you would run to verify each assumption and acceptable thresholds for each check.

Sample Answer

When estimating WAU from event data, analysts commonly make these assumptions. For each I give a concrete validation query/test and suggested acceptable thresholds.

1) Uniqueness of user_id (one row per user per event source)- Test: count distinct user_id vs total rows and distinct across sessions/devices.

sql

SELECT
  COUNT(*) AS rows,
  COUNT(DISTINCT user_id) AS distinct_users,
  COUNT(DISTINCT user_id, device_id) AS distinct_user_device
FROM events;

- Check: distinct_users/rows ratio should be <<1 (many events per user). If COUNT(DISTINCT user_id, device_id) / COUNT(DISTINCT user_id) >1.2, investigate multi-device inflation. Thresholds: unexpected duplicate user_id formats <0.1% of users.

2) Event coverage (every active user emits at least one tracked event)- Test: compare WAU computed from events to independent sources (auth logs, MAU estimates).

sql

-- WAU from events
WITH wau AS (
 SELECT user_id
 FROM events
 WHERE event_time >= current_date - interval '7 days'
 GROUP BY user_id
)
SELECT COUNT(*) AS wau_events;
-- compare to auth table
SELECT COUNT(DISTINCT user_id) FROM auths WHERE last_login >= current_date - interval '7 days';

- Check: event-based WAU / auth-based WAU between 0.9–1.05. If <0.9, missing instrumentation.

3) Timezone consistency / event_time correctness- Test: distribution of event_time hour, sudden spikes at midnight; check timezone offsets present.

sql

SELECT date_trunc('hour', event_time AT TIME ZONE 'UTC') AS hr, COUNT(*) FROM events
WHERE event_time >= current_date - interval '14 days' GROUP BY hr ORDER BY hr;

- Check: expected diurnal pattern; no repeated exact timestamps cluster >99th percentile unless batch. Threshold: >1% events with timezone null/invalid flags triggers fix.

4) Bot / automated traffic filtering- Test: identify high-frequency user_ids, known bot UA strings, same IP performing many accounts.

sql

SELECT user_id, COUNT(*) AS events, COUNT(DISTINCT session_id) FROM events
WHERE event_time >= current_date - interval '7 days'
GROUP BY user_id HAVING COUNT(*) > 10000;

- Check: users with >p99 events flagged. Acceptable bot rate depends on product; flag if >0.5–1% of users.

5) Deduplication of replayed events / ETL retries- Test: look for identical event_id or same payload within short window.

sql

SELECT event_id, COUNT(*) FROM events GROUP BY event_id HAVING COUNT(*)>1;

- Check: duplicate event_id rate <0.01%. Higher indicates ETL issues.

6) Stable user_id assignment over time (no churny IDs)- Test: count users with user_id reassignments (same device_id linked to multiple user_ids).

sql

SELECT device_id, COUNT(DISTINCT user_id) FROM events GROUP BY device_id HAVING COUNT(DISTINCT user_id) > 1 LIMIT 20;

- Check: proportion of device_ids mapping to multiple user_ids <5% for multi-account apps; if unexpected, investigate.

7) Retention window / inactivity definition correctness- Test: sensitivity analysis: compute WAU with 7-day, 14-day windows and compare growth.

sql

SELECT
 (SELECT COUNT(DISTINCT user_id) FROM events WHERE event_time >= current_date - interval '7 days') AS wau7,
 (SELECT COUNT(DISTINCT user_id) FROM events WHERE event_time >= current_date - interval '14 days') AS wau14;

- Check: wau14 >= wau7; ratio informs expected churn. Use for sanity thresholds per product.

General best practices: run these checks daily, build alerting on thresholds (e.g., sudden >5% drift in WAU week-over-week or any threshold above), and store validation metadata.

MediumTechnical

56 practiced

Design instrumentation and analysis plan for rolling out a feature behind a feature flag across users. Define what to log for exposure and assignment, how to detect contamination across devices or accounts, sample population definition, metrics to monitor (primary and guardrails), rollout cadence, and rollback strategies. Consider multi-environment and cross-device users.

Sample Answer

Situation: You're rolling a feature behind a feature flag and must ensure correct measurement, limit contamination, and safely ramp to production. Below is a data-scientist–oriented instrumentation & analysis plan.

Instrumentation / logging (what to log)- Assignment event (once, deterministic): {event: "flag_assigned", timestamp, env, flag_name, flag_version, bucket_id, treatment, assignment_seed, user_id, account_id, device_id, session_id, source} - Exposure/impression event (every UI render or API hit): {event: "flag_exposed", timestamp, flag_name, flag_version, treatment, user_id, account_id, device_id, session_id, page, response_time} - Action events (business metrics): conversions, clicks, purchases, errors, backend metrics; include same identifiers for joinability. - System telemetry: latency, error codes, CPU/memory, downstream failure rates tagged by flag_version.

Assignment logic & deterministic bucketing- Prefer account_id-based bucketing for cross-device consistency; fall back to user_id, and ultimately device_id if anonymous. Use a stable hash(seed + account_id) → bucket. Record assignment_seed and bucket_id so you can reproduce assignment in analysis.

Preventing and detecting contamination- Define contamination: user/account sees both treatment & control (different devices) or control receives treatment code path. Detect by: - Cross-device logs: group by account_id and check count(distinct treatment) > 1. - Temporal conflicts: exposure timestamps showing both treatments within evaluation window. - Leak-rate metric: fraction of control exposures that received treatment code paths (anomalous >1% triggers alert).- If you use device-level bucketing, measure account-level contamination and report.

Sample population definition- Primary population: active accounts meeting eligibility (e.g., logged-in, region, platform). Specify inclusion/exclusion rules (e.g., exclude internal/test accounts, bots, low-activity accounts). - Define analysis cohorts: intention-to-treat (ITT) by assignment, and per-protocol for those actually exposed. Pre-specify subgroups (platform, geography, new vs returning users).

Metrics to monitor- Primary (business): conversion rate, DAUs/MAUs, revenue per user, feature-specific success metric (e.g., completion rate). - Guardrails (safety/quality): error rate, API latency, crash rate, 500s, abandonment rate, CPU/memory, backend queue lengths. Also monitor retention and unexpected drop in upstream metrics. - Statistical monitoring: rolling uplift estimate (ITT), confidence intervals, and minimum detectable effect checks. Use sequential methods (alpha spending / O’Brien-Fleming) or pre-registered stopping rules.

Rollout cadence & monitoring windows- Canary: dev/stage tests → internal dogfood (5–10 internal users) for functional correctness. - Gradual ramp: 1% (24–48h) → 5% (48–72h) → 25% (72h) → 100% (monitoring for 7 days). Each step has pre-defined wait and checks. For metrics with low frequency, extend windows to capture sufficient events (e.g., weekly). Always wait at least one business cycle (24–72h) to detect diurnal effects.

Decision criteria & rollback strategies- Automatic rollback triggers: guardrail breaches (e.g., error rate + absolute increase > X% and relative >Y%), SLA violations, or critical business metric degradation beyond pre-specified thresholds with statistical significance. - Manual rollback: product or SRE can flip flag or reduce percent to 0% immediately. Keep playbook: who acts, how to notify, how to backfill logs. - Mitigation: isolate by region/platform or disable specific sub-components if partial failure. After rollback, collect logs and perform root-cause analysis.

Multi-environment and cross-device considerations- Namespaces: separate flag namespaces for dev/stage/prod. Tag all logs with env and flag_version. - Account-level identity: unify identity across devices for consistent assignment and analysis. For anonymous flows, map to a temporary persistent id if possible and flag these as lower-confidence. - Reconciliation: when users later link accounts, backfill assigned bucket mapping and treat as “assigned” from first exposure for ITT, but track time-to-link as a covariate.

Analysis plan & statistical considerations- Primary analysis: ITT comparing treatment vs control on primary metric with pre-specified model (e.g., logistic regression or Poisson/negative binomial for counts) controlling for covariates (platform, region, historical activity). Report uplift, 95% CI, p-values adjusted for sequential looks. - Secondary: per-protocol sensitivity excluding contaminated accounts; subgroup analyses with multiplicity adjustments. - Power & sample size: compute MDE and required n before ramp; ensure early ramps have sample large enough to detect major regressions. - Backfill & audit: store raw assignment logs; keep reproducible SQL/ETL notebooks; daily automated dashboards showing exposures, assignment consistency, lift, and guardrails.

Example quick checks after each ramp:- Are logged exposures = expected % of traffic? (± tolerance) - Contamination rate < threshold (e.g., 1%) - Guardrails stable (no significant increases) - Primary metric: no large negative drift; compute uplift and CI

This plan provides reproducible instrumentation, protects against cross-device contamination, defines clear populations and metrics, uses safe ramping with monitoring windows, and establishes clear automatic and manual rollback criteria.

EasyTechnical

76 practiced

You're handed a new event stream from a mobile app. Describe five concrete data-quality checks you would perform before using the data for product analytics or experiments. For each check explain the diagnostic query or metric, acceptance criteria, and suggested follow-up fixes or instrumentation changes.

Sample Answer

1) Schema & required fields present- Diagnostic: count rows where any required_field IS NULL or missing_event_type; sample JSON schema validation failures.- Metric/query: SELECT COUNT(*) AS missing FROM events WHERE event_type IS NULL OR user_id IS NULL;- Acceptance: <0.1% missing for critical fields (user_id, timestamp, event_type); zero for hard-required fields.- Follow-up: Add client-side validation, enforce server-side schema checks (e.g., JSON Schema), log rejected events to a dead-letter queue for debugging.

2) Timestamps and time skew- Diagnostic: distribution of event_timestamp vs ingestion_timestamp; max/min offset.- Metric/query: SELECT PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY ingestion_ts - event_ts) AS p99_skew FROM events;- Acceptance: p99 skew < 5 minutes; no negative >1 hour unless expected (offline buffering).- Follow-up: Ensure mobile SDK sets accurate device time, include timezone/epoch; surface device_time vs server_time fields.

3) Duplicate events- Diagnostic: dedupe by (user_id, event_type, event_id, event_ts) and count duplicates.- Metric/query: SELECT event_id, COUNT(*) FROM events GROUP BY event_id HAVING COUNT(*)>1;- Acceptance: duplicates <0.05% or none for idempotent events.- Follow-up: Implement client-side idempotency keys, server idempotency dedupe, and truncate replay logs.

4) Volume and cardinality sanity- Diagnostic: compare daily MAU and event-per-user to expected baselines; check sudden spikes/drops.- Metric/query: daily_events = SELECT DATE(event_ts), COUNT(*) FROM events GROUP BY 1; compare to baseline % change.- Acceptance: daily change within expected business variance (e.g., ±20% without release).- Follow-up: Add release tags, feature-flag metadata; if spike: inspect campaign/cron jobs or instrumentation bug.

5) Event semantics and payload validity- Diagnostic: check distributions and nulls for key metrics (price ≥0, screen_name in allowed set).- Metric/query: SELECT COUNT(*) FROM events WHERE price < 0 OR screen_name NOT IN (<allowed_list>);- Acceptance: 0 invalid values; expected categorical coverage aligned with UI.- Follow-up: Tighten enums in SDK, add unit tests for event builders, instrument end-to-end telemetry to trace how invalid payloads are produced.

These checks quickly surface upstream bugs and guide concrete fixes (validation, idempotency, server-side schema enforcement, and better telemetry) so analytics and experiments rely on trustworthy data.

MediumTechnical

52 practiced

You only have pre-aggregated metrics from an analytics platform but need raw behavioral data to do attribution. Explain how you would validate the provided aggregations (e.g., sampling rate, deduplication, timezone handling), what reconciliation queries or data requests you would make to get raw events, and what instrumentation changes you would request to enable accurate attribution analysis.

Sample Answer

Approach summary: validate aggregations by testing consistency (counts, uniqueness, timing), estimate sampling, verify deduplication logic and timezone handling; request raw event exports (unaggregated) and add instrumentation to capture deterministic identifiers and canonical timestamps so attribution can be done reliably.

Validation checks and reconciliation queries- Total count reconciliation: compare platform-aggregated metric to sum of raw events (by day / event_type).

sql

-- compare aggregated metric to raw events
SELECT
  DATE(event_timestamp, "UTC") AS date,
  COUNTIF(event_type='purchase') AS raw_purchases,
  agg.purchases AS reported_purchases
FROM `project.dataset.raw_events` r
LEFT JOIN `project.dataset.aggregates` agg
  ON DATE(r.event_timestamp,"UTC") = agg.date
GROUP BY date, agg.purchases
ORDER BY date;

- Sampling rate estimation: if platform claims sampling, estimate by comparing unique client_ids in raw vs sampled stream.

sql

SELECT
  COUNT(DISTINCT client_id) AS unique_clients_raw,
  COUNT(DISTINCT client_id) * 1.0 / SUM(sample_flag) OVER() AS est_sample_rate
FROM `project.dataset.raw_events`;

- Deduplication check: look for duplicate event_ids or identical (client_id, event_type, timestamp_ms) within short windows.

sql

SELECT event_id, COUNT(*) as cnt
FROM `project.dataset.raw_events`
GROUP BY event_id
HAVING cnt > 1
LIMIT 100;

- Timezone handling: ensure timestamps are normalized to UTC and check for shifts around DST or midnight boundaries.

sql

SELECT
  DATE(event_timestamp, "UTC") AS utc_date,
  DATE(event_local_timestamp, "America/Los_Angeles") AS local_date,
  COUNT(*) FROM `...` GROUP BY utc_date, local_date ORDER BY utc_date LIMIT 50;

- Attribution-specific: reconstruct sessions by client_id + session_id, compare conversions within attribution windows to aggregated funnel counts.

Data/exports to request- Full raw event stream (unaggregated) in BigQuery or raw parquet: event_id, client_id (persistent), user_id (if logged in), event_type, event_props (JSON), event_timestamp (ISO8601 UTC), client_timestamp, server_timestamp, session_id, page_url/referrer, user_agent, platform, sampling_flag, and ingestion metadata.- Raw SDK/server logs around known spikes for troubleshooting.- Historical export covering at least twice the longest attribution lookback.

Instrumentation changes to request- Unique, stable event_id per event (UUID) generated client-side or server-side.- Persistent user identifier and separate anonymous device id; ensure clear mapping when user logs in (merge keys).- Canonical timestamp in UTC from server; include client_timestamp for clock-drift analysis.- Session_id and explicit session_start event; include referrer and campaign parameters on entry events.- Sampling metadata: if sampling occurs, include sample_rate and sample_seed per event so we can re-weight.- No client-side aggregation; send raw events or enable raw export toggle.- Add consistent event taxonomy and schema (required fields) and enforce semantic versioning of schema.- Instrument conversion-context fields: attribution_id, touchpoint_id, media_source, click_timestamp when available.

Why these steps- Reconciliation finds mismatches and quantifies bias from sampling/dedup/timezones.- Raw exports plus event-level IDs enable deterministic attribution, dedup, sessionization.- Instrumentation changes remove ambiguity (who did what, when), allow deterministic joins, and enable reweighting for sampling.

Acceptance criteria before attribution analysis- Raw event export available and matches aggregated sums within an explainable delta.- Sampling rate known and reversible (or small enough to accept with adjustment).- Event schema contains IDs, UTC timestamps, and session info for reliable joins and lookback attribution.

MediumTechnical

45 practiced

Design an A/B test to evaluate a new checkout flow expected to change conversion. Specify hypothesis, primary metric, minimum detectable effect, sample size calculation assumptions (baseline rate, power, alpha), randomization strategy, guardrail metrics, pre-registration entries, and stopping rules. Explain how you would handle multiple variations and multiple metrics.

Sample Answer

Hypothesis: Changing to the new checkout flow will increase purchase conversion rate (completed purchase / sessions reaching checkout) versus current flow.

Primary metric: Checkout-to-purchase conversion rate (binary per session).

Minimum Detectable Effect (MDE): 5% relative uplift (e.g., from 10.0% → 10.5%).

Assumptions for sample size:- Baseline conversion p0 = 0.10- MDE (absolute) = 0.005- Power = 0.8- Alpha = 0.05 (two-sided)

Sample size calc (approximate two-proportion z-test):

python

import math
from statsmodels.stats.power import NormalIndPower
power = NormalIndPower()
p0=0.10; p1=0.105
effect = (p1-p0)/math.sqrt(p0*(1-p0))
n = power.solve_power(effect_size=effect, power=0.8, alpha=0.05, alternative='two-sided')
print(math.ceil(n))  # per group

This yields ~30k–40k sessions per arm (compute precisely before launch).

Randomization strategy:- Unit = user-session or user (preferably user for one-experience consistency).- Server-side deterministic hashing on user_id + experiment_id to assign treatment, ensuring allocation and persistence.- Exclude bots, internal traffic; stratify or block by device type if conversion differs strongly.

Guardrail metrics:- Cart abandonment rate, page load time, payment error rate, revenue per user, customer support messages.- Monitor for negative impacts; set soft thresholds (e.g., >10% relative worsening triggers investigation).

Pre-registration (publish before launch):- Hypothesis, primary metric, secondary/guardrail metrics, MDE, sample size/time, randomization unit, inclusion/exclusion, planned analysis (two-proportion z-test, covariate adjustment), segmentation, stopping rules.

Stopping rules:- No peeking policy: do not stop early for significance unless pre-specified using alpha spending (O’Brien-Fleming) or sequential testing.- Allow early stop for harm if guardrail crosses pre-defined boundary (e.g., payment error rate increase >5pp with p<0.001).- If using continuous monitoring, apply corrected thresholds (alpha spending) to control Type I error.

Multiple variations / multiple metrics:- Multiple variations: use ANOVA / logistic regression or multi-arm z-tests; apply multiplicity correction (Bonferroni or preferably FDR/BH) for multiple comparisons vs control. Alternatively consider Bayesian multi-armed bandit for faster routing but pre-specify priors and decision rules.- Multiple metrics: keep one primary metric for decision. Adjust secondary/guardrails with FDR control; report confidence intervals and p-values but avoid "cherry-picking." Use multiplicity-aware decision rules: require primary significant and no significant harm on guardrails.

Analysis notes:- Use pre-specified covariates (device, country) for regression adjustment to improve power.- Report absolute and relative effects, CIs, and run subgroup checks only as exploratory unless pre-registered.

Unlock Full Question Bank

Get access to hundreds of Data Analysis and Requirements Translation interview questions and detailed answers.

Join thousands of developers preparing for their dream job.