Microsoft Data Scientist (Staff Level) Interview Preparation Guide 2026

Data Scientist

Microsoft

Staff

7 rounds

Updated 6/25/2026

Microsoft's Data Scientist interview process consists of a structured multi-stage evaluation designed to assess technical depth, product intuition, machine learning expertise, and cultural alignment. The process begins with a recruiter screening, followed by a technical phone screen, and culminates in a 4-5 round virtual/onsite loop. For Staff-level candidates, the interviews emphasize architectural thinking, mentorship capability, cross-functional leadership, and the ability to drive data-driven strategy across multiple teams.

Interview Rounds

Recruiter Screening

25 min4 focus topicsbehavioral

What to Expect

This initial round with a recruiter focuses on understanding your background, professional trajectory, motivation for the role, and fit with Microsoft's culture. The recruiter will provide an overview of the interview process, timeline, and expectations. For Staff-level candidates, expect deeper questions about leadership experience, mentorship, and strategic impact in previous roles.

Tips & Advice

Prepare a concise 2-3 minute summary of your career progression emphasizing leadership milestones and strategic impact. Be ready to discuss specific examples of mentoring junior colleagues, driving technical initiatives, and how your skills align with Microsoft's mission. Show genuine interest in the specific team and problem domain. Ask thoughtful questions about team structure, current challenges, and growth opportunities. For Staff level, highlight your ability to work cross-functionally and drive organizational impact.

Focus Topics

Motivation and Role Alignment

Clearly articulate why you're interested in the Data Scientist role at Microsoft specifically, how your background aligns with the role requirements, and what attracts you to the company's mission and values.

Practice Interview

Study Questions

Domain Expertise and Technical Depth

Briefly highlight your expertise in data science domains relevant to Microsoft (cloud platforms, AI/ML, large-scale systems, business analytics). Mention key projects and technical accomplishments.

Practice Interview

Study Questions

Mentorship and Team Leadership

Provide concrete examples of mentoring junior data scientists, leading technical discussions, and influencing team direction. Discuss how you've developed talent and scaled team capabilities.

Practice Interview

Study Questions

Career Trajectory and Leadership Experience

Articulate your progression from junior to staff-level roles, emphasizing key achievements, increasing scope of responsibility, and examples of leading cross-functional initiatives. For Staff level, discuss how you've scaled from individual contributions to mentoring and strategic direction-setting.

Practice Interview

Study Questions

Technical Screen (Phone)

50 min5 focus topicstechnical

What to Expect

A 45-60 minute technical phone screen conducted via an interactive coding platform (e.g., CodeSignal, CoderPad). This round evaluates your ability to solve real-world data problems combining SQL, Python, and ML fundamentals. You'll work through live coding exercises and discuss product case scenarios. For Staff level, expect more nuanced problems requiring optimization and architectural thinking.

Tips & Advice

Practice 2-3 data manipulation and SQL problems before the interview, aiming to solve them within 7-8 minutes. Focus on clear communication while coding—explain your approach, consider edge cases, and discuss trade-offs. Optimize for both correctness and efficiency. Be prepared to explain your solution, alternative approaches, and performance implications. For Staff level, demonstrate systems thinking and ability to handle ambiguous requirements. Use the interactive platform effectively and ask clarifying questions to scope problems correctly.

Focus Topics

Machine Learning Fundamentals and Model Evaluation

Discuss supervised and unsupervised learning concepts, model evaluation metrics (accuracy, precision, recall, F1, AUC-ROC), bias-variance trade-off, cross-validation, and overfitting/underfitting. Be able to recommend appropriate metrics based on business problem.

Practice Interview

Study Questions

Product Case Analysis Framework

Analyze hypothetical product scenarios by defining success metrics, collecting relevant data, performing analysis, and recommending actions. Example: analyzing user engagement drop in a Microsoft product or designing metrics to track model performance in production.

Practice Interview

Study Questions

Statistical Concepts and Hypothesis Testing

Understand fundamental statistics: mean, median, variance, distributions, correlation, hypothesis testing (null/alternative hypotheses, p-values, Type I/II errors), confidence intervals, and Bayes' theorem. Apply these concepts to real-world problems like A/B testing evaluation.

Practice Interview

Study Questions

Python for Data Manipulation and Analysis

Demonstrate proficiency with pandas (groupby, merge, pivot operations), NumPy (array operations), and data transformation patterns. Solve problems involving data cleaning, reshaping, and feature extraction. For Staff level, show understanding of efficiency considerations and when to use different data structures.

Practice Interview

Study Questions

SQL Query Optimization and Complex Joins

Master advanced SQL including multiple joins, window functions (ROW_NUMBER, RANK, LAG, LEAD), aggregations, subqueries, and CTEs. Optimize query performance for large datasets. Handle complex business logic through SQL queries like customer cohort analysis, retention calculations, and multi-step aggregations.

Practice Interview

Study Questions

