Question 1

You have an `orders` table with 50 million rows and a `customers` table with 2 million rows. A JOIN query between them takes 45 seconds. How would you approach optimizing this?

Accepted Answer

1. Diagnose with EXPLAIN/EXPLAIN ANALYZE: identify JOIN type, indexes used, row estimates. 2. Check indexes on JOIN key (customer_id) and WHERE/ORDER BY columns. 3. Consider partial indexes, correct column-order composite indexes. 4. Evaluate table partitioning by date if queries often filter by range. 5. Consider materialized views or pre-aggregation for repeated queries. 6. Benchmark after each change - no blind optimization. Recruiters want to see systematic thinking, not memorized solutions. Strong candidates always start with diagnosis (EXPLAIN ANALYZE) before making any changes - this distinguishes experienced practitioners. Big plus if the candidate mentions understanding query patterns: the best index depends on actual queries, not general theory. Senior candidates will discuss the trade-off between read performance vs write overhead when adding indexes, and know when to use a materialized view instead of pure query optimization.

Question 2

You ran an A/B test for a new checkout feature for 2 weeks. Group B has a 5.2% conversion rate vs Group A at 4.8%. p-value = 0.06. Your manager wants to ship immediately because 'B is clearly better'. How do you respond?

Accepted Answer

1. Explain what p=0.06 means: 6% probability of observing this result if there's no real difference - below standard threshold (typically α=0.05). 2. Calculate statistical power and sample size: was the test adequately powered? 3. Consider practical significance: is a 0.4% delta large enough to justify the risk and effort? 4. Propose actions: extend by 1 week OR lower α to 0.1 if there's business justification. 5. Highlight the risk of a wrong decision: shipping an inferior feature, revenue impact. This question tests both statistical skills and the ability to communicate and hold professional ground under stakeholder pressure. Strong candidates won't just rigidly say 'p>0.05 so not significant' but will present the full picture: power analysis, business context, and specific next steps. Bonus points for mentioning novelty effect, seasonal bias within 2 weeks, or segment analysis for deeper understanding. Most importantly: does the candidate dare to say 'no' to their manager with solid reasoning?

Question 3

A Sales Director asks you to build a sales performance dashboard. They say 'show me all the numbers'. What is your process from start to delivery?

Accepted Answer

1. Discovery (30 min): ask 3-5 questions to understand the decisions they need to make, not the metrics they want to see. 2. Define KPIs: classify metrics into North Star, driver, and diagnostic. 3. Wireframe/mockup: sketch layout before building, confirm with stakeholder. 4. Data audit: verify data sources, refresh latency, data quality issues. 5. Build MVP in 3 days: 3-5 core charts, no more. 6. Iteration: collect feedback after 1 week of actual use. 7. Documentation: define metrics to avoid number disputes later. This question clearly reveals whether candidates have product thinking or are just 'tool operators'. Someone who only knows how to build dashboards will immediately ask 'Tableau or Power BI?'. Strong candidates recognize 'show me everything' is an anti-pattern and know how to re-scope requirements through goal-oriented questions. Recruiters specifically watch whether candidates mention asking stakeholders back, and understand that the best dashboard is one used daily - not the one with the most charts.

Question 4

In a salary survey dataset of 10,000 employees, the 'salary' column has 23% missing data. You need to analyze the gender pay gap. How do you handle this missing data and why?

Accepted Answer

1. Classify the missing mechanism: MCAR (completely at random), MAR (at random conditional on other variables), or MNAR (systematic - e.g., high earners unwilling to disclose). 2. Check patterns: compare distribution of other variables (age, title, gender) between groups with and without salary. 3. Choose method based on mechanism: MCAR → listwise deletion OK; MAR → multiple imputation or regression imputation; MNAR → need external data or sensitivity analysis. 4. For gender pay gap analysis: MNAR is especially dangerous as it may bias results in one direction. 5. Report missing rate and handling method in the analysis output. This question clearly separates junior and senior candidates. Juniors typically answer 'use mean imputation' or 'delete missing rows' without thinking about consequences. Strong candidates recognize that for a sensitive analysis like gender pay gap, missing data handling can severely skew results. Extra points if the candidate mentions checking whether missing data correlates with gender - this is the core issue of this problem.

Question 5

A Product Director asks why revenue is up 15% this month but order count only grew 3%. How would you use cohort analysis to find the root cause?

Accepted Answer

