Loading the editor…
Find users who were active in their signup month and the following calendar month.
Product defines early retention as any event in the signup month plus any event in the next calendar month. One event in each month is enough.
Return name. A user qualifies when they have at least one event in the YYYY-MM of users.created_at and at least one event in the next calendar month. Sort by name.
| id | name | created_at | country | |
|---|---|---|---|---|
| 1 | Maya Chen | maya@lumen.app | 2023-01-05 | US |
| 2 | Andre Silva | andre@orbit.io | 2023-02-11 | BR |
| 3 | Sophie Laurent | sophie@northstar.co | 2023-03-20 | FR |
| id | name | plan | owner_id | created_at |
|---|---|---|---|---|
| 1 | Lumen | pro | 1 | 2023-01-06 |
| 2 | Orbit Analytics | starter | 2 | 2023-02-12 |
| 3 | Northstar | pro | 3 | 2023-03-21 |
| id | workspace_id | plan | status | mrr | started_at | canceled_at |
|---|---|---|---|---|---|---|
| 1 | 1 | starter | canceled | 19 | 2023-01-06 | 2023-04-01 |
| 2 | 1 | pro | active | 79 | 2023-04-02 | NULL |
| 3 | 2 | starter | canceled | 19 | 2023-02-12 | 2024-11-01 |
| id | user_id | workspace_id | name | occurred_at |
|---|---|---|---|---|
| 1 | 1 | 1 | query_run | 2023-01-20 |
| 2 | 1 | 1 | dashboard_view | 2023-02-11 |
| 3 | 2 | 2 | query_run | 2023-02-18 |
| id | workspace_id | amount | status | issued_at | paid_at |
|---|---|---|---|---|---|
| 1 | 1 | 79 | paid | 2025-01-01 | 2025-01-01 |
| 2 | 1 | 79 | paid | 2025-02-01 | 2025-02-02 |
| 3 | 3 | 79 | paid | 2025-02-01 | 2025-02-01 |
Ctrl or Cmd + Enter runs the query. The database resets on every run.
Run the query to see rows from the sample database.
Next exercise: Running total of cash collected
Related: Workspace cohorts and active accounts, Monthly succeeded revenue