Coding and Algorithm Round (Onsite/Virtual)

60 min5 focus topicstechnical

What to Expect

A 60-minute technical interview focusing on data structure selection, algorithmic problem-solving, and code quality. You'll solve problems involving arrays, strings, linked lists, trees, graphs, and sorting/searching algorithms in the context of real-world data scenarios. For Staff level, interviewers assess not just correctness but your ability to optimize solutions, reason about trade-offs, and mentor others on approach.

Tips & Advice

Use an online platform like Google Colab or LeetCode during practice. For each problem, first clarify requirements and constraints with the interviewer. State your approach before coding. Consider multiple solutions (brute force vs optimized) and discuss time/space complexity trade-offs explicitly. Write clean, readable code with meaningful variable names. Test your solution with edge cases. For Staff level, demonstrate mentorship by explaining design decisions and considering scalability. If you get stuck, think aloud and ask for hints rather than remaining silent. Be prepared to optimize further after an initial correct solution.

Focus Topics

Handling Large-Scale Data Problems

Discuss approaches for problems involving millions or billions of data points. Address memory constraints, distributed processing considerations, and when to use approximation algorithms vs exact solutions.

Practice Interview

Study Questions

Real-world Data Processing Patterns

Solve problems that simulate real data scenarios: streaming data processing, handling missing values, deduplication, aggregation patterns, and data transformation pipelines. Connect algorithmic concepts to practical data engineering challenges.

Practice Interview

Study Questions

Time and Space Complexity Optimization

Start with a working solution and progressively optimize it. Reduce time complexity from O(n²) to O(n log n) or better where possible. Optimize space usage without sacrificing readability. Explain your optimization strategy to the interviewer.

Practice Interview

Study Questions

Algorithm Complexity Analysis and Trade-offs

Analyze time complexity (Big O notation) and space complexity for algorithms. Compare multiple approaches and articulate the trade-offs between time and space. For Staff level, discuss when to accept higher complexity for better readability or maintainability. Understand practical implications of complexity in production environments.

Practice Interview

Study Questions

Advanced Data Structure Selection and Optimization

Demonstrate deep understanding of when to use arrays, linked lists, stacks, queues, heaps, hash tables, trees, and graphs. For Staff level, discuss trade-offs between different data structures for specific problems, memory efficiency, and scalability considerations. Explain why certain choices lead to better algorithmic performance.

Practice Interview

Study Questions

SQL and Data Manipulation Round (Onsite/Virtual)

60 min5 focus topicstechnical

What to Expect

A 60-minute technical interview dedicated to SQL and data manipulation. You'll solve complex real-world data problems requiring advanced SQL queries, data transformation, and analytical reasoning. Problems may involve customer analytics, retention analysis, funnel analysis, or time-series data. For Staff level, expect problems requiring optimization of complex queries and architectural thinking about data pipelines.

Tips & Advice

Write SQL queries on platforms like DataInterview SQL pad or similar. Start with a clear understanding of the data schema and problem requirements. Write readable SQL with proper aliasing and formatting. Explain your approach before writing the full query. Consider multiple solutions and discuss performance implications. For Staff level, optimize queries for execution plans—think about index usage and join order. Be prepared to debug queries and handle edge cases. Practice common patterns: ranking, cohort analysis, retention calculations, funnel analysis. Time management is crucial; aim to write correct queries efficiently.

Focus Topics

Handling Structured and Unstructured Data in SQL

Work with JSON/nested data structures in SQL. Extract and manipulate semi-structured data. Handle NULL values appropriately. Understand data type conversions and string operations.

Practice Interview

Study Questions

Real-world Data Pipeline Scenarios

Solve problems simulating data pipeline stages: fact/dimension table design, slowly changing dimensions, data deduplication, incremental data loading logic, and data quality checks in SQL.

Practice Interview

Study Questions

Window Functions and Advanced Query Patterns

Master window functions (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM OVER, AVG OVER) for ranking, sequencing, and running calculations. Use PARTITION BY and ORDER BY effectively. Understand frame specifications (ROWS BETWEEN, ROWS PRECEDING). Apply to ranking problems, retention analysis, and time-series calculations.

Practice Interview

Study Questions

Data Aggregation and Performance Optimization

Write efficient aggregation queries using GROUP BY, HAVING, and multiple aggregation levels. Optimize for query performance considering indexing, join order, and column selection. Understand execution plans and how to interpret them.

Practice Interview

Study Questions

Complex Join Scenarios and Multi-table Queries

Write queries involving multiple joins (INNER, LEFT, RIGHT, FULL), CTEs (WITH clauses), subqueries, and self-joins. Understand join semantics deeply—when each type is appropriate and how to debug join logic.

Practice Interview

Study Questions

Machine Learning Theory and Applied Modeling Round (Onsite/Virtual)

70 min6 focus topicstechnical

What to Expect

