Business Context
A two-sided home-services marketplace (think TaskRabbit-scale, with millions of completed tasks per year) uses customer ratings to maintain service quality and decide which taskers get priority placement in search results. The operations team wants a repeatable, SQL-only way to identify the top decile of taskers based on recent performance so they can trigger perks (higher visibility, faster payouts) and coaching interventions.
Your analytics warehouse stores tasker profiles in a dimension table and task events in a fact table. A task is considered for quality scoring only if it is completed and has a non-NULL rating.
Task
Write a SQL query that computes a 90-day quality snapshot ending on 2025-01-01 (exclusive) and returns only the taskers in the top decile by a composite score.
Requirements
- Consider only tasks where
status = 'completed', rating IS NOT NULL, and completed_at is in the half-open window: [2025-01-01 - 90 days, 2025-01-01).
- For each tasker, compute:
completed_tasks_90d = count of qualifying tasksavg_rating_90d = average rating across qualifying tasks
- Compute
score using the composite formula:
score = 0.7 * avg_rating_90d
(Note: this intentionally emphasizes rating only; volume is tracked but not included in the score.)
- Assign deciles using
NTILE(10) ordered by score DESC (highest score = decile 1).
- Return only decile 1 (top decile) with columns:
tasker_id, full_name, completed_tasks_90d, avg_rating_90d (4 decimals), score (4 decimals), score_percentile.
- Order results by
score DESC, then completed_tasks_90d DESC for deterministic output.
Table Definitions
taskers
| column | type | description |
|---|
| tasker_id | INT | Primary key |
| full_name | VARCHAR(100) | Tasker display name |
| city | VARCHAR(50) | Primary operating city |
| activated_at | DATE | Date tasker joined/activated |
tasks
| column | type | description |
|---|
| task_id | INT | Primary key |
| tasker_id | INT | Foreign key to taskers.tasker_id |
| customer_id | INT | Customer identifier |
| status | VARCHAR(20) | Task lifecycle status (completed, canceled, etc.) |
| completed_at | DATE | Completion date; NULL if not completed |
| rating | INT | Customer rating 1–5; NULL if not rated |
| price_usd | DECIMAL(10,2) | Task price in USD |
Sample Data
taskers
| tasker_id | full_name | city | activated_at |
|---|
| 101 | Maya Chen | San Francisco | 2023-06-10 |
| 102 | Jordan Patel | San Francisco | 2024-02-01 |
| 103 | Elena Garcia | Oakland | 2024-05-20 |
| 104 | Samir Khan | San Jose | 2022-11-03 |
| 105 | Priya Nair | San Francisco | 2024-08-15 |
tasks
| task_id | tasker_id | customer_id | status | completed_at | rating | price_usd |
|---|
| 9001 | 101 | 501 | completed | 2024-12-15 | 5 | 120.00 |
| 9002 | 101 | 502 | completed | 2024-11-10 | 4 | 80.00 |
| 9003 | 102 | 503 | completed | 2024-12-20 | 5 | 60.00 |
| 9004 | 103 | 504 | canceled | NULL | NULL | 75.00 |
| 9005 | 104 | 505 | completed | 2024-10-15 | 3 | 200.00 |
Expected Output
| tasker_id | full_name | completed_tasks_90d | avg_rating_90d | score | score_percentile |
|---|
| 102 | Jordan Patel | 1 | 5.0000 | 3.5000 | 1 |
