Summary
Microsoft data scientist technical screen, heavy SQL focus with some tricky edge cases built into the schema. Three-part question that escalated from a delivery rate calculation to a scenario-based switching analysis. Felt like a reasonable challenge but part (c) was a curveball I wasn't fully ready for.
Questions Asked(3)
The spam_folder filter tripped me up first.
Suggested Approach
Start by clearly defining the data pipeline: filter the Emails table to the last 7 days, join to Deliveries while excluding spam_folder rows and selecting the earliest delivered event per email, then compute the 5-minute delivery flag. Finally, aggregate by provider and recipient domain to compute the proportion, explicitly handling NULLs from missing delivery records as undelivered (0).
Scope & Filter the Emails Table
Filter the Emails table to only rows where send_time falls within the last 7 days (e.g., send_time >= CURRENT_TIMESTAMP - INTERVAL '7 days'). Extract the provider and recipient domain from the email address fields at this stage to keep downstream logic clean.
Prepare the Deliveries Table
Filter out any rows in the Deliveries table where event_type = 'spam_folder', then for each email_id select the earliest delivered event using MIN(event_time) WHERE event_type = 'delivered'. This correctly handles the bounce-then-deliver scenario by ignoring event ordering and focusing solely on the earliest delivered timestamp.
Join & Compute the 5-Minute Flag
LEFT JOIN the filtered Emails table to the prepared Deliveries CTE on email_id, so emails with no delivery record produce a NULL delivery time. Compute a binary flag: 1 if (earliest_delivered_time - send_time) <= 5 minutes, else 0 (treating NULL as 0 via COALESCE or a CASE statement).
Aggregate by Provider and Recipient Domain
GROUP BY provider and recipient_domain, then compute the proportion as SUM(delivered_within_5min_flag) / COUNT(*). Optionally include COUNT(*) as a volume column so stakeholders can assess statistical reliability of each group's proportion.
Validate & Communicate Assumptions
State your assumptions explicitly: 'delivered' is the only qualifying event type, spam_folder rows are excluded before finding the earliest event, missing records are treated as undelivered, and the 5-minute window is inclusive. Mention that you'd validate row counts at each CTE stage to catch unexpected data issues.
Key Points to Mention
Straightforward once you've already built the CTEs from part (a).
Suggested Approach
Break the problem into sequential filtering steps: first identify message IDs where the earliest delivery event is a bounce, then among those find a subsequent 'delivered' event, and finally compute the lag between the original send time and that first delivered event. Use window functions to rank events per message and apply conditional filtering to enforce the ordering constraint. Express the solution in SQL with clear CTEs to keep logic readable and auditable.
Clarify Schema & Assumptions
Identify the relevant tables (e.g., messages with send_time, events with message_id, event_type, event_timestamp). Confirm event_type values ('bounce', 'delivered') and whether a single message can have multiple events of the same type.
Rank Events Per Message
Use ROW_NUMBER() or RANK() partitioned by message_id and ordered by event_timestamp to label each event chronologically. This lets you reliably identify the 'first' delivery-related event per message.
Filter Messages Where First Event Is a Bounce
From the ranked events, keep only message IDs where the row ranked #1 has event_type = 'bounce'. This isolates the cohort of messages that initially bounced.
Find the First Subsequent 'Delivered' Event
Within the filtered cohort, identify the earliest 'delivered' event that occurs after the bounce. Join back to the messages table to retrieve the original send_time for lag calculation.
Apply the 5-Minute Threshold & Compute Lag
Filter to rows where the first delivered event timestamp minus the original send_time exceeds 5 minutes, then compute the lag in minutes using DATEDIFF or equivalent. Return message_id and the computed lag.
Key Points to Mention
This one took me a minute to even parse.
Suggested Approach
Treat this as a cumulative window analysis problem where you compute a running delivered-within-5-minutes rate for each email provider (Gmail vs. Outlook) restricted to the ms.com recipient domain, updating the cumulative rate after each send date. Then scan chronologically for the first date where Outlook's cumulative rate strictly exceeds Gmail's cumulative rate, and surface that date along with both rates as the justification.
Filter and Scope the Data
Restrict the dataset to rows where the recipient domain is ms.com and the send date falls within the specified 7-day window. Ensure you have columns for send date, email provider (Gmail vs. Outlook), whether the email was delivered within 5 minutes, and a total send count.
Aggregate Daily Counts per Provider
Group by send date and provider to compute two daily metrics: the number of emails delivered within 5 minutes and the total emails sent. This gives you a clean daily summary table with one row per (date, provider) combination.
Compute Cumulative Running Rates
For each provider, sort by send date and apply a cumulative sum over delivered counts and total counts separately, then divide to get the cumulative delivered-within-5-minutes rate at each date. Using cumulative sums (not averages of daily rates) correctly weights days with higher send volumes.
Identify the Crossover Date
Join the two cumulative rate series on send date and scan chronologically for the earliest date where Outlook's cumulative rate is strictly greater than Gmail's cumulative rate. This is the candidate switch date.
Output and Validate the Result
Return the identified date along with both cumulative rates (e.g., Outlook: 94.2%, Gmail: 91.8%) as the justification. Sanity-check by confirming no earlier date satisfies the strict inequality and that sample sizes on that date are large enough to be meaningful.
Key Points to Mention
Discussion(3)
Sign in to join the discussion.
The cumulative framing is what makes this genuinely hard to parse on first read. A per-day snapshot would be simple aggregation, but cumulative rates mean your denominator and numerator for any given date both include everything from the start of the 7-day window through that date. So you're not asking 'which provider was better today' but 'if I had committed to a provider from day one through today, which would have served me better cumulatively.'
The cleanest approach I can think of: build a base table at the send-date grain that flags each email as delivered-within-5-min or not (using the same logic from part a, scoped to ms.com recipients). Then for each calendar date in the window, compute a running SUM of delivered flags and a running COUNT of total sends, partitioned by provider, ordered by send date with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That gives you cumulative numerator and denominator per provider per date, so the rate is just their ratio.
Then you pivot or self-join to get Gmail and Outlook side by side on the same date row, filter to rows where Outlook's cumulative rate strictly exceeds Gmail's, and take the MIN(send_date). The 'strictly improves' wording matters, equal rates don't qualify.
Second-guessing the ROWS BETWEEN framing out loud is actually not the worst thing in a Microsoft phone screen. I've found that thinking out loud about window frame semantics, even if you stumble a bit, lands better than silently writing something that turns out to be wrong. The interviewers at that level generally care more about whether you understand what the frame is doing than whether you memorized the exact syntax.
Your instinct about filtering spam_folder early is exactly right, and it's the kind of thing that's easy to miss under pressure. The way I think about it: if you let spam_folder rows survive into your MIN(event_ts) calculation, you might pull a timestamp from a spam event that technically precedes the real delivered event, which completely breaks the 5-minute window check. The filter has to live inside the CTE that isolates delivered rows, not outside it.
The bounce-then-deliver rule is the other landmine. A lot of people read that and write something like MIN(event_ts) across all statuses, which grabs the bounce timestamp instead of the delivered one. You need to be surgical: filter to status = 'delivered', then MIN(event_ts), then join back to Emails on message_id and check whether that minimum delivered timestamp falls within send_time + 5 minutes. The bounce is irrelevant to the calculation, it just can't be allowed to pollute your event selection.
For the grouping, make sure your denominator is total emails sent in the window, not total emails with any delivery record. Missing delivery records are undelivered, so you need a LEFT JOIN from Emails to your cleaned delivery CTE, and NULLs from the join count as failures. I've seen people accidentally inner join and quietly drop the undelivered population, which inflates the rate. Microsoft screens at this level tend to have at least one schema detail that punishes exactly that kind of shortcut.
ROW_NUMBER() partitioned by message_id ordered by event_ts is the right call. Quick note though: if your Deliveries table can have ties on event_ts for the same message_id, ROW_NUMBER() will arbitrarily pick one, and that could matter if one tie is a bounce and the other isn't. Worth a one-sentence callout to the interviewer just to show you're thinking about it, even if they wave it off.