A 60-75 minute technical interview evaluating deep machine learning expertise, model design, and practical implementation knowledge. You'll discuss ML concepts, select appropriate models for business problems, design feature engineering strategies, evaluate models rigorously, and consider deployment considerations. For Staff level, expect questions about designing ML systems at scale, mentoring others on ML decisions, and balancing theoretical rigor with practical constraints.

Tips & Advice

Review core ML concepts: supervised vs unsupervised learning, regression vs classification, probabilistic models, and ensemble methods. Prepare concrete project examples where you designed features, trained models, evaluated performance, and deployed solutions. Practice explaining trade-offs between model complexity, interpretability, and performance. For Staff level, emphasize how you've designed ML systems for production, handled real-world constraints (data imbalance, missing data, concept drift), and mentored junior colleagues on ML decisions. Understand when to use simple models vs complex ones. Be prepared to code a simple model (e.g., logistic regression) from scratch in Python if asked. Discuss evaluation metrics appropriate to business context, not just accuracy.

Focus Topics

Model Validation and Cross-Validation Strategies

Understand different cross-validation approaches (k-fold, stratified, time-series split) and when each is appropriate. Discuss holdout validation, nested cross-validation, and validation on out-of-distribution data. For Staff level, explain how to validate models in production and detect model drift.

Practice Interview

Study Questions

A/B Testing and Experimental Design

Design experiments to validate ML model improvements. Understand sample size calculation, statistical power, Type I/II errors, and significance testing. Discuss when to use online vs offline evaluation. Address multi-armed bandit problems and continuous experimentation frameworks.

Practice Interview

Study Questions

Distributed ML and Scalability Considerations

Discuss training models on large datasets using distributed frameworks (Spark, TensorFlow distributed). Understand parallelization strategies, data partitioning, and communication overhead. Address scalability of inference in production systems.

Practice Interview

Study Questions

Model Selection and Evaluation Metrics

Understand different model families (linear models, tree-based, neural networks, clustering algorithms) and when each is appropriate. Select evaluation metrics based on problem context: accuracy, precision, recall, F1, AUC-ROC, RMSE, MAPE. Explain why different metrics matter for different business problems (e.g., precision for fraud detection vs recall for disease screening).

Practice Interview

Study Questions

Bias-Variance Tradeoff and Regularization

Understand the bias-variance tradeoff conceptually and practically. Discuss overfitting and underfitting with real examples. Explain regularization techniques (L1, L2, dropout, early stopping) and when to apply them. Know how to diagnose whether a model has high bias or high variance from learning curves.

Practice Interview

Study Questions

Feature Engineering for Production Systems

Design features that improve model performance and generalize well. Discuss handling missing values, outliers, categorical encoding, scaling, and creating domain-specific features. For Staff level, emphasize scalable feature engineering, avoiding data leakage, and maintaining feature consistency between training and serving. Discuss feature selection and dimensionality reduction.

Practice Interview

Study Questions

Product Sense and Business Case Interview (Onsite/Virtual)

60 min5 focus topicscase study

What to Expect

A 60-minute interview evaluating your ability to think like a product leader and translate business challenges into data solutions. You'll analyze product scenarios, define success metrics, propose analytical approaches, and communicate insights to stakeholders. For Staff level, expect complex, ambiguous problems requiring strategic thinking about tradeoffs and ability to drive recommendations across functions.

Tips & Advice

Approach product cases systematically: (1) clarify the business context and goals, (2) define relevant metrics and KPIs, (3) frame the analytical approach, (4) identify data sources and required analysis, (5) propose hypotheses and how to test them, (6) communicate recommendations with clear justification. For Staff level, demonstrate strategic thinking—discuss trade-offs, acknowledge constraints, and explain how your analysis influences cross-functional decisions. Ask clarifying questions to scope ambiguous problems. Use frameworks like ORCA (Opportunity, Recommendation, Constraints, Approach) to structure thinking. Think about business impact, not just technical elegance. Consider how you'd communicate findings to non-technical stakeholders.

Focus Topics

Building Cross-functional Collaboration Strategies

Discuss how you'd work with product managers, engineers, and business stakeholders to solve problems. Address potential disagreements and how you'd drive consensus. For Staff level, demonstrate mentorship of others through technical discussions.

Practice Interview

Study Questions

Communicating Insights to Diverse Stakeholders

Present findings clearly to both technical and non-technical audiences. Use visualizations effectively. Focus on business implications, not technical details. Tailor communication to audience (engineers need different info than business leaders). Address questions directly and admit when you don't know rather than guessing.

Practice Interview

Study Questions

Translating Business Problems to ML/Analytics Solutions

Take ambiguous business challenges and frame them as analytical problems. Identify whether the problem needs prediction, classification, clustering, or causal inference. Propose appropriate models or analysis techniques. Discuss data requirements and timeline feasibility.

Practice Interview

Study Questions

Data-Driven Decision Making and Impact Analysis

Analyze business scenarios using data to inform decisions. Quantify impact of proposed changes. Understand causality vs correlation and how to isolate causal effects. Discuss trade-offs between options with supporting data.

