Exploratory Data Analysis Questions

Exploratory Data Analysis is the systematic process of investigating and validating a dataset to understand its structure, content, and quality before modelling or reporting. Core activities include examining schema and data types, computing descriptive statistics such as counts, means, medians, standard deviations and quartiles, and measuring class balance and unique value counts. It covers distribution analysis, outlier detection, correlation and relationship exploration, and trend or seasonality checks for time series. Data validation and quality checks include identifying missing values, anomalies, inconsistent encodings, duplicates, and other data integrity issues. Practical techniques span SQL profiling and aggregation queries using GROUP BY, COUNT and DISTINCT; interactive data exploration with pandas and similar libraries; and visualization with histograms, box plots, scatter plots, heatmaps and time series charts to reveal patterns and issues. The process also includes feature summary and basic metric computation, sampling strategies, forming and documenting hypotheses, and recommending cleaning or transformation steps. Good Exploratory Data Analysis produces a clear record of findings, assumptions to validate, and next steps for cleaning, feature engineering, or modelling.

MediumTechnical

59 practiced

Explain how to compute weighted averages for KPIs within SQL or pandas, for example computing average order value weighted by number of items per order instead of simple average of order prices. Explain why naive averages may mislead and demonstrate the formula or SQL construct you'd use.

Sample Answer

Weighted averages give each observation a weight proportional to its importance (e.g., number of items). A naive average (mean of order prices) treats every order equally and can mislead when orders vary in size: many small orders can skew the simple mean away from what a "per-item" metric would show.

Formula:weighted_avg = sum(value_i * weight_i) / sum(weight_i)

SQL (compute average order value weighted by number_of_items, i.e., value is order_price):

sql

SELECT
  SUM(order_price * num_items) / NULLIF(SUM(num_items),0) AS weighted_avg_order_value
FROM orders;

If you want it per customer or per day:

sql

SELECT
  customer_id,
  SUM(order_price * num_items) / NULLIF(SUM(num_items),0) AS weighted_avg_order_value
FROM orders
GROUP BY customer_id;

Pandas:

python

# df has columns: order_price, num_items
weighted_avg = (df['order_price'] * df['num_items']).sum() / df['num_items'].sum()

# groupby example: weighted avg per customer
grp = df.groupby('customer_id').apply(
    lambda g: (g['order_price']*g['num_items']).sum() / g['num_items'].sum()
)

Notes and pitfalls:- NULLIF or guard against division by zero when sum(weights)=0.- Choose weights intentionally: use number_of_items to get per-item view, or order_value to get revenue-weighted view.- For sampling or missing data, ensure weights reflect population (don’t weight by an already sampled frequency without correction).- For dashboards, label clearly whether metric is weighted (per-item vs per-order) to avoid misinterpretation.

HardTechnical

56 practiced

You have 10,000 numeric features and building a full correlation heatmap is infeasible. Propose computational and visualization strategies to summarize relationships at scale and surface the most relevant feature pairs for BI analysts. Discuss dimensionality reduction, clustering, feature grouping, and prioritization heuristics.

Sample Answer

Clarify goals & constraints first: do BI analysts need global structure (redundancy, groups) or targeted pairs for dashboards/alerting? Assume both: summarize overall relationships, then surface top pairs for investigation. Proposed pipeline:

1. Pre-filter & compute efficiently- Remove low-variance / near-constant features and features with excessive missingness.- Compute pairwise relationships on a sample or using streaming/online estimators to reduce cost (e.g., sample 1–10% rows, or reservoir sampling).- Use approximate algorithms / parallelization (block-wise pair computation, vectorized Pearson via BLAS, Spark/BigQuery UDFs) or approximate nearest neighbors (ANN) on feature vectors.

2. Dimensionality reduction for structure- Apply PCA (or randomized SVD) on standardized features to capture redundancy; examine loadings to map principal components back to top contributing features.- Use nonlinear embeddings (UMAP/t-SNE on feature correlation/profile vectors) to visualize features as points clustered by behavior.

3. Clustering & feature grouping- Compute feature similarity using 1 - |corr| or mutual information; cluster features with hierarchical/agglomerative clustering or DBSCAN to form groups.- Create representative features per cluster (medoid, first PC) to reduce from 10k to a few hundred or dozen groups.

4. Prioritization heuristics to surface pairs- Rank pairs by absolute correlation, but prioritize by: - Business importance weight (metrics flagged by stakeholders) - High variance and low missingness - Predictive relevance (feature importance from quick decision tree or mutual information with a target) - Redundancy score (pairs that would reduce feature space if collapsed) - Novelty/anomaly: correlations that deviate from historical baseline- Present top-K pairs per cluster and top inter-cluster correlations.

5. Visualization strategies for BI audiences- Clustered/block heatmap of group-level correlations (N groups × N groups) with ability to drill into group → pairwise heatmap for that block.- Interactive network graph: nodes = features or clusters; edge weight = corr magnitude; filters for threshold, business tags; click node to show top correlated partners and scatterplots.- Small-multiples scatterplots or hexbin plots for top pairs with regression and point-density controls (for performance).- Tableau/Power BI dashboards: show summary statistics, group thumbnails, top-K table with linkable detail views; use parameters for thresholds and sampling.- Sparse correlation table export for analysts to explore in spreadsheets.

