Wayfair·Data Scientist·Online Assessment (OA)·Intermediate

Intermediate

Jun 2026

Summary

Wayfair data scientist OA with two tasks back to back, SQL and Python. The SQL one had a tricky time-window filter that I almost misread, and the Python function had more edge cases than I expected for something that looked straightforward on the surface.

Questions Asked (2)

Given a customers table and a purchases table, write a SQL query to find the customer(s) with the highest purchase price within the earliest 10-year window of purchase history. Return all tied customers if applicable.

Data ModelingProduct Analytics & Metrics

Author's notes

The 10-year window thing tripped me up.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, determine the earliest purchase date in the purchases table to establish the start of the 10-year window. Then, filter purchases to those within that window, find the maximum purchase price, and return all customers whose purchase price equals that maximum. Use a subquery or CTE to compute the window and max price, then join back to get customer details.

Pro tip: Clarify whether the 10-year window is relative to the earliest purchase overall or per customer, and whether ties should be based on exact price or rounded values. Also, consider performance by indexing purchase_date and using window functions if supported.

1. Understand the schema and define the window

Identify the relevant columns: customer_id, purchase_date, and purchase_price. Determine the earliest purchase date across all purchases to set the start of the 10-year window.

2. Filter purchases within the window

Select all purchases where purchase_date is between the earliest date and the date 10 years later (inclusive). This narrows the dataset to the relevant period.

3. Find the maximum purchase price in the window

Compute the maximum purchase_price from the filtered purchases. This can be done with a subquery or a window function like MAX() OVER ().

4. Identify customers with that maximum price

Join the filtered purchases with the customers table and filter for rows where purchase_price equals the maximum. Use DISTINCT to avoid duplicates if a customer has multiple purchases at that price.

5. Return all tied customers

Ensure the query returns all customers who have at least one purchase at the maximum price within the window, including ties. Order the results if needed.

Key Points to Mention

Use of CTEs or subqueries to break down the problem into steps
Handling of date ranges and inclusive/exclusive boundaries
Consideration of ties and returning all tied customers
Potential need for DISTINCT to avoid duplicate customer rows
Performance considerations: indexing on purchase_date and purchase_price
Clarifying assumptions about the 10-year window (e.g., relative to earliest purchase overall)

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Write a Python function using pandas that cleans a student scores DataFrame by dropping students missing two or more scores, imputing remaining nulls with per-column medians, then returning the top 5 students sorted by math score descending, physics score descending, and student ID ascending.

Algorithms & Data StructuresTechnical Trade-offs

Author's notes

Looked easy.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the DataFrame structure and the definition of 'missing two or more scores' (i.e., at least two nulls per row). Then outline a pandas pipeline: drop rows with >=2 nulls, impute remaining nulls with column medians, sort by the specified columns, and return the top 5. Emphasize vectorized operations and avoid unnecessary loops.

Pro tip: Mention that you would use `df.isna().sum(axis=1) >= 2` to efficiently identify rows to drop, and that imputing with medians should be done after dropping to avoid bias from rows that will be removed. Also note that sorting by multiple columns with mixed ascending/descending order is straightforward with `sort_values`.

1. Clarify requirements and data assumptions

Confirm the DataFrame columns (e.g., student ID, math, physics, chemistry) and that 'missing two or more scores' means at least two null values in the score columns. Ask if student ID should be included in the null count (typically not).

2. Drop rows with two or more missing scores

Use `df.dropna(thresh=len(score_cols)-1)` or `df[df[score_cols].isna().sum(axis=1) < 2]` to remove students missing two or more scores. Ensure only score columns are considered.

3. Impute remaining nulls with per-column medians

For each score column, compute the median and fill nulls using `df[col].fillna(df[col].median(), inplace=True)` or `df.fillna(df.median())` for the score columns. Avoid using mean if outliers are present.

4. Sort and select top 5 students

Sort the DataFrame by math score descending, physics score descending, and student ID ascending using `df.sort_values(by=['math', 'physics', 'student_id'], ascending=[False, False, True])`. Then take the first 5 rows with `head(5)`.

5. Return the cleaned and sorted DataFrame

Return the resulting DataFrame, ensuring the index is reset if needed. Optionally, discuss handling ties or edge cases (e.g., fewer than 5 students remaining).

Key Points to Mention

Use vectorized pandas operations (e.g., `isna().sum(axis=1)`) instead of iterating over rows for performance.
Impute with median rather than mean to reduce the impact of outliers, and compute medians after dropping rows to avoid bias.
Specify the exact sort order: math descending, physics descending, student ID ascending, using the `ascending` parameter in `sort_values`.
Consider whether to include all score columns or only math and physics in the missingness threshold; clarify with the interviewer.
Handle potential edge cases: if fewer than 5 students remain after cleaning, return all of them; if ties occur, the sort order ensures deterministic output.
Mention that the function should be pure (no side effects) and return a new DataFrame, preserving the original data.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.