Practice Interview

Study Questions

Product Metrics Definition and Analysis

Define appropriate metrics to measure product success (engagement, retention, conversion, monetization, quality). Distinguish between leading and lagging indicators. Understand tradeoffs between metrics. For Staff level, propose comprehensive metric frameworks and discuss how metrics interact. Analyze metric trends and decompose changes into drivers.

Practice Interview

Study Questions

Behavioral and Cultural Fit Interview (Onsite/Virtual)

50 min6 focus topicsbehavioral

What to Expect

A 45-60 minute interview conducted by a senior team member, focusing on soft skills, teamwork, alignment with Microsoft values (Growth Mindset, One Microsoft, Customer Obsession), and cultural fit. You'll discuss past experiences demonstrating leadership, handling ambiguity, collaboration, learning, and resilience. For Staff level, expect questions about influencing without authority, mentoring others, navigating organizational complexity, and contributing to strategic decisions.

Tips & Advice

Use the STAR methodology (Situation, Task, Action, Result) to structure all responses. Prepare 5-7 concrete examples that demonstrate: leadership and mentorship, handling conflict or disagreement, learning from failure, collaborative success, driving change, and impact on teams/organizations. For Staff level, emphasize strategic impact, mentoring trajectory, and ability to influence cross-functional initiatives. Research Microsoft's values deeply and reference them naturally in examples. Show genuine passion for Microsoft's mission and products. Address teamwork and adaptability specifically. Be honest about mistakes and focus on learnings. Ask thoughtful closing questions about team culture and growth opportunities. Avoid rehearsed-sounding responses—be authentic.

Focus Topics

Customer Obsession and Business Impact

Provide examples of understanding customer/user needs deeply and making decisions prioritizing customer value. For Staff level, discuss how customer obsession has guided your technical choices and team strategy.

Practice Interview

Study Questions

Handling Ambiguity and Driving Clarity

Provide examples of working on ill-defined problems, managing incomplete information, and creating structure. For Staff level, discuss how you've helped teams navigate ambiguity, defined scope, and moved forward despite uncertainty.

Practice Interview

Study Questions

Learning from Failure and Growth Mindset

Discuss a significant failure or mistake you made, what you learned, and how it shaped your approach. For Staff level, discuss how failures have informed your leadership and mentorship philosophy. Show genuine growth from setbacks.

Practice Interview

Study Questions

Cross-functional Collaboration and Influencing Without Authority

Describe scenarios where you collaborated with peers and stakeholders without direct authority. Discuss resolving disagreements, building consensus, and driving decisions across organizations. For Staff level, emphasize influencing senior leaders and navigating organizational dynamics.

Practice Interview

Study Questions

Leadership and Mentorship Examples

Discuss specific instances where you led technical initiatives, mentored junior colleagues, and grew your team's capabilities. For Staff level, emphasize scaling impact through others. Describe how you've developed talent, created learning opportunities, and elevated team performance.

Practice Interview

Study Questions

Microsoft Values and Cultural Alignment

Understand and demonstrate Microsoft's core values: Growth Mindset (embracing learning, continuous improvement, resilience), One Microsoft (cross-team collaboration, breaking silos), and Customer Obsession (understanding customer needs, delivering value). Provide examples of embodying each value.

Practice Interview

Study Questions

Frequently Asked Data Scientist Interview Questions

Bias Variance Tradeoff and Model SelectionMediumTechnical

92 practiced

Discuss how bagging (e.g., random forest) and boosting (e.g., XGBoost) differentially affect bias and variance. Give examples of data/noise regimes where each method is preferable and explain why boosting can sometimes increase variance despite reducing bias.

Model Evaluation and ValidationEasyTechnical

87 practiced

Given the following confusion matrix for a binary classifier:

| Actual \ Predicted | Positive | Negative ||--------------------|----------|----------|| Positive | 70 | 30 || Negative | 20 | 880 |

Compute precision, recall, specificity, and accuracy. Then interpret what the model is doing well and where it is failing in plain language for a stakeholder who is not technical.

Advanced Querying with Structured Query LanguageMediumTechnical

20 practiced

Given price_timeseries(symbol, trade_time TIMESTAMP, price DECIMAL), write SQL to compute both a 7-day moving average and a 30-day moving average per symbol aligned to each trade_time using window functions. Explain the choice of frame (ROWS vs RANGE) and performance implications on large time series data.

Sample Answer

Approach: use windowed AVG over each symbol partition ordered by trade_time, with a time-based frame so the window includes rows whose timestamps fall within the last 7 (or 30) days up to the current trade_time. For time windows use RANGE ... INTERVAL to express "last N days"; ROWS would instead refer to a fixed number of rows (not correct for irregular time series).

SQL (Postgres-style):

sql

