Write SQL for snapshot features and labels
Company: Stripe
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Use 'today' = 2025-09-01. Invented schema (UTC):
users(u_id INT, signup_dt DATE, country STRING)
events(u_id INT, event_time TIMESTAMP, arrival_time TIMESTAMP, event_type STRING, session_id STRING)
orders(order_id INT, u_id INT, order_time TIMESTAMP, amount DECIMAL(10,2))
Sample rows:
users
u_id | signup_dt | country
1 | 2025-07-20 | US
2 | 2025-08-25 | CA
3 | 2025-08-30 | US
events
u_id | event_time | arrival_time | event_type | session_id
1 | 2025-08-29 10:00:00 | 2025-08-29 10:00:02 | view | a
1 | 2025-08-31 09:00:00 | 2025-08-31 09:00:03 | add_to_cart | a
1 | 2025-09-01 12:00:00 | 2025-09-01 12:00:01 | view | b
2 | 2025-08-30 15:00:00 | 2025-08-30 15:00:02 | view | c
2 | 2025-09-01 09:30:00 | 2025-09-01 09:30:01 | view | d
3 | 2025-08-31 23:59:00 | 2025-09-02 00:00:05 | view | e (late-arriving)
3 | 2025-09-01 08:00:00 | 2025-09-01 08:00:00 | view | e
3 | 2025-09-01 08:00:00 | 2025-09-01 08:00:00 | view | e (duplicate)
orders
order_id | u_id | order_time | amount
101 | 1 | 2025-09-10 13:00:00 | 120.00
102 | 2 | 2025-08-31 16:00:00 | 50.00
103 | 2 | 2025-09-03 11:00:00 | 60.00
Task: Write one SQL query that produces a training dataset at anchor snapshot_ts = '2025-09-01 00:00:00' with columns: (u_id, country, days_since_signup, last_event_time, events_7d, sessions_7d, added_to_cart_7d, y_30d). Rules: (A) Features use only events with event_time < snapshot_ts AND arrival_time <= snapshot_ts; drop duplicates by (u_id, event_time, event_type, session_id). (B) Compute 7-day windows over [snapshot_ts - 7 days, snapshot_ts). (C) sessions_7d counts distinct session_id in window; events_7d counts distinct (event_time, event_type, session_id). (D) y_30d = 1 if there exists an order in [snapshot_ts, snapshot_ts + INTERVAL '30' DAY), else 0. (E) Include users with no events (fill nulls/zeros appropriately). Show the exact row outputs for the provided sample data and explain why user 3's late-arriving 2025-08-31 event must be excluded from features.
Overview: This question evaluates proficiency in SQL-based feature engineering for time-series and user-event data, covering snapshotting, temporal joins, deduplication, windowed aggregations, distinct counting, and label construction in the Data Manipulation (SQL/Python) domain.
Read the full Stripe Data Scientist interview experience this question came from
You are building a point-in-time training table for a churn/purchase model. All timestamps are in UTC. You are given three tables:
- `users(u_id INT, signup_dt DATE, country VARCHAR)`
- `events(u_id INT, event_time TIMESTAMP, arrival_time TIMESTAMP, event_type VARCHAR, session_id VARCHAR)` — `event_time` is when the action happened; `arrival_time` is when the row became available in the warehouse (it can be later than `event_time` due to ingestion latency).
- `orders(order_id INT, u_id INT, order_time TIMESTAMP, amount DECIMAL(10,2))`
Use a fixed anchor **snapshot timestamp `snapshot_ts = '2025-09-01 00:00:00'`**. Write a single PostgreSQL query (CTEs allowed) that returns **one row per user in `users`** with exactly these columns, in this order:
- `u_id`
- `country`
- `days_since_signup` — integer number of days from `signup_dt` to `DATE '2025-09-01'` (i.e. `DATE '2025-09-01' - signup_dt`).
- `last_event_time` — the latest `event_time` among the user's **qualifying feature events** (see rules A); `NULL` if the user has none.
- `events_7d` — count of the user's deduplicated feature events that fall in the 7-day feature window.
- `sessions_7d` — count of **distinct** `session_id` values among those 7-day-window feature events.
- `added_to_cart_7d` — count of 7-day-window feature events with `event_type = 'add_to_cart'`.
- `y_30d` — `1` if the user has at least one order in the 30-day label window, else `0`.
Rules:
**A) Qualifying feature events.** Only consider event rows with `event_time < snapshot_ts` **AND** `arrival_time <= snapshot_ts` (an event that physically occurred before the snapshot but arrived afterward is *not yet known* at snapshot time and must be excluded). Deduplicate these rows on `(u_id, event_time, event_type, session_id)` before counting. `last_event_time` is `MAX(event_time)` over these qualifying (deduplicated) events — it is **not** restricted to the 7-day window.
**B) 7-day feature window.** `events_7d`, `sessions_7d`, and `added_to_cart_7d` are computed over qualifying feature events whose `event_time` is in `['2025-08-25 00:00:00', '2025-09-01 00:00:00')` — i.e. `>= snapshot_ts - INTERVAL '7 days'` and `< snapshot_ts`.
**C) 30-day label window.** `y_30d = 1` if the user has at least one order with `order_time` in `['2025-09-01 00:00:00', '2025-10-01 00:00:00')` — i.e. `>= snapshot_ts` and `< snapshot_ts + INTERVAL '30 days'`; otherwise `0`. (Orders strictly before the snapshot do not count as labels.)
**D) Completeness.** Include **every** user from `users`, even with no events or orders. For users with no qualifying events, `last_event_time` is `NULL` and the three `*_7d` counts are `0`. For users with no qualifying orders, `y_30d = 0`.
Return the rows **ordered by `u_id` ascending**.
With the sample data, note in particular that user 3's event with `event_time = '2025-08-31 23:59:00'` and `arrival_time = '2025-09-02 00:00:05'` is excluded (it arrived after the snapshot), and user 3's two `'2025-09-01 08:00:00'` rows are excluded (they occur at/after the snapshot and are duplicates of each other) — so user 3 ends up with no qualifying feature events.
Tables
users(u_id INT, signup_dt DATE, country VARCHAR(2))
events(u_id INT, event_time TIMESTAMP, arrival_time TIMESTAMP, event_type VARCHAR(50), session_id VARCHAR(50))
orders(order_id INT, u_id INT, order_time TIMESTAMP, amount DECIMAL(10,2))
Hints
- Apply the point-in-time filter first: keep only events with event_time < snapshot_ts AND arrival_time <= snapshot_ts, and SELECT DISTINCT to drop duplicate ingests — that single CTE explains why user 3 has no features.
- last_event_time is MAX(event_time) over ALL qualifying events, but events_7d / sessions_7d / added_to_cart_7d are over the narrower 7-day window — keep them in separate CTEs.