Coding questions · Microsoft
Query email logs for deliverability insights
This question evaluates SQL-based data manipulation skills focused on event-time analytics, time-windowed aggregations, temporal joins, handling missing or multiple event rows, and computing deliverability metrics; it is categorized under Data Manipulation (SQL/Python) for a Data Scientist role.
Using the schema and sample data below, write SQL to answer parts a–c. Assume timestamps are UTC. Today is 2025-09-01.
Schema:
- Emails(sent_id INT, message_id TEXT, provider TEXT, from_addr TEXT, to_domain TEXT, sent_ts TIMESTAMP, subject TEXT)
- Deliveries(message_id TEXT, status TEXT, event_ts TIMESTAMP, detail TEXT)
Sample data:
Emails
| sent_id | message_id | provider | from_addr | to_domain | sent_ts | subject |
| 1 | m1 | Gmail | candidate@gmail.com | ms.com | 2025-08-30 09:00 | avail slots |
| 2 | m2 | Gmail | candidate@gmail.com | ms.com | 2025-08-31 10:00 | follow-up |
| 3 | m3 | Outlook | candidate@outlook.com | ms.com | 2025-08-31 10:05 | follow-up alt |
| 4 | m4 | Gmail | candidate@gmail.com | ms.com | 2025-09-01 08:00 | confirmation |
| 5 | m5 | Outlook | candidate@outlook.com | ms.com | 2025-08-26 20:00 | initial reach |
| 6 | m6 | Gmail | candidate@gmail.com | otherco.com| 2025-08-27 09:00 | sanity check |
Deliveries
| message_id | status | event_ts | detail |
| m1 | bounced | 2025-08-30 09:01 | 550 5.7.1 |
| m1 | delivered | 2025-08-30 09:20 | |
| m3 | delivered | 2025-08-31 10:06 | |
| m4 | spam_folder | 2025-09-01 08:00 | filtered |
| m5 | delivered | 2025-08-26 20:01 | |
| m6 | delivered | 2025-08-27 09:01 | |
Note: message_id m2 has no rows in Deliveries (treat as undelivered).
Tasks: (a) For emails sent in the last 7 days relative to 2025-09-01 (window 2025-08-26 through 2025-09-01 inclusive), compute by provider and to_domain the proportion delivered within 5 minutes of sent_ts. Rules: use the earliest event with status = 'delivered'; ignore 'spam_folder'; treat messages with no delivery rows as undelivered; if a message bounces then later delivers, count it as delivered only if the delivered event occurs within 5 minutes of sent_ts. (b) Return all message_ids where the first observed event was a bounce and a later delivered event occurred more than 5 minutes after sent_ts; include the lag in minutes between sent_ts and the first delivered event. (c) For domain ms.com over the same window, determine the earliest calendar date on which switching from Gmail to Outlook would have strictly improved deliverability, based on same-day cumulative delivered-within-5-minutes rates up to each send. Output that date and show the cumulative rates used to justify the switch.
Compute provider-domain 5-minute delivery proportions
Using the tables below, for emails sent between '2025-08-26' and '2025-09-01' (inclusive), compute by provider and to_domain the proportion of messages that were delivered within 5 minutes of sent_ts.
Rules:
- For each message_id, consider only the earliest event in Deliveries with status = 'delivered'.
- Ignore events with status = 'spam_folder'.
- If a message has no rows in Deliveries, treat it as undelivered.
- If a message first bounces and later delivers, count it as delivered within 5 minutes only if the (earliest) delivered event occurs within 5 minutes of sent_ts.
Return one row per (provider, to_domain) with the proportion of emails delivered within 5 minutes in that date window.
Table Emails
| column | type | constraints |
|---|---|---|
| sent_id | INT | PRIMARY KEY |
| message_id | VARCHAR(20) | NOT NULL, UNIQUE |
| provider | VARCHAR(50) | NOT NULL |
| from_addr | VARCHAR(255) | NOT NULL |
| to_domain | VARCHAR(255) | NOT NULL |
| sent_ts | TIMESTAMP | NOT NULL |
| subject | VARCHAR(255) |
Sample data:
| from_addr | message_id | provider | sent_id | sent_ts | subject | to_domain |
|---|---|---|---|---|---|---|
| candidate@gmail.com | m1 | Gmail | 1 | 2025-08-30 09:00:00 | avail slots | ms.com |
| candidate@gmail.com | m2 | Gmail | 2 | 2025-08-31 10:00:00 | follow-up | ms.com |
| candidate@outlook.com | m3 | Outlook | 3 | 2025-08-31 10:05:00 | follow-up alt | ms.com |
| candidate@gmail.com | m4 | Gmail | 4 | 2025-09-01 08:00:00 | confirmation | ms.com |
| candidate@outlook.com | m5 | Outlook | 5 | 2025-08-26 20:00:00 | initial reach | ms.com |
| candidate@gmail.com | m6 | Gmail | 6 | 2025-08-27 09:00:00 | sanity check | otherco.com |
Table Deliveries
| column | type | constraints |
|---|---|---|
| message_id | VARCHAR(20) | NOT NULL |
| status | VARCHAR(50) | NOT NULL |
| event_ts | TIMESTAMP | NOT NULL |
| detail | VARCHAR(255) |
Sample data:
| detail | event_ts | message_id | status |
|---|---|---|---|
| 550 5.7.1 | 2025-08-30 09:01:00 | m1 | bounced |
| 2025-08-30 09:20:00 | m1 | delivered | |
| 2025-08-31 10:06:00 | m3 | delivered | |
| filtered | 2025-09-01 08:00:00 | m4 | spam_folder |
| 2025-08-26 20:01:00 | m5 | delivered | |
| 2025-08-27 09:01:00 | m6 | delivered |
Expected output
One row per (provider, to_domain) for emails sent between 2025-08-26 and 2025-09-01 inclusive, showing the fraction of those emails whose earliest 'delivered' event occurred within 5 minutes of sent_ts. Messages without a delivered event (or with only spam_folder events) count as not delivered within 5 minutes.
| proportion_within_5 | provider | to_domain |
|---|---|---|
| 0 | Gmail | ms.com |
| 1 | Gmail | otherco.com |
| 1 | Outlook | ms.com |
Hints
- First derive, per message_id, the timestamp of the earliest 'delivered' event using an aggregate or FILTER clause.
- Join that back to Emails, flag messages delivered within 5 minutes, then aggregate by provider and to_domain to compute the proportion.
Identify bounced-then-late-delivered messages
Using the same tables, return all message_ids where:
- The first observed event in Deliveries (earliest event_ts for that message_id) had status = 'bounced', and
- A later event with status = 'delivered' exists, and
- The first 'delivered' event occurred more than 5 minutes after the email's sent_ts.
For each such message_id, also return the lag in minutes between sent_ts and the first delivered event (as a numeric value). Consider all data in the tables.
Table Emails
| column | type | constraints |
|---|---|---|
| sent_id | INT | PRIMARY KEY |
| message_id | VARCHAR(20) | NOT NULL, UNIQUE |
| provider | VARCHAR(50) | NOT NULL |
| from_addr | VARCHAR(255) | NOT NULL |
| to_domain | VARCHAR(255) | NOT NULL |
| sent_ts | TIMESTAMP | NOT NULL |
| subject | VARCHAR(255) |
Sample data:
| from_addr | message_id | provider | sent_id | sent_ts | subject | to_domain |
|---|---|---|---|---|---|---|
| candidate@gmail.com | m1 | Gmail | 1 | 2025-08-30 09:00:00 | avail slots | ms.com |
| candidate@gmail.com | m2 | Gmail | 2 | 2025-08-31 10:00:00 | follow-up | ms.com |
| candidate@outlook.com | m3 | Outlook | 3 | 2025-08-31 10:05:00 | follow-up alt | ms.com |
| candidate@gmail.com | m4 | Gmail | 4 | 2025-09-01 08:00:00 | confirmation | ms.com |
| candidate@outlook.com | m5 | Outlook | 5 | 2025-08-26 20:00:00 | initial reach | ms.com |
| candidate@gmail.com | m6 | Gmail | 6 | 2025-08-27 09:00:00 | sanity check | otherco.com |
Table Deliveries
| column | type | constraints |
|---|---|---|
| message_id | VARCHAR(20) | NOT NULL |
| status | VARCHAR(50) | NOT NULL |
| event_ts | TIMESTAMP | NOT NULL |
| detail | VARCHAR(255) |
Sample data:
| detail | event_ts | message_id | status |
|---|---|---|---|
| 550 5.7.1 | 2025-08-30 09:01:00 | m1 | bounced |
| 2025-08-30 09:20:00 | m1 | delivered | |
| 2025-08-31 10:06:00 | m3 | delivered | |
| filtered | 2025-09-01 08:00:00 | m4 | spam_folder |
| 2025-08-26 20:01:00 | m5 | delivered | |
| 2025-08-27 09:01:00 | m6 | delivered |
Expected output
All message_ids whose first event is a bounce but that eventually deliver, where the first delivered event comes more than 5 minutes after sent_ts. Also include the lag in minutes until that first delivery.
| message_id | minutes_to_deliver |
|---|---|
| m1 | 20 |
Hints
- Use a window function (ROW_NUMBER) over Deliveries to identify the first event per message_id.
- Separately compute the first 'delivered' timestamp per message_id, then join to Emails and filter on the time difference.
Find earliest date where Outlook beats Gmail on fast delivery
For domain 'ms.com' and emails sent between '2025-08-26' and '2025-09-01' (inclusive), determine the earliest calendar date on which switching from Gmail to Outlook would have strictly improved deliverability, where deliverability is defined as the same-day cumulative rate of deliveries within 5 minutes of sent_ts.
Definitions and rules:
- Restrict to Emails with to_domain = 'ms.com' in that date window.
- A message is considered a fast delivery if its earliest 'delivered' event in Deliveries occurs within 5 minutes of sent_ts. Ignore 'spam_folder' events. Messages with no 'delivered' event are not fast.
- For each provider (Gmail and Outlook) and date, compute the same-day cumulative fast-delivery rate as: (cumulative count of fast deliveries up to each send that day) / (cumulative count of sends up to each send that day). Use the end-of-day cumulative rate for each provider-date.
- Identify the earliest date on which Outlook's end-of-day cumulative rate is strictly greater than Gmail's end-of-day cumulative rate for that same date (and both providers have at least one send to ms.com that day).
Return a single row with that date and the Gmail and Outlook end-of-day cumulative fast-delivery rates used to justify the switch.
Table Emails
| column | type | constraints |
|---|---|---|
| sent_id | INT | PRIMARY KEY |
| message_id | VARCHAR(20) | NOT NULL, UNIQUE |
| provider | VARCHAR(50) | NOT NULL |
| from_addr | VARCHAR(255) | NOT NULL |
| to_domain | VARCHAR(255) | NOT NULL |
| sent_ts | TIMESTAMP | NOT NULL |
| subject | VARCHAR(255) |
Sample data:
| from_addr | message_id | provider | sent_id | sent_ts | subject | to_domain |
|---|---|---|---|---|---|---|
| candidate@gmail.com | m1 | Gmail | 1 | 2025-08-30 09:00:00 | avail slots | ms.com |
| candidate@gmail.com | m2 | Gmail | 2 | 2025-08-31 10:00:00 | follow-up | ms.com |
| candidate@outlook.com | m3 | Outlook | 3 | 2025-08-31 10:05:00 | follow-up alt | ms.com |
| candidate@gmail.com | m4 | Gmail | 4 | 2025-09-01 08:00:00 | confirmation | ms.com |
| candidate@outlook.com | m5 | Outlook | 5 | 2025-08-26 20:00:00 | initial reach | ms.com |
| candidate@gmail.com | m6 | Gmail | 6 | 2025-08-27 09:00:00 | sanity check | otherco.com |
Table Deliveries
| column | type | constraints |
|---|---|---|
| message_id | VARCHAR(20) | NOT NULL |
| status | VARCHAR(50) | NOT NULL |
| event_ts | TIMESTAMP | NOT NULL |
| detail | VARCHAR(255) |
Sample data:
| detail | event_ts | message_id | status |
|---|---|---|---|
| 550 5.7.1 | 2025-08-30 09:01:00 | m1 | bounced |
| 2025-08-30 09:20:00 | m1 | delivered | |
| 2025-08-31 10:06:00 | m3 | delivered | |
| filtered | 2025-09-01 08:00:00 | m4 | spam_folder |
| 2025-08-26 20:01:00 | m5 | delivered | |
| 2025-08-27 09:01:00 | m6 | delivered |
Expected output
The earliest date in the window where, for ms.com, Outlook's end-of-day cumulative fast-delivery rate exceeded Gmail's end-of-day cumulative fast-delivery rate, along with those two rates. Based on the sample, this occurs on 2025-08-31: Gmail had 0 fast deliveries out of its ms.com sends that day, while Outlook had 1 fast delivery out of 1.
| gmail_rate | outlook_rate | switch_date |
|---|---|---|
| 0 | 1 | 2025-08-31 |
Hints
- First, for ms.com emails in the window, flag each message as delivered within 5 minutes or not using the earliest 'delivered' event.
- Use window functions partitioned by provider and sent_date to compute same-day cumulative counts and rates, then compare Gmail vs Outlook per date and pick the earliest date where Outlook’s rate is higher.