SELECT
  symbol,
  trade_time,
  price,
  AVG(price) OVER (
    PARTITION BY symbol
    ORDER BY trade_time
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
  ) AS ma_7d,
  AVG(price) OVER (
    PARTITION BY symbol
    ORDER BY trade_time
    RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW
  ) AS ma_30d
FROM price_timeseries;

Key points / reasoning:- PARTITION BY symbol scopes the computation per symbol.- ORDER BY trade_time plus RANGE BETWEEN INTERVAL 'N days' PRECEDING AND CURRENT ROW yields a logical time window covering N+1 days including current timestamp (6 days preceding + current = 7-day window).- Use RANGE for time-based windows because ROWS N PRECEDING would pick the last N physical rows (wrong if sampling frequency varies).

Edge cases & caveats:- If multiple rows share the same trade_time, RANGE treats them all as within the same logical boundary; if you want deterministic tie-breaking, add a unique tie column to ORDER BY.- Sparse data: a 7-day window may include fewer than expected rows — this is correct for time-based averages.- Timezones: ensure trade_time is consistent (use TIMESTAMPTZ if needed).

Performance implications on large time series:- RANGE frames are logically convenient but may be more expensive because the DB must identify rows in a time interval for each row; optimizers differ across engines.- ROWS frames are cheaper when you truly need fixed-row windows (uses offset pointers).- For large datasets, improve performance by: - Partitioning the table by symbol and range (e.g., by month) so scans are limited. - Creating a composite index on (symbol, trade_time) so range scans are efficient. - Pre-aggregating to daily buckets if intra-day detail isn’t required. - Using cumulative SUM/COUNT (running totals) and subtracting earlier cumulative values: compute cumulative sum ordered by trade_time, then for each row find the last trade_time < current_time - N days (via lateral join or binary search on array of times) to compute window sum in O(1) per row; this reduces repeated scanning. - Consider materialized incremental views for frequently queried moving averages.

Complexity: naive window evaluation is effectively O(n) per partition if the engine maintains sliding-window state (good engines do), but without optimizations it can be costly. Test on representative data and use explain/analyze to confirm plan.

Hypothesis Testing and InferenceHardTechnical

28 practiced

Implement, in Python, a bootstrap-based hypothesis test to compute a two-sided p-value for the difference in medians between two independent samples. Your function should accept two numpy arrays and number_of_bootstraps, and must return the bootstrap p-value and a bootstrap percentile confidence interval for the median difference. Comment on computational considerations and reproducibility.

Sample Answer

Approach: compute observed median difference d_obs = median(x) - median(y). For the p-value we generate bootstrap replicates under the null hypothesis by recentring both groups to the common pooled median (so any observed difference is due to sampling variability) and resampling within groups with replacement. For the confidence interval we generate bootstrap replicates of the raw difference (no recentering) and take the percentile interval.

python

import numpy as np

def bootstrap_median_test(x, y, number_of_bootstraps=10000):
    """
    Two-sided bootstrap test for difference in medians between two independent samples.
    Returns: p_value, (ci_lower, ci_upper)
    - x, y: 1D numpy arrays
    - number_of_bootstraps: int
    Note: For reproducibility, set np.random.seed(...) before calling.
    """
    x = np.asarray(x)
    y = np.asarray(y)
    if x.ndim != 1 or y.ndim != 1:
        raise ValueError("Inputs must be 1D arrays")
    n_x, n_y = len(x), len(y)
    if n_x == 0 or n_y == 0:
        raise ValueError("Both samples must be non-empty")

    # observed statistic
    med_x = np.median(x)
    med_y = np.median(y)
    d_obs = med_x - med_y

    # --- bootstrap CI (percentile) using resampling within groups ---
    diffs = np.empty(number_of_bootstraps)
    for i in range(number_of_bootstraps):
        bx = np.random.choice(x, size=n_x, replace=True)
        by = np.random.choice(y, size=n_y, replace=True)
        diffs[i] = np.median(bx) - np.median(by)
    alpha = 0.05
    ci_lower = np.percentile(diffs, 100 * (alpha/2))
    ci_upper = np.percentile(diffs, 100 * (1 - alpha/2))

    # --- bootstrap under null: recentre to pooled median then resample within groups ---
    pooled_med = np.median(np.concatenate([x, y]))
    x_centered = x - med_x + pooled_med
    y_centered = y - med_y + pooled_med

    null_diffs = np.empty(number_of_bootstraps)
    for i in range(number_of_bootstraps):
        bx = np.random.choice(x_centered, size=n_x, replace=True)
        by = np.random.choice(y_centered, size=n_y, replace=True)
        null_diffs[i] = np.median(bx) - np.median(by)

    # two-sided p-value: proportion of |null_diff| >= |d_obs|
    p_value = np.mean(np.abs(null_diffs) >= np.abs(d_obs))

    return p_value, (ci_lower, ci_upper)

