Most Popular Active Plan for the Top Event Type
Notion● HardFULLTIMEPHONE SCREENProblem statement
First find the event type with the most event rows. Break an event-type tie alphabetically.
For events of that type, join the subscription active at each event time and return the plan with the most matching events. A subscription is active when start_time <= event_time < end_time; a null end_time means it is still active. Break a plan tie alphabetically. Return plan_type and event_count.
Table schema
MySQLPostgreSQLPandas
Use the same input data with any supported language. Open the Schema tab in the editor to see the generated SQL setup or Pandas DataFrames.
events
| Column | Type | Nullable | Description |
|---|---|---|---|
| event_idPK | Integer | No | — |
| user_id | Integer | No | — |
| event_type | Text | No | — |
| event_time | Timestamp | No | — |
subscriptions
| Column | Type | Nullable | Description |
|---|---|---|---|
| subscription_idPK | Integer | No | — |
| user_id | Integer | No | — |
| plan_type | Text | No | — |
| start_time | Timestamp | No | — |
| end_time | Timestamp | Yes | — |
Expected result
Your query or function must return these columns.
| Column | Type | Nullable | Description |
|---|---|---|---|
| plan_type | Text | No | — |
| event_count | Integer | No | — |
Row order: must match exactly. Numeric tolerance: 0.
Constraints
- At most one subscription is active for a user at any event time.
- The selected top event type has at least one event with an active subscription.
- The end boundary is exclusive.