Business Context
A two-sided home-services marketplace (think “TaskRabbit”-style) operates in multiple US cities and relies on a healthy supply of active taskers to meet demand. The growth team is running a re-engagement campaign and needs a deterministic list of taskers who appear “inactive” based on completed work (not merely created, canceled, or in-progress tasks).
For operational simplicity, assume today is 2025-01-15 and the re-engagement cutoff is 2024-10-15 (i.e., taskers whose last completed task is strictly earlier than 2024-10-15 are considered inactive).
Task
Write a SQL query to identify taskers who are inactive.
Requirements
- For each tasker, compute
last_completed_at as the most recent completed_at among tasks where status = 'completed'.
- Include taskers who have never completed a task (their
last_completed_at should be NULL).
- Return only taskers where
last_completed_at IS NULL OR last_completed_at < DATE '2024-10-15'.
- Output columns:
tasker_id, full_name, last_completed_at.
- Order results so that taskers with
NULL last_completed_at come first, then by last_completed_at ascending, then by tasker_id ascending.
Table Definitions
taskers
| column | type | description |
|---|
| tasker_id | INT | Primary key |
| full_name | VARCHAR(200) | Tasker display name; not null |
| city | VARCHAR(100) | Primary service city; not null |
| created_at | DATE | Date the tasker joined; not null |
tasks
| column | type | description |
|---|
| task_id | BIGINT | Primary key |
| tasker_id | INT | Foreign key to taskers.tasker_id; not null |
| status | VARCHAR(30) | Task status enum: completed/canceled/in_progress; not null |
| completed_at | DATE | Completion date; nullable and expected NULL unless status='completed' |
| created_at | DATE | Task creation date; not null |
Sample Data
taskers
| tasker_id | full_name | city | created_at |
|---|
| 101 | Maya Chen | Seattle | 2023-02-10 |
| 102 | Jordan Patel | Seattle | 2023-11-05 |
| 103 | Luis Romero | Austin | 2024-01-20 |
| 104 | Aisha Khan | Austin | 2024-06-02 |
| 105 | Priya Nair | Chicago | 2024-03-14 |
tasks
| task_id | tasker_id | status | completed_at | created_at |
|---|
| 9001 | 101 | completed | 2024-09-30 | 2024-09-29 |
| 9002 | 101 | canceled | NULL | 2024-12-01 |
| 9003 | 102 | completed | 2024-12-20 | 2024-12-18 |
| 9004 | 104 | in_progress | NULL | 2025-01-10 |
| 9005 | 105 | completed | 2024-10-10 | 2024-10-09 |
Expected Output
| tasker_id | full_name | last_completed_at |
|---|
| 103 | Luis Romero | NULL |
| 104 | Aisha Khan | NULL |
| 101 | Maya Chen | 2024-09-30 |
| 105 | Priya Nair | 2024-10-10 |