6. Trade-offs & operational notes- Sampling/approximation trades accuracy for speed — validate top hits with full-data recomputation when flagged.- Beware spurious correlations with high dimensionality — combine correlation with domain heuristics and predictive checks.- Automate periodic recomputation and alerting for correlation changes.

Outcome: reduces 10k×10k problem into actionable group-level summaries and a prioritized, validated short-list of feature pairs for BI analysts to inspect and use in dashboards.

MediumTechnical

65 practiced

Write SQL to compute a cohort retention matrix. Given registrations(user_id, registered_at) and orders(user_id, order_date), produce monthly cohorts by registration month and percent of users in each cohort who made at least one purchase in months 0..N after registration. Assume Postgres and explain edge cases.

Sample Answer

Approach: create cohorts by registration month, compute month offset for each user's first order on/after registration, then aggregate to get percent of cohort active in month 0..N. Use generate_series to ensure months with 0% appear.

SQL (Postgres):

sql

-- params
WITH params AS (SELECT 6 AS max_months), 

-- normalize registrations to cohort month (UTC or company timezone)
cohorts AS (
  SELECT
    user_id,
    date_trunc('month', registered_at::timestamptz AT TIME ZONE 'UTC')::date AS cohort_month
  FROM registrations
  GROUP BY user_id, cohort_month
),

-- find each user's first order ON/AFTER registration, and compute month offset
first_orders AS (
  SELECT
    c.user_id,
    c.cohort_month,
    MIN(o.order_date::date) FILTER (WHERE o.order_date::date >= c.cohort_month) AS first_order_date
  FROM cohorts c
  LEFT JOIN orders o
    ON o.user_id = c.user_id
  GROUP BY c.user_id, c.cohort_month
),

-- compute month offset (0 = same month as registration)
orders_with_offset AS (
  SELECT
    user_id,
    cohort_month,
    CASE 
      WHEN first_order_date IS NULL THEN NULL
      ELSE (date_part('year', first_order_date) - date_part('year', cohort_month)) * 12
           + (date_part('month', first_order_date) - date_part('month', cohort_month))
    END::int AS month_offset
  FROM first_orders
),

-- cohort sizes
cohort_sizes AS (
  SELECT cohort_month, COUNT(*) AS cohort_users
  FROM cohorts
  GROUP BY cohort_month
),

-- cross join months 0..N for each cohort_month to ensure full matrix
months AS (
  SELECT generate_series(0, (SELECT max_months FROM params)) AS month_offset
),

cohort_months AS (
  SELECT DISTINCT cohort_month FROM cohorts
),

matrix AS (
  SELECT
    cm.cohort_month,
    m.month_offset,
    cs.cohort_users,
    COALESCE(SUM(CASE WHEN o.month_offset = m.month_offset THEN 1 ELSE 0 END), 0) AS users_active
  FROM cohort_months cm
  CROSS JOIN months m
  LEFT JOIN cohort_sizes cs ON cs.cohort_month = cm.cohort_month
  LEFT JOIN orders_with_offset o ON o.cohort_month = cm.cohort_month
  GROUP BY cm.cohort_month, m.month_offset, cs.cohort_users
)

SELECT
  cohort_month,
  month_offset,
  users_active,
  cohort_users,
  ROUND(100.0 * users_active::numeric / NULLIF(cohort_users,0),2) AS pct_retained
FROM matrix
ORDER BY cohort_month, month_offset;

Key points and edge cases:- Uses first purchase on/after registration; exclude orders before registration.- Ensure timezone normalization (use company timezone instead of 'UTC' if needed).- Users with multiple registrations: deduplicate or choose business rule (this uses user_id->cohort month grouped).- Users with no orders get NULL offset and count as 0 retention.- Choose whether month 0 counts registration-month purchases (this query does).- For long gaps, cap months with generate_series; orders beyond max_months are ignored for matrix but can be analyzed separately.

MediumTechnical

81 practiced

When you see extreme numeric values in a column, how do you determine whether they are legitimate business outliers or data entry / processing errors? Provide an EDA checklist that includes visualizations, grouping, cross-field checks, and suggestions for corrective actions to prepare clean dashboard metrics.

Sample Answer

Start with a hypothesis-driven EDA checklist: you want to verify provenance, reproducibility, and business plausibility before deciding whether to treat extreme values as true outliers or errors.

1) Quick provenance & sanity checks- Confirm source systems, ingestion timestamps, ETL job runs, timezone/currency conversions, and any recent schema changes.- Look for batch loads or retries around the timestamps of extremes.

2) Visualizations (first-pass)- Histogram / density: see distribution and tail shape.- Boxplot (by segment): highlights points beyond IQR fences.- Time series / run chart (with event markers): check if spikes align with promotions, imports, or outages.- Scatter plot (value vs volume/age) and log-scale plots for heavy tails.- Cumulative distribution (CDF) to quantify tail mass.

