Design Schema for Accurate Subscription State Tracking

## Daily end-of-day subscription status You are given a single event-log table, `subscription_events`, where each row records a `signup` or `cancel` event for a user, with the plan a user chose at signup. ``` subscription_events(user_id, event_ts, event_type, plan_type) - event_type IN ('signup','cancel') - plan_type IN ('free','paid_trial') -- meaningful on 'signup' rows; may be carried on 'cancel' rows but is irrelevant there ``` For **every user** and **every calendar date** in the observed range, report the user's subscription status at the **end of that day** (i.e. as of 23:59:59 of that date). Rules: - The **observed date range** is every day from the earliest `event_ts::date` to the latest `event_ts::date` across the whole table (inclusive). Produce one row per (user, date) for **all** users on **every** date in that range, even on dates before a user's first event. - A user is **`active`** on a date if the **most recent event at or before end-of-day** for that user is a `signup`; otherwise the user is **`inactive`** (this includes dates before the user has any event, and dates whose most recent event is a `cancel`). - `plan_type` must be carried from the **most recent `signup`** event when the user is `active`, and must be **`NULL`** when the user is `inactive`. - Handle multiple signup -> cancel -> signup cycles correctly: the status flips back and forth based purely on the latest event up to that day's end. **Output columns:** `user_id`, `snapshot_date`, `subscription_status` (`'active'`/`'inactive'`), `plan_type` (the carried plan, or `NULL`). **Sort order:** by `user_id` ascending, then `snapshot_date` ascending.

For each signup event, derive the user's active subscription interval. Use `subscription_events`. A signup starts an interval at its `event_ts`. The interval ends at the first `cancel` event for the same user that occurs after that signup and before the next signup. If there is no such cancel, `end_ts` should be `NULL`. Return these columns: - `user_id` - `start_ts`: signup time formatted as `YYYY-MM-DD HH24:MI:SS` - `end_ts`: cancel time formatted as `YYYY-MM-DD HH24:MI:SS`, or `NULL` - `plan_type`: the plan from the signup event Order by `user_id`, then `start_ts`.

Given a specific snapshot date (e.g., 2024-04-15), return each user's subscription status as of end-of-day and the plan_type from the latest signup when active.

## Daily Active Users by Plan Using the `subscription_events` table, compute the number of **active users per day, broken down by `plan_type`**, across the full observed date range (from the earliest event date through the latest event date, inclusive — one row per calendar day that has at least one active user in a plan). **Active-user rules (end-of-day status):** - For each user and each calendar day, look at that user's **most recent event whose timestamp is before the end of that day** (i.e. before midnight at the start of the next day). - If that most recent event is a `signup`, the user is **active** on that day and is counted under the `plan_type` of that signup event. - If that most recent event is a `cancel` (or the user has no event yet on that day), the user is **not** active and is not counted. - Because a user's latest signup determines their current plan, a user who cancels one plan and later signs up for another is counted under whichever plan their most recent signup belongs to. **Notes on the data:** `cancel` events carry the `plan_type` of the plan being cancelled, but cancellations never contribute to the active count — only the latest *signup* before end-of-day is counted. **Result must contain exactly these columns, in this order:** - `snapshot_date` — the calendar day (DATE) - `plan_type` — `'free'` or `'paid_trial'` - `active_users` — count of users active under that plan on that day **Sort order:** ascending by `snapshot_date`, then ascending by `plan_type`. Only emit (date, plan) combinations that have at least one active user.