Statistical Fundamentals and Exploratory Analysis Questions

Core descriptive and exploratory statistical techniques used to summarize data, detect patterns, and generate testable hypotheses. Covers measures of central tendency and dispersion such as mean median and standard deviation, distributional assumptions, frequency and cross tabulation, visualization for exploration, cohorting and segmentation, identifying biases and data quality issues, and designing exploratory analyses to suggest causal hypotheses. Understand when to apply EDA to prepare data for formal tests and how to translate exploratory findings into confirmatory analyses. Candidates should demonstrate ability to summarize quantitative data, detect anomalies, and propose appropriate follow up hypothesis tests or experiments.

MediumTechnical

66 practiced

Discuss the multiple comparisons problem in exploratory analysis. Explain Bonferroni correction and Benjamini–Hochberg procedure, and provide guidance on when each is more appropriate in a BI context where analysts run many hypothesis tests across segments and metrics.

Sample Answer

Multiple comparisons arises when you run many hypothesis tests (e.g., conversion by 50 segments × 10 metrics = 500 tests). With α=0.05 each test, you expect ~25 false positives by chance — inflating Type I error and leading to spurious business actions.

Bonferroni correction- Method: divide desired family-wise error rate (FWER) α by number of tests m, use per-test threshold α/m (or multiply p-values by m).- Properties: controls FWER (probability of any false positive) conservatively, valid under arbitrary dependence.- Pros: simple, strong control — useful when any false positive is costly (regulatory reports, financial reconciliation).- Cons: very conservative when m large or tests correlated → high Type II error (miss real effects).

Benjamini–Hochberg (BH) procedure- Method: sort p-values p(1) ≤ ... ≤ p(m); find largest k with p(k) ≤ (k/m)·q, reject all p(i) ≤ p(k). Controls false discovery rate (FDR) = expected proportion of false rejections.- Properties: less conservative, higher power, assumes independence or certain positive dependence (often acceptable in practice).- Pros: better for exploratory analyses where you can tolerate some false positives in exchange for more discoveries.- Cons: allows some false positives; careful interpretation required.

Guidance for BI context- Use BH for routine exploratory dashboards, segmentation scans, feature discovery, A/B test post-hoc dives — you want sensitivity and can follow-up with validation experiments or business-significance filters.- Use Bonferroni (or family-limited corrections) when decisions have high cost per false positive (billing, compliance, release gates) or for small, pre-specified families of tests.- Practical tips: - Define test families logically (one metric across segments vs. many unrelated metrics) and correct per-family rather than across everything. - Combine statistical significance with effect size and business thresholds (minimum lift) before acting. - Report adjusted p-values and the method used in dashboards, and surface the expected FDR/FWER so stakeholders understand uncertainty. - Where possible, pre-register primary metrics to avoid large multiple-testing burdens and reserve corrections for exploratory follow-ups.

Overall: BH for scalable discovery in BI; Bonferroni for high-cost, confirmatory decisions or very small test families.

MediumTechnical

70 practiced

Given a transactions table with daily totals, write a SQL query that flags anomalous days using a rolling 28-day mean and standard deviation and returns dates where daily total revenue's z-score exceeds 3. Explain how you would adapt this if distribution is heavy-tailed.

Sample Answer

Approach: compute 28-day rolling mean and stddev (excluding current day or including depending on requirement), compute z-score = (revenue - mean)/stddev, and return dates with |z|>3. If distribution is heavy-tailed, use robust alternatives (median & MAD), log-transform, or percentile-based thresholds.

sql

-- Rolling 28-day mean/stddev excluding current day (looks back 28 days)
WITH daily AS (
  SELECT
    day::date AS dt,
    total_revenue
  FROM transactions_daily
),
rolling AS (
  SELECT
    dt,
    total_revenue,
    AVG(total_revenue) OVER (
      ORDER BY dt
      ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING
    ) AS mean_28,
    STDDEV_SAMP(total_revenue) OVER (
      ORDER BY dt
      ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING
    ) AS sd_28
  FROM daily
)
SELECT
  dt,
  total_revenue,
  mean_28,
  sd_28,
  CASE
    WHEN sd_28 IS NULL OR sd_28 = 0 THEN NULL
    ELSE (total_revenue - mean_28) / sd_28
  END AS z_score
FROM rolling
WHERE sd_28 IS NOT NULL
  AND ABS((total_revenue - mean_28) / NULLIF(sd_28,0)) > 3
ORDER BY dt;

Adapting for heavy-tailed distribution:- Use log(total_revenue) before computing rolling stats to stabilize variance.- Or compute rolling median and MAD (median absolute deviation) and flag when |x - median| / (1.4826*MAD) > 3 (1.4826 scales MAD to stddev for normal-like interpretation).- Or use percentile rules: flag values above the 99.7th percentile of the rolling window.- In BI dashboards, surface both z-score and robust-score and let stakeholders inspect context (seasonality, holidays). Validate alerts with manual review and tune window/thresholds as needed.

EasyTechnical

100 practiced

Explain the difference between population variance and sample variance and why we often use 'n-1' (Bessel's correction) when estimating variance from a sample. Provide a concise business example where using sample variance vs population variance matters when reporting uncertainty.

MediumTechnical

62 practiced

You are summarizing churn by plan type but some plans have very few users (class imbalance). Describe three statistical strategies to present reliable churn comparisons across plans, including aggregation choices, bootstrapping, and weighting, and explain pros/cons of each.

Sample Answer

When comparing churn across plans with class imbalance, aim for estimates that are stable, comparable, and actionable. Three practical strategies:

1) Aggregation choices (merge sparse plans or use hierarchical roll-up)- Approach: Combine very small plans into a “Other” group or roll up by plan family (e.g., monthly vs annual) until sample sizes reach a sensible minimum (e.g., n≥50).- Pros: Simple, reduces noise, easier to interpret on dashboards.- Cons: Hides plan-level nuance; risk of masking a high-churn outlier in a merged group.- Implementation tip: Show both rolled-up and drill-down views; annotate when a plan was aggregated.

