Stripe·Data Scientist·Technical Phone Screen·Senior

SeniorPrefer not to say

Sep 2025Remote

Summary

Stripe DS interview that was basically one long SQL gauntlet. The question was dense enough that I'm still thinking about edge cases a week later. Solid experience if you like data engineering adjacent problems, rough if you were expecting something lighter.

Questions Asked (1)

Write a single SQL query to build a training dataset anchored at a specific snapshot timestamp, including feature columns like days since signup, event counts over a 7-day window, distinct session counts, and a binary 30-day order label. The schema includes users, events, and orders tables, and the data has late-arriving events, duplicates, and users with no events at all.

Data ModelingProduct Analytics & MetricsTechnical Trade-offs

Author's notes

This was the whole interview basically.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the snapshot timestamp and the exact definitions of features and label, then outline a query that uses a CTE to filter and deduplicate events and orders up to the snapshot, handles late-arriving data by filtering on event timestamp, and finally left joins users to aggregated features and label to include users with no events. Emphasize correctness with duplicates and missing data, and discuss trade-offs like window functions vs. subqueries.

Pro tip: Always anchor all time-based features and the label to the snapshot timestamp to prevent data leakage, and explicitly handle late-arriving events by filtering on event_time <= snapshot, not ingestion time.

1. Clarify requirements and assumptions

Confirm the snapshot timestamp, feature definitions (e.g., 7-day window relative to snapshot), label definition (order within 30 days after snapshot), and how to treat duplicates and late-arriving events.

2. Deduplicate and filter raw data

Use CTEs to select distinct events and orders, filtering to only those with timestamps up to the snapshot (and for label, up to snapshot + 30 days), ensuring late-arriving events are included if their event_time is before snapshot.

3. Compute user-level features

Aggregate events per user to calculate days since signup (snapshot - signup_date), event counts in the 7-day window before snapshot, and distinct session counts in that window.

4. Compute binary label

Determine if the user placed any order in the 30 days after the snapshot (including the snapshot day if applicable) and create a binary label.

5. Combine and handle missing users

Left join the users table to the feature and label aggregates, using COALESCE to fill zeros for users with no events or orders, ensuring all users are included.

Key Points to Mention

Use of snapshot timestamp to avoid data leakage and ensure point-in-time correctness
Handling late-arriving events by filtering on event_time <= snapshot, not ingestion time
Deduplication strategy (e.g., DISTINCT or ROW_NUMBER) for events and orders
Definition of 7-day window relative to snapshot (e.g., event_time > snapshot - INTERVAL '7 days' AND event_time <= snapshot)
Left join to include users with no events, with COALESCE for zero counts
Binary label definition: EXISTS order in (snapshot, snapshot + 30 days]

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