Summary
SQL-heavy technical screen for a Data Scientist role at Upstart. Three prompts all built around the same two-table schema, escalating from aggregation to attribution to a filtered count. The edge cases were the real test, not the queries themselves.
Questions Asked(3)
The denominator scoping is where people slip up.
Suggested Approach
Break the problem into two aggregation steps: first count the number of touches per company per month, then average those counts across only the companies that appear in each month. Emphasize that the denominator is the count of distinct active companies in that month, not the total company universe, which is the key nuance of this question.
Clarify the Schema and Requirements
Confirm the table structure (e.g., columns like company_id, touch_date, touch_id) and restate the key constraint: the denominator is only companies with at least one touch in that specific month, not all companies ever recorded.
Extract the Calendar Month
Truncate or format the touch_date to a year-month grain (e.g., DATE_TRUNC('month', touch_date) in SQL) so all touches can be grouped by calendar month.
Aggregate Touches per Company per Month
Group by (month, company_id) and COUNT the number of touches to produce a row for each company-month combination with its touch count — this naturally filters to only active companies.
Compute the Monthly Average
Wrap the previous result in an outer query that groups by month and takes AVG(touch_count); since every company in this intermediate table had at least one touch, the denominator is automatically correct.
Validate and Sanity-Check the Result
Verify the output by spot-checking a single month manually, and consider edge cases such as months with only one active company or ties in touch counts to ensure the logic holds.
Key Points to Mention
The tie-break rule tripped me up a bit.
Suggested Approach
Approach this as a last-touch attribution problem by joining a conversions table to a marketing touches table using a non-equi join (touch timestamp <= conversion timestamp), then use a window function like ROW_NUMBER() or LAST_VALUE() partitioned by company to isolate the most recent touch before conversion. Finally, compute the same-month flag using date truncation or EXTRACT functions on both timestamps, and filter out companies with no prior touches using an INNER JOIN or EXISTS clause.
Identify & Join Source Tables
Start by clarifying the schema — a conversions table (company, conversion_timestamp) and a marketing_touches table (company, touch_timestamp, channel, campaign). Perform a JOIN between them on company where touch_timestamp <= conversion_timestamp to get all eligible touches per conversion.
Rank Touches to Find the Last One
Apply ROW_NUMBER() OVER (PARTITION BY company ORDER BY touch_timestamp DESC) to rank all qualifying touches per company, so rank = 1 identifies the most recent touch at or before conversion. Alternatively, use a correlated subquery with MAX(touch_timestamp) for the same result.
Filter to Last Touch Only
Wrap the ranked query in a CTE or subquery and filter WHERE row_num = 1 to retain only the last touch record per company, ensuring one output row per converted company.
Compute the Same-Month Flag
Add a boolean/integer flag column using DATE_TRUNC('month', conversion_timestamp) = DATE_TRUNC('month', touch_timestamp) — or equivalent EXTRACT(YEAR/MONTH) comparisons — to indicate whether both events fell in the same calendar month.
Exclude Companies with No Prior Touches
Because the JOIN is on touch_timestamp <= conversion_timestamp, companies with zero qualifying touches will naturally be excluded by an INNER JOIN; explicitly call this out to show awareness that LEFT JOIN would incorrectly include them with NULL touch data.
Key Points to Mention
Pretty much a wrapper around the previous query.
Suggested Approach
Start by clarifying the schema (e.g., a touches/events table with company_id, touch_timestamp, conversion_timestamp) and then write a SQL query that extracts the calendar month from both the last-touch date and the conversion date, comparing them for equality. For the follow-up, add a DATEDIFF condition (≤ 45 days) alongside the same-month filter, and use COUNT(DISTINCT company_id) in both cases to avoid double-counting.
Clarify Schema & Definitions
Ask about the table structure (columns like company_id, touch_date, conversion_date) and confirm what 'last touch' means — the maximum touch timestamp per company, or the touch immediately preceding conversion. Also confirm whether a company can have multiple conversions.
Identify the Last Touch per Company
Use a subquery or CTE with MAX(touch_date) grouped by company_id (and optionally by conversion_id) to isolate the single last-touch record for each company before writing the main filter logic.
Apply the Same-Month Filter
Compare the calendar month and year of the last touch and conversion dates using DATE_TRUNC('month', last_touch_date) = DATE_TRUNC('month', conversion_date) (or YEAR/MONTH extraction), then wrap with COUNT(DISTINCT company_id).
Add the 45-Day Gap Constraint (Follow-up)
Extend the WHERE clause with DATEDIFF(day, last_touch_date, conversion_date) BETWEEN 0 AND 45 (or ABS version if direction is ambiguous), keeping the same-month condition, and re-run COUNT(DISTINCT company_id) for the stricter result.
Sanity-Check & Interpret Results
Briefly discuss how you would validate the output — for example, checking that the 45-day version always returns a count ≤ the same-month-only version — and mention what the metric could mean for Upstart's attribution or funnel analysis.
Key Points to Mention
Discussion(3)
Sign in to join the discussion.
The denominator scoping thing is genuinely confusing the first time you see it phrased that way, and I did exactly what you did: wrote the simple version, then talked myself out of it. The GROUP BY month implicitly restricts rows to that month, so COUNT(DISTINCT company_id) in the denominator is already scoped correctly. No subquery needed. Where I'd actually push yourself is on the month extraction piece, because Upstart's data stack almost certainly runs on Snowflake or BigQuery at this point, not Postgres, and the interviewer asking about dialect differences isn't idle curiosity. In Postgres DATE_TRUNC('month', touch_date) returns a timestamp truncated to midnight on the first, which you can group on cleanly. In BigQuery DATE_TRUNC(touch_date, MONTH) flips the argument order and expects a DATE type, so if your column is a TIMESTAMP you need DATE(touch_date) first or you get a type error that looks mysterious. Snowflake's DATE_TRUNC matches Postgres argument order but is more permissive about types. Getting that detail right out loud, without being asked, reads well in a phone screen because it shows you've actually run these queries against real warehouses and hit the errors rather than just knowing the syntax from documentation.
The filter-before-window ordering is the part that bites people. If you run ROW_NUMBER() across all touches and then filter to rows where touch_timestamp <= conversion_timestamp afterward, you can get a rank of 1 assigned to a touch that happened after conversion, and your WHERE rank = 1 pulls the wrong row entirely. The fix is a CTE or subquery that drops post-conversion touches first, then applies the window. The tie-break on touch_id is a small thing but interviewers at this level notice if you skip it, because identical timestamps aren't hypothetical in marketing data, batch jobs fire multiple events at the same second constantly. For the same-month flag I'd write it as a CASE WHEN inside the final SELECT rather than computing it in the window CTE, keeps the logic readable. One thing I'd add that the author didn't mention: the EXCLUDE companies with no prior touches requirement means an INNER JOIN between conversions and your last-touch CTE, not a LEFT JOIN. Easy to default to LEFT JOIN out of habit and then realize you're returning NULLs for companies you were supposed to drop.
Yeah this one is basically free if Q2 is solid. The 45-day gap filter is where I'd just be careful about DATEDIFF argument order since it flips between warehouses. In Snowflake it's DATEDIFF('day', earlier, later), BigQuery uses DATE_DIFF(later, earlier, DAY). Getting that backwards gives you a negative number and your <= 45 check silently returns nothing.