2) Bootstrapping (estimate uncertainty)- Approach: For each plan, bootstrap churn rate by resampling users (with replacement) many times to build confidence intervals for churn.- Pros: Nonparametric, works with small samples, provides uncertainty quantification you can display (CI bands).- Cons: CIs for extremely small n remain wide; more compute for many plans.- Implementation tip: Precompute bootstraps in ETL and surface 95% CI in visuals; use stratified bootstrap if seasonality applies.

3) Weighting (stabilize estimates via shrinkage)- Approach: Apply empirical Bayes / James-Stein shrinkage or weight per-plan churn toward the overall mean based on sample size (posterior mean = (n*plan_rate + τ*global_rate)/(n+τ)).- Pros: Reduces variance for small groups while preserving signal for large ones; principled statistical adjustment.- Cons: Introduces bias toward global mean; requires choosing hyperparameter τ or estimating prior.- Implementation tip: Estimate prior from historical data; display both raw and shrinkage-adjusted rates and explain methodology in the dashboard.

Best practice: combine methods—use aggregation for ultra-rare plans, bootstrap to show uncertainty, and present shrinkage-adjusted rates as primary comparisons with raw rates available on demand. Include sample size and CI annotations so stakeholders can judge reliability.

HardSystem Design

73 practiced

High-cardinality fields (thousands of SKUs) are causing slow load times in Looker/Tableau dashboards. Propose strategies to create responsive interactive dashboards that still allow meaningful drill-down: discuss pre-aggregation, materialized views, parameterized queries, top-K + 'other' buckets, and data sampling.

Sample Answer

Requirements & constraints:- Fast interactive UX (<1–2s for aggregates, <3–5s for drill-downs)- Support exploration across thousands of SKUs with meaningful detail on demand- Keep data fresh (near real-time vs daily) and cost within budget

High-level approach:- Serve common summary queries from pre-computed structures; push on-demand detail to targeted, parameterized queries. Combine pre-aggregation + materialized views + UX patterns (Top-K + “Other”) + safe sampling.

Strategies and how/when to apply:

1) Pre-aggregation (rollups)- Build aggregated tables at merchant/category/date granularity that cover 80–90% of dashboard queries (e.g., SKU-level only for selected top categories).- In Looker: implement aggregate tables or persistent derived tables (PDTs) in LookML; in Tableau, point to aggregated tables or use extracts refreshed on a schedule.- Benefit: sub-second pivots for common slices. Trade-off: storage and ETL complexity.

2) Materialized views (MV)- Use DB-native MVs (BigQuery, Redshift, Snowflake streams+tasks) for frequently-run group-bys (sales_by_sku_day). Refresh policy depends on freshness needs (incremental vs full).- Benefit: low compute per query; DB-managed performance. Trade-off: MV refresh cost and eventual consistency.

3) Parameterized (filtered) queries & user-driven loading- In dashboards, require user to pick a filter (category, brand, date range, top-N) before issuing SKU-level queries. Use dashboard parameters, filter-first UX or “apply” buttons.- Implement LookML or Tableau parameter actions to limit the query domain (e.g., WHERE category = :selected_category).- Benefit: avoids exploding cardinality by narrowing scope.

4) Top-K + “Other” buckets- Default visuals show top 10–50 SKUs and group the rest into “Other” with a drill path to see full list on request.- Precompute top-K per dimension in a table (top_skus_by_category_week) and store “other” aggregates to preserve totals.- Benefit: preserves readability, reduces mark count. Trade-off: requires a clear drill path for analysts.

5) Data sampling for exploration- For early exploratory dashboards, use reservoir sampling or deterministic hashed sampling on SKU to return a representative subset (e.g., 5–10%) for near-real-time interaction.- Ensure final/official reports use full data; show sampling flag and estimated error.- Benefit: hugely faster iterations. Trade-off: statistical uncertainty—don’t use for billing or precise KPIs.

Implementation pattern (practical):- Build nightly ETL to create aggregated tables and MV for common metrics.- Expose lightweight aggregate LookML views and Tableau extracts for the dashboard base.- Add dashboard controls that require a scoped filter before SKU-level queries run; supply “Show all SKUs” button that triggers a background job or paginated download.- Maintain a top-K table and “other” aggregates to render PIE/BAR with stable totals.- For ad-hoc analysis, provide a separate “explore” view that runs parameterized queries against raw tables with query timeouts and quotas.

Operational considerations & trade-offs:- Freshness vs cost: more frequent MV/PDT refresh increases compute; balance with business need.- Storage vs query cost: pre-aggregation increases storage but reduces per-query compute.- Complexity: aggregation design requires understanding query patterns—monitor slow queries and add rollups iteratively.- UX: educate users on filters/drill paths; provide clear affordances (buttons, tooltips) so they know when they’re seeing sampled/aggregated results.

Metrics to track:- Dashboard load latency (median, P95), query cost per dashboard, MV/PDT refresh time, % of queries hitting pre-aggregated tables vs raw.

This combined approach keeps dashboards responsive for common workflows while enabling meaningful, controlled drill-down on high-cardinality SKU data.

Unlock Full Question Bank

Get access to hundreds of Statistical Fundamentals and Exploratory Analysis interview questions and detailed answers.

Join thousands of developers preparing for their dream job.