Loading the editor…
Find workspaces whose most recent invoice is not paid.
Collections ignores an old open bill when a later invoice was paid. The latest invoice, by issue date, is the one that decides the call list.
Return workspace. Choose the invoice with the latest issued_at for each workspace, breaking ties by the larger invoice id. Keep workspaces whose chosen invoice status is not paid. Sort by workspace 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: Posted expenses above the average