3) Grouping and segmentation- Aggregate by customer_id, region, product, channel, and time-granularity (day/week/month). If extremes concentrate in one group, more likely business-driven.- Compare metrics per cohort (new vs returning users, internal test accounts).

4) Cross-field validation- Cross-check suspicious numeric with related fields: quantity × unit_price ≟ revenue, session_duration ≟ page_views, refund_flag with negative values, order_count with order_items.- Verify nulls/zeros in dependent fields, inconsistent IDs, duplicated transaction_ids.- Use join checks to external reference tables (price lists, exchange rates).

5) Automated flagging & examples- Rule-based flags: value > mean + 5σ, or beyond 99.9th percentile, or outside business thresholds.- SQL example to flag extremes:

sql

SELECT id, value, created_at
FROM transactions
WHERE value > (SELECT PERCENTILE_CONT(0.999) WITHIN GROUP (ORDER BY value) FROM transactions)

6) Investigate records- Drill into raw logs, raw payloads, and audit columns (created_by, source_system).- Check for duplicates, unit mismatch (cents vs dollars), scaling errors, sign errors, and timezone shifts.

7) Corrective actions (choose based on root cause)- If data entry/processing error: fix ETL bug, correct records (convert units, remove duplicates), and backfill.- If business-true but extreme: leave as-is, create separate visualization (cap axes or use log scale), compute robust metrics (median, trimmed mean, winsorized mean).- If uncertain: mark as “suspect” and exclude from sensitive aggregates until confirmed; provide sensitivity analysis.- Implement alerts and tests: add validation rules (bounds, referential checks), continuous monitoring, and SLA for data fixes.

8) Dashboard and metric prep- Use robust aggregations (median, percentiles) and show data volume & counts.- Provide filters/toggles: include/exclude outliers, choose capping method, choose log/linear scale.- Annotate charts with provenance and last-verified timestamp; document business rules.

This approach balances statistical detection with business validation to avoid throwing away real signals while preventing erroneous values from misleading stakeholders.

MediumTechnical

78 practiced

How would you quantify and visualize pairwise correlation among 50 numeric features to prioritize candidate features for downstream modeling and dashboards? Discuss choice of correlation metric, dimensionality reduction, clustering, and visualization techniques to surface the most relevant relationships.

Sample Answer

Situation: You have 50 numeric features and need to surface the strongest, actionable pairwise relationships so downstream models and dashboards use non-redundant, meaningful signals.

Approach summary:1) Correlation metric- Use Pearson by default for linear relationships after checking distributions.- Compute Spearman (rank) and/or Kendall as robustness checks for non-linear monotonic relationships or heavy tails.- Treat missing values consistently (pairwise complete) and winsorize or log-transform heavy outliers before Pearson.

2) Quantify & prioritize- Produce a full 50×50 correlation matrix for each metric.- Convert to a distance: d = 1 − |r| (or 1 − r if direction matters) for clustering.- Compute cluster-level summaries: mean absolute correlation per feature, max |r| with any other feature, and Variance Inflation Factor to flag multi-collinearity.- For prioritization: rank features by (a) business importance, (b) uniqueness score = 1 − max |r|, and (c) predictive signal (e.g., feature importance or SHAP from a quick baseline model).

3) Dimensionality reduction & clustering- Run PCA on standardized features: inspect explained variance and the first 2–3 PCs; use loadings to see which features drive each PC (high absolute loading → cluster representative).- For visual clustering, apply hierarchical clustering (Ward or average linkage) on the correlation-distance; the dendrogram reveals natural groups. Optionally validate with k-means on PCs or DBSCAN/affinity propagation for non-globular clusters.

4) Visualizations to surface relationships- Clustered heatmap / correlogram: reorder rows/cols by hierarchical clustering, annotate clusters, show both Pearson and Spearman tabs. Use diverging color palette and mask diagonal; include interactive tooltip with exact r, p-value, sample count.- Dendrogram + heatmap combo to pick cluster representatives.- Network graph: nodes=features, edges filtered by |r|>threshold (e.g., 0.6), edge width = |r|, color by sign — useful for executives to see groups.- Scatter matrix or linked scatter + marginal histograms for top N correlated pairs to inspect shape and outliers.- PCA biplot: plot features as vectors on PC1/PC2 → quick view of correlated groups.- Interactive dashboard (Tableau/Power BI): allow filters for segment, choose metric (Pearson/Spearman), threshold slider, and buttons to “pick representative” per cluster to export feature list.

Edge cases & best practices- Correlation ≠ causation; always validate business logic and time-ordering.- If features are time series, use cross-correlation or shift-aware measures.- For categorical-numeric mixes, use point-biserial or ANOVA-based measures.- Document feature groups and chosen representatives; re-run periodically as data drifts.

Result: a repeatable pipeline (compute correlations → cluster → summarize → visualize interactive dashboard) that highlights redundant features, surfaces strong relationships for deeper investigation, and produces a prioritized, business-aligned candidate list for modeling and dashboard inclusion.

Unlock Full Question Bank

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

Join thousands of developers preparing for their dream job.