Design Schema and Logic for Subscription Event Tracking

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.