Your question is Rank Top Service Categories. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
ServiceTitan wants to compare service-category performance across operating regions. Write a PostgreSQL query that identifies the top three service categories in each region based on completed job revenue during January 2025.
status = 'completed' and completion dates in January 2025.RANK() or DENSE_RANK() to return every category whose rank is 3 or better, including ties. Sort the final output by region, revenue descending, and category name.| Column | Type | Description |
|---|---|---|
| region_idPK | INTEGER | Unique operating region identifier |
| region_name | VARCHAR(100) | Operating region name |
| Column | Type | Description |
|---|---|---|
| category_idPK | INTEGER | Unique service category identifier |
| category_name | VARCHAR(100) | ServiceTitan service category name |
| Column | Type | Description |
|---|---|---|
| job_idPK | INTEGER | Unique job identifier |
| region_id | INTEGER | References regions.region_id |
| category_id | INTEGER | References service_categories.category_id |
| status | VARCHAR(30) | Job lifecycle status |
| completed_at | DATE | Date the job was completed |
| invoice_total | NUMERIC(12,2) | Total invoiced amount for the job |