Design Schema and Logic for Subscription Event Tracking
Company: OpenAI
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Online Assessment
user_subscription_events
+----------+-------------+---------------------+-----------+---------+
| user_id | event_type | event_time | plan_type | source |
+----------+-------------+---------------------+-----------+---------+
| 101 | signup | 2023-05-01 09:03:12 | paid | web |
| 101 | cancel | 2023-06-10 14:27:45 | paid | web |
| 101 | signup | 2023-07-02 08:11:06 | paid | mobile |
| 202 | signup | 2023-05-05 11:55:20 | paid | email |
| 303 | cancel | 2023-05-07 16:02:09 | paid | support |
+----------+-------------+---------------------+-----------+---------+
##### Scenario
Subscription event pipeline feeding the experiment: users can signup, cancel, then signup again; downstream tables drive metrics and triggering code.
##### Question
Design the raw event table schema needed to derive a user's current subscription status and historical states. 2. Write SQL or Python logic that produces one row per user with their latest status, handling the edge case where a user signs up, cancels, and then signs up again. 3. Review the provided Python snippet that assigns variants and delivers the free-trial offer; identify at least three improvements or safeguards you would add.
##### Hints
Think append-only event logs, window functions, deduping by latest timestamp, idempotent triggers.
Overview: This question evaluates proficiency in event-driven data modeling, temporal state derivation, and implementation of SQL/Python logic to compute per-user subscription status and history within the Data Manipulation (SQL/Python) domain.
Design subscription events schema
Create SQL DDL for an append-only user_subscription_events table to capture subscription signups and cancels so downstream jobs can derive both current status and history. Enforce NOT NULL constraints and restrict event_type to 'signup' or 'cancel'. Add useful indexes for windowing by user and event_time using the provided columns. For this executable answer, return the sample rows that conform to the proposed schema, ordered by user_id and event_time.
Tables
user_subscription_events(user_id INTEGER, event_type VARCHAR(20), event_time TIMESTAMP, plan_type VARCHAR(20), source VARCHAR(20))
Hints
- Use a CHECK constraint to restrict event_type to allowed values.
- Index by (user_id, event_time) to speed up latest-event window functions.
Latest Subscription Status Per User
Return one row per user from `user_subscription_events` with the user's most recent event details and derived `current_status`. Use `active` when the latest event is `signup` and `canceled` when it is `cancel`. If a user has multiple events at the same timestamp, prefer `signup` over `cancel`, then use `source` alphabetically as a deterministic final tie-breaker.
Tables
user_subscription_events(user_id INTEGER, event_type VARCHAR(20), event_time TIMESTAMP, plan_type VARCHAR(20), source VARCHAR(20))
Hints
- Use `ROW_NUMBER()` partitioned by `user_id`.
- Order by `event_time DESC`, then prefer signup before cancel.
Improve offer-delivery logic
List at least three concrete improvements or safeguards for a Python service that assigns experiment variants and delivers a free-trial offer, focusing on idempotency, deterministic assignment, eligibility, late events, I/O robustness, and data validation.
Tables
user_subscription_events(user_id INTEGER, event_type VARCHAR(20), event_time TIMESTAMP, plan_type VARCHAR(20), source VARCHAR(20))
Hints
- Think about deduplication and transactionality.
- Use stable hashing to assign variants deterministically.