Key points and reasoning:- Recentring to pooled median enforces the null that medians are equal while preserving within-group variability.- CI uses the percentile bootstrap of the raw statistic, which is simple and commonly used for medians.Computational considerations:- O(B*(n_x + n_y)) time and O(B) memory to store replicates (can stream to reduce memory).- Median computation is O(n) per replicate; for very large B or large n, vectorized or compiled approaches (numba/C) or subsampling can accelerate.Reproducibility:- This function uses numpy's global RNG; to reproduce results set np.random.seed(seed) before calling, or modify the function to accept a Generator/seed.

Advanced SQL Window FunctionsMediumTechnical

106 practiced

Write a SQL query to compute week-over-week percentage change in purchases per product using LAG and window aggregates. Include how you handle weeks where the previous week's value is NULL or zero and ensure you avoid division-by-zero errors.

Sample Answer

Approach: aggregate purchases per product by calendar week, then use LAG() over product partitions to get prior-week value. Compute percent change with NULLIF to avoid division-by-zero; use CASE to handle NULL prior-week values (either return NULL or a sentinel like 'inf').

sql

WITH weekly AS (
  SELECT
    product_id,
    DATE_TRUNC('week', purchase_date)::date AS week_start,
    COUNT(*) AS purchases  -- or SUM(amount) if using revenue
  FROM purchases
  GROUP BY 1,2
)
SELECT
  product_id,
  week_start,
  purchases AS this_week,
  LAG(purchases) OVER (PARTITION BY product_id ORDER BY week_start) AS prev_week,
  CASE
    WHEN LAG(purchases) OVER (PARTITION BY product_id ORDER BY week_start) IS NULL THEN NULL
    WHEN LAG(purchases) OVER (PARTITION BY product_id ORDER BY week_start) = 0 THEN NULL
    ELSE ROUND( (purchases - LAG(purchases) OVER (PARTITION BY product_id ORDER BY week_start)) * 100.0
                / NULLIF(LAG(purchases) OVER (PARTITION BY product_id ORDER BY week_start), 0), 2)
  END AS pct_change
FROM weekly
ORDER BY product_id, week_start;

Key points:- DATE_TRUNC groups by calendar week.- LAG() retrieves prior-week value per product.- NULLIF(prev, 0) prevents division-by-zero; CASE ensures we return NULL (or choose to return 'inf' / large value) when previous is 0 or missing.- Round to 2 decimals for readability.

Edge cases:- Weeks with no rows for a product: they won't appear; if you need continuous weekly rows, generate a calendar of weeks and LEFT JOIN.- Decide business rule for prev = 0 (NULL, 'infinite', or specific business label).Alternative: compute rolling sums with SUM(...) OVER(...) if weeks can be sparse.

A and B Test DesignMediumTechnical

59 practiced

Explain alpha-spending and group-sequential designs for experiments. Compare Pocock and O'Brien-Fleming boundaries, describing how significance thresholds change across interim looks and the practical implications for speed vs conservativeness in product experiments.

Applying Data Science Techniques to Business ProblemsEasyTechnical

61 practiced

Your company is deciding between building a complex machine-learning churn model and implementing a simple rule-based alert using recent billing failures and inactivity. List the factors you would consider (data availability, interpretability, maintenance cost, latency, expected ROI) and propose a decision framework and a phased approach for choosing between simple rules and a production ML model.

Model Evaluation and ValidationEasyTechnical

93 practiced

You built a 5-class medical diagnosis classifier where one condition is rare but especially dangerous to miss. Walk through how you'd aggregate the per-class F1 scores into a single number to report, and why picking the wrong aggregation could hide poor performance on that rare, high-stakes condition.

Sample Answer

When you have per-class F1 scores and need to report one number, the two common ways to combine them are macro F1 and weighted F1 (a third option, micro F1, works differently: it pools all the TP/FP/FN counts across classes first and then computes one F1, rather than averaging per-class F1s).

- Macro F1: average the per-class F1 scores with every class weighted equally, regardless of how many examples that class has. Formula: F1_macro = (F1_class1 + F1_class2 + ... + F1_classN) / N.- Weighted F1: average the per-class F1 scores, but weight each class by its support (how many true examples of that class exist). Formula: F1_weighted = sum over classes of (support_class / total_examples) * F1_class. Common and rare classes contribute in proportion to how often they occur.

Worked example: a 5-class classifier over 1,000 patients, where condition E is rare (only 20 patients) but dangerous to miss.

| Class | Support | F1 score ||-------|---------|----------|| A | 400 | 0.95 || B | 300 | 0.92 || C | 200 | 0.90 || D | 80 | 0.85 || E | 20 | 0.40 |

Macro F1 = (0.95 + 0.92 + 0.90 + 0.85 + 0.40) / 5 = 4.02 / 5 = 0.804

Weighted F1 = (400*0.95 + 300*0.92 + 200*0.90 + 80*0.85 + 20*0.40) / 1,000= (380 + 276 + 180 + 68 + 8) / 1,000 = 912 / 1,000 = 0.912

