New Notification
0:00:00
Twitter wants to boost user engagement by increasing push notification frequency, so they launch an A/B test:
One group receives the standard notification cadence (‘control’), while the other gets more frequent notifications (‘variant’). After launch, the total number of unsubscribes rises, and the product team suspects this might affect user login behavior differently across groups.
You’re tasked to help the team visualize these trends. Write a SQL query to produce a dataset that, for each day and for each experiment group, shows the total number of users who logged in and the total number who unsubscribed. This data will help the team graph how login and unsubscribe rates change over time for both the control and variant groups.
Example:
Input:
events table
| Column | Type |
|---|---|
| user_id | INTEGER |
| created_at | DATETIME |
| action | VARCHAR |
Possible values for action are: ‘login’, ‘nologin’, ‘unsubscribe’.
variants table
| Column | Type |
|---|---|
| user_id | INTEGER |
| experiment | VARCHAR |
| variant | VARCHAR |
variant is either ‘control’ or ‘variant’.
Output:
| Column | Type |
|---|---|
| event_date | DATE |
| variant | VARCHAR |
| logins | INTEGER |
| unsubscribes | INTEGER |
Note: Count unique users per day per variant for both logins and unsubscribes. If a user performs the same action multiple times in a day, count them only once for that action.
.
.
.
.
Comments