1. Decompose revenue growth: volume (order count) vs value (AOV). Volume +3% is known, so AOV must have grown ~12%. 2. Cohort analysis by acquisition month: compare retention rate and AOV by cohort - which cohort is driving revenue? 3. Analyze basket composition: is revenue up due to high-value product mix shift or successful upsell/cross-sell? 4. Check repeat purchase rate: are existing customers buying more, or do new customers have higher AOV? 5. Segment cohorts by channel, geography, device to find patterns. 6. Conclude and propose actions: if old cohorts drive growth, invest in retention; if new cohorts, optimize that acquisition channel. This question assesses structured decomposition thinking - an essential skill for strong Data Analysts. Candidates need to show they can break a large problem into small, testable hypotheses rather than jumping to conclusions. Bonus points if candidates ask 'could this be seasonality?' or 'was there a special campaign this month?' before analyzing - showing holistic thinking.

Question 6

You discovered that the product return rate spiked 40% among new customers last month, correlated with a Flash Sale campaign. How do you present this insight to the CEO in 5 minutes?

Accepted Answer

1. Open with a headline number (1 sentence): 'We're losing X million VND/month in return costs because Flash Sales attract the wrong customer segment.' 2. Brief context (30 sec): returns +40% in new customers, starting from [date], correlated with Flash Sale. 3. Root cause (1 min): Flash Sale heavy discount → attracts price-sensitive customers → product doesn't match expectations → high returns. 4. Business impact (30 sec): specific figures - return costs, logistics, lost LTV. 5. Propose 2-3 options with clear trade-offs. 6. Close with a clear question: 'Which direction does the CEO want to prioritize?' This skill is the biggest differentiator between average and excellent Data Analysts. Recruiters want to see candidates understand that CEOs don't care about p-values or R-squared - they care about money and decisions. The SCQA framework (Situation - Complication - Question - Answer) or Pyramid Principle is a big plus. Observe whether candidates naturally transition from 'data story' to 'recommendation', or just read numbers.

Question 7

Would you choose Python/pandas or SQL for each scenario: (a) calculating monthly active users from a 200-million-row event log table, (b) building an RFM customer segmentation model, (c) cleaning a 50,000-row Excel file from the sales team?

Accepted Answer

(a) SQL: large aggregations on the DB server are better than pulling data into Python - leverages query optimizer, avoids memory issues. (b) Python/pandas: RFM requires iterative scoring, normalization, clustering (K-means) - logic too complex for SQL alone. However: calculate raw R/F/M in SQL, then pull to Python for further processing. (c) Python/pandas: Excel isn't a DB, pandas reads it directly, handles messy data (merged cells, irregular formats) better. General rule: SQL for transformation/aggregation on data warehouse; Python for ML, complex logic, and non-DB data. This question assesses pragmatic thinking - choosing the right tool for the right job, not personal preference. Strong candidates won't answer in extremes. They recognize that in practice, SQL and Python are often used together (SQL extract → Python transform/model → SQL load results). Bonus points for mentioning data volume considerations, team skill sets, and maintainability when choosing tools.

Question 8

The company is launching an 'AI Job Recommendations' feature on a recruitment app. As the Data Analyst, how would you design the metrics framework to evaluate the success of this feature?

Accepted Answer

1. North Star Metric: rate of candidates receiving offers from AI-recommended jobs (quality of placement). 2. Leading indicators (measure immediately): CTR on recommendations (>recommendation baseline), apply rate from recommendations, session depth with vs without recommendations. 3. Lagging indicators (measure after 30-90 days): interview rate, offer rate, 30-day user retention after applying. 4. Guardrail metrics (cannot decrease): total applies don't drop, users don't skip manual search (avoid engagement trap). 5. Algorithmic metrics: precision@K, recall@K, diversity of recommendations (avoid filter bubble). 6. Establish baselines, targets, and alert thresholds before launch day. 7. Segment by: junior vs senior, industry, geography. This expert-level question assesses the ability to combine product, statistical, and machine learning thinking. Strong candidates understand that metric design is an art - choosing the wrong metric can optimize for a bad product (e.g., maximizing CTR but users apply to unfit jobs). Big bonus for mentioning guardrail metrics, as this signals someone who has seen A/B tests 'succeed' while harming overall experience. If candidates naturally distinguish leading vs lagging indicators and explain why both are needed, this is a strong signal of senior-level thinking.