If I only reported weighted F1 (0.912), it looks like a strong, reliable model. Macro F1 (0.804) exposes that one class, the rare and dangerous condition E, is performing badly (0.40 F1), because macro treats all 5 classes as equally important instead of letting the 400-patient class A drown it out. Since condition E is rare but especially costly to miss, weighted F1 would hide exactly the failure that matters most here.

So for this scenario I'd report macro F1 as the headline metric (or at minimum report both macro and the per-class F1 for the high-stakes rare condition), because picking weighted F1 alone would make a model with a dangerous blind spot look excellent.

Advanced Querying with Structured Query LanguageHardTechnical

25 practiced

You have a query that joins orders -> order_items -> discounts and aggregates revenue by customer, but results are inflated due to join duplication. Show how to refactor the query using subqueries or CTEs to avoid double-counting order item amounts when discounts are at the order level. Explain why the original plan caused duplication and how your refactor fixes it.

Sample Answer

Original problem: joining orders -> order_items -> discounts where discounts are at order level causes each order-level discount row to multiply rows from order_items, inflating aggregates.

Problematic query (simplified):

sql

SELECT
  o.customer_id,
  SUM(oi.price * oi.quantity * (1 - d.percent)) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
LEFT JOIN discounts d ON d.order_id = o.id
GROUP BY o.customer_id;

Why duplication happens:- If an order has N items and M discount rows (e.g., multiple discount records or a single row that should apply once), the join produces N*M rows. The discount factor repeats per item-row, or if multiple discounts exist it's applied multiple times, overcounting.

Refactor: compute order-level values once (aggregate items per order), then join discounts and combine at order granularity (CTE or subquery):

sql

WITH items_per_order AS (
  SELECT
    order_id,
    SUM(price * quantity) AS items_total
  FROM order_items
  GROUP BY order_id
),
best_discount AS (
  -- pick appropriate discount per order (example: max percent or single active)
  SELECT order_id, MAX(percent) AS percent
  FROM discounts
  GROUP BY order_id
)
SELECT
  o.customer_id,
  SUM(ipo.items_total * COALESCE(1 - bd.percent, 1)) AS revenue
FROM orders o
LEFT JOIN items_per_order ipo ON ipo.order_id = o.id
LEFT JOIN best_discount bd ON bd.order_id = o.id
GROUP BY o.customer_id;

Why this fixes it:- items_total is computed once per order so no multiplicative row explosion.- discounts are reduced to one row per order (via aggregation/selection) so they apply exactly once.- Keeps aggregation logic clear and avoids double-counting.

Performance notes:- Pre-aggregating reduces join size. Ensure proper indexes on order_items(order_id) and discounts(order_id). If discounts have complex rules, compute discount amount per order in the discount CTE to keep final join simple.

Hypothesis Testing and InferenceHardTechnical

26 practiced

Design a Bayesian A/B testing approach for binary conversion outcomes. Specify suitable priors and likelihood, explain how you would compute posterior probabilities that variant beats control, recommend stopping rules and decision thresholds, and describe how you would present posterior summaries and expected financial impact to stakeholders. Discuss sensitivity to prior choices.

Sample Answer

Approach overview- Model each arm’s conversion rate p using a Binomial likelihood and a Beta prior (conjugate, interpretable). This yields closed-form Beta posteriors and lets us compute P(p_variant > p_control) and full posterior uplift distribution for decisions.

Model specification- Likelihood: for arm i (control C, variant V) with ni trials and xi successes: xi ~ Binomial(ni, pi).- Priors (recommendations): - Uninformative baseline: Beta(1,1) (uniform) if no prior data. - Weakly informative: Beta(2,2) to shrink extreme probabilities slightly. - Skeptical prior (centered on current baseline p0): Beta(α, β) with mean α/(α+β)=p0 and small prior sample size (α+β ~ 10) if you trust historical baseline. Choose prior based on domain knowledge; always run sensitivity checks.

Posterior- Conjugacy gives pi | data ~ Beta(αi + xi, βi + ni - xi).- Compute probability variant beats control: - Analytic via Beta-Beta integral (closed form via summation) or Monte Carlo:

python

import numpy as np
from numpy.random import default_rng
rng = default_rng()

def posterior_prob_beats(a_c, b_c, a_v, b_v, samples=200_000):
    pc = rng.beta(a_c, b_c, size=samples)
    pv = rng.beta(a_v, b_v, size=samples)
    return (pv > pc).mean()

# example: prior Beta(1,1), observed x/n for each arm
a_c, b_c = 1 + x_c, 1 + n_c - x_c
a_v, b_v = 1 + x_v, 1 + n_v - x_v
p = posterior_prob_beats(a_c, b_c, a_v, b_v)

- Also compute posterior of absolute uplift D = pV - pC and relative uplift R = pV/pC by sampling; report mean, median, 95% credible interval, and P(D > δ) for a business-relevant minimum effect δ.

