Write monthly touches and last-touch SQL
Company: Upstart
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You have two tables tracking marketing touches and downstream conversions. Write SQL to answer the three prompts below. Assume a warehouse like Postgres/BigQuery/Snowflake; months are calendar months in UTC; when multiple touches tie on timestamp, break ties by the highest touch_id; ignore touches strictly after a company's conversion.
Schema:
- marketing_touch(
touch_id BIGINT PRIMARY KEY,
company_id INT NOT NULL,
touch_timestamp TIMESTAMP NOT NULL,
channel VARCHAR,
campaign VARCHAR
)
- conversion(
company_id INT NOT NULL,
conversion_timestamp TIMESTAMP NOT NULL
)
Sample data (minimal, for reasoning/testing):
marketing_touch
| touch_id | company_id | touch_timestamp | channel | campaign |
| 1 | 100 | 2025-01-05 10:00:00 | Email | E1 |
| 2 | 100 | 2025-01-20 12:00:00 | Paid | P1 |
| 3 | 100 | 2025-02-01 09:00:00 | Direct | D1 |
| 4 | 101 | 2025-02-10 08:00:00 | Paid | P2 |
| 5 | 101 | 2025-02-10 08:00:00 | Email | E2 |
| 6 | 102 | 2025-02-28 23:59:59 | Referral | R1 |
conversion
| company_id | conversion_timestamp |
| 100 | 2025-02-10 00:00:00 |
| 101 | 2025-02-10 08:00:00 |
| 103 | 2025-03-01 12:00:00 |
Prompts:
1) For each month (YYYY-MM), output month and avg_touches_per_company = total touches in that month divided by the number of distinct companies that had at least one touch in that same month. Include months present in data only. Be explicit about handling companies with zero touches in a month (exclude them from the denominator).
2) For each company that converted (exists in conversion), return the last touch at or before its conversion_timestamp: company_id, conversion_timestamp, last_touch_timestamp, channel, campaign, and whether the last touch occurred in the same calendar month as the conversion. If a company has no touch at or before conversion, exclude it.
3) Count distinct companies where the last touch (as defined in #2) and the conversion occur in the same calendar month. Bonus: add a second output where you also require DATEDIFF in days between conversion_timestamp and last_touch_timestamp <= 45.
Edge cases to handle in your SQL: (a) multiple touches at the exact same timestamp for a company (pick the one with the highest touch_id); (b) touches after conversion (ignore for #2/#3); (c) companies present in conversion with no prior touches (exclude in #2/#3). Provide performant SQL (CTEs are fine) and briefly explain your tie-break and month-extraction logic.
Overview: This question evaluates proficiency in SQL-based data manipulation and time-based attribution, covering aggregations, joins, window functions, date truncation to calendar months, tie-breaking logic, and handling edge cases like post-conversion touches and duplicate timestamps.
Read the full Upstart Data Scientist interview experience this question came from
Monthly average touches per active company
You are given a table of marketing touches. For each calendar month (UTC) present in the touch data, return:
- month (format YYYY-MM)
- avg_touches_per_company = (total touches in that month) / (number of distinct companies that had >= 1 touch in that same month)
Important: Companies with zero touches in a month must be excluded from the denominator (i.e., only count companies that appear in that month). Return only months that exist in the data.
Tables
marketing_touch(touch_id BIGINT, company_id INT, touch_timestamp TIMESTAMP, channel VARCHAR(50), campaign VARCHAR(50))
Hints
- Group by DATE_TRUNC('month', touch_timestamp).
- The denominator should be COUNT(DISTINCT company_id) within each month, not all companies overall.
Last touch at or before conversion (with tie-break)
You are given two tables: marketing_touch (touches) and conversion (downstream conversions). For each company that appears in conversion, find the last marketing touch that occurred at or before its conversion_timestamp.
Return these columns:
- company_id
- conversion_timestamp formatted as `YYYY-MM-DD HH24:MI:SS`
- last_touch_timestamp formatted as `YYYY-MM-DD HH24:MI:SS`
- channel
- campaign
- same_calendar_month (boolean) = whether last_touch_timestamp and conversion_timestamp fall in the same UTC calendar month
Rules / edge cases:
- Ignore touches strictly after a company's conversion_timestamp.
- If multiple touches tie on the exact same timestamp for a company, pick the one with the highest touch_id.
- If a company has no touch at or before conversion, exclude it from the output.
Tables
marketing_touch(touch_id BIGINT, company_id INT, touch_timestamp TIMESTAMP, channel VARCHAR(50), campaign VARCHAR(50))
conversion(company_id INT, conversion_timestamp TIMESTAMP)
Hints
- Filter to touches with `touch_timestamp <= conversion_timestamp` before ranking.
- Use `ROW_NUMBER` ordered by `touch_timestamp DESC, touch_id DESC` to implement the tie-break.
Count companies where last touch and conversion are in same month (and <=45 day bonus)
Using the same two tables (marketing_touch and conversion) and the same definition of "last touch" as in Question #2 (last touch at or before conversion, tie-break by highest touch_id when timestamps tie), return a single-row result with:
- companies_same_month: count of distinct companies whose last touch and conversion are in the same UTC calendar month
- companies_same_month_and_le_45_days: count of distinct companies that satisfy the same-month requirement AND also have (conversion_timestamp - last_touch_timestamp) <= 45 days
Exclude converting companies that have no touch at or before conversion.
Tables
marketing_touch(touch_id BIGINT, company_id INT, touch_timestamp TIMESTAMP, channel VARCHAR(50), campaign VARCHAR(50))
conversion(company_id INT, conversion_timestamp TIMESTAMP)
Hints
- Reuse a CTE that finds the last eligible touch per converting company with ROW_NUMBER.
- For the 45-day condition in Postgres, you can compare timestamps using an INTERVAL (conversion_timestamp - last_touch_timestamp <= INTERVAL '45 days').