Decision rules & stopping- Bayesian sequential updating is coherent; pre-specify stopping/decision rules to avoid cognitive bias: - Efficacy rule: stop and ship if P(pV > pC) ≥ 0.975 AND expected net benefit > 0 (see below). - Futility rule: stop if P(pV > pC) ≤ 0.05 OR P(D > δ) ≤ 0.1. - Alternatively use decision-theoretic threshold: compute expected loss/gain of choosing V vs C and pick action minimizing posterior expected loss. This naturally accounts for asymmetric costs.- Recommended thresholds are business-dependent; 0.95/0.975 common for strong evidence, but tie to cost of shipping a bad variant.- Pre-specify maximum sample size or time horizon to control operational risk.

Expected financial impact- For each posterior sample, compute expected incremental revenue = (pV - pC) * value_per_conversion * expected future traffic.- Summarize: posterior mean expected gain, 95% credible interval, and probability expected gain > 0.- Present an expected value table: EV of shipping V, EV of keeping C, and expected regret of a wrong decision. Show break-even conversion uplift given cost and traffic.

Presentation to stakeholders- Key numbers: posterior mean conversion rates, 95% credible intervals, P(variant beats control), mean and CI of absolute uplift, P(uplift > business_minimum), expected monthly revenue impact, risk scenarios.- Visuals: posterior density plots for pC and pV, histogram of uplift, cumulative expected gain curve vs traffic, decision boundary annotated.- Story: translate probabilities into business language ("There is a 92% probability that Variant increases conversions; expected monthly uplift is $X (90% CI $A–$B). Risk of negative impact is 8%.")

Sensitivity and robustness- Run sensitivity analysis with alternative priors: Beta(1,1), Beta(2,2), skeptical Beta centered at baseline with effective sample 10, optimistic prior. Report how P(pV>pC) and expected gain change.- Perform prior predictive checks to ensure priors produce plausible data.- If results depend strongly on prior, either collect more data or be transparent: present both skeptical and optimistic posterior conclusions.

Additional considerations- Hierarchical model if running many similar A/B tests or multiple segments to borrow strength.- Account for multiple looks via decision-theoretic framing; Bayesian sequential analysis doesn’t require alpha-spending but requires pre-specified decision criteria.- Consider practical confounders (noncompliance, delayed conversions) and model them (censoring models, survival/Bernoulli with delay).

Why this is robust- Beta-Binomial is simple, fast, interpretable, and supports sequential updates.- Decision-theoretic thresholds align statistical evidence with business consequences rather than arbitrary p-values.- Sensitivity checks and expected-value reporting communicate uncertainty and risk to stakeholders.

Practice Data Scientist questions across all topics

Additional Information

Want to create your own tailored preparation guide using our deep research?

Get Started for Free

Interview-Ready Courses

Visual-first, interactive, structured learning paths

Browse Data Scientist jobs

AI-enriched listings across hundreds of company career pages

Explore Jobs

Microsoft Data Scientist (Staff Level) Interview Preparation Guide 2026

Interview Process Overview

Interview Rounds

Recruiter Screening

What to Expect

Tips & Advice

Focus Topics

Motivation and Role Alignment

Practice Interview

Study Questions

Domain Expertise and Technical Depth

Practice Interview

Study Questions

Mentorship and Team Leadership

Practice Interview

Study Questions

Career Trajectory and Leadership Experience

Practice Interview

Study Questions

Technical Screen (Phone)

What to Expect

Tips & Advice

Focus Topics

Machine Learning Fundamentals and Model Evaluation

Practice Interview

Study Questions

Product Case Analysis Framework

Practice Interview

Study Questions

Statistical Concepts and Hypothesis Testing

Practice Interview

Study Questions

Python for Data Manipulation and Analysis

Practice Interview

Study Questions

SQL Query Optimization and Complex Joins

Practice Interview

Study Questions

Coding and Algorithm Round (Onsite/Virtual)

What to Expect

Tips & Advice

Focus Topics

Handling Large-Scale Data Problems

Practice Interview

Study Questions

Real-world Data Processing Patterns

Practice Interview

Study Questions

Time and Space Complexity Optimization

Practice Interview

Study Questions

Algorithm Complexity Analysis and Trade-offs

Practice Interview

Study Questions

Advanced Data Structure Selection and Optimization

Practice Interview

Study Questions

SQL and Data Manipulation Round (Onsite/Virtual)

What to Expect

Tips & Advice

Focus Topics

Handling Structured and Unstructured Data in SQL

Practice Interview

Study Questions

Real-world Data Pipeline Scenarios

Practice Interview

Study Questions

Window Functions and Advanced Query Patterns

Practice Interview

Study Questions

Data Aggregation and Performance Optimization

Practice Interview

Study Questions

Complex Join Scenarios and Multi-table Queries

Practice Interview

Study Questions

Machine Learning Theory and Applied Modeling Round (Onsite/Virtual)

What to Expect

Tips & Advice

Focus Topics