Annual Repair Spend by Zone

A facilities platform tracks completed repair jobs and the operating zone assigned to each facility. Finance wants a zone-level view of repair spending for a fixed 365-day reporting period.

Table: facilities

+-------------+--------------+
| Column Name | Type         |
+-------------+--------------+
| facility_id | integer      |
| zone        | varchar(255) |
+-------------+--------------+
+-------------+--------------+
| Column Name | Type         |
+-------------+--------------+
| facility_id | integer      |
| zone        | varchar(255) |
+-------------+--------------+

facility_id is the primary key. zone may be NULL.

Table: repair_jobs

+----------------+---------------+
| Column Name    | Type          |
+----------------+---------------+
| repair_id      | integer       |
| facility_id    | integer       |
| completed_on   | date          |
| repair_cost    | decimal(12,2) |
+----------------+---------------+
+----------------+---------------+
| Column Name    | Type          |
+----------------+---------------+
| repair_id      | integer       |
| facility_id    | integer       |
| completed_on   | date          |
| repair_cost    | decimal(12,2) |
+----------------+---------------+

repair_id is the primary key. facility_id references facilities.facility_id. Each row represents one completed repair job.

Consider repair jobs completed during the 365-day window beginning on 2025-10-01 and ending immediately before 2026-10-01. In other words, include 2025-10-01 through 2026-09-30, inclusive.

Join each qualifying repair job to its facility and calculate the total repair_cost for every zone. Replace a NULL zone with Unknown. A real zone value equal to Unknown belongs to the same output group as the replaced NULL values.

Return zone and total_repair_cost. Order the results by total_repair_cost descending, then by zone ascending when totals are tied. Do not return zones without a qualifying repair job.

Example

Input:

facilities table:
+-------------+-------+
| facility_id | zone  |
+-------------+-------+
| 1           | West  |
| 2           | East  |
| 3           | North |
+-------------+-------+
facilities table:
+-------------+-------+
| facility_id | zone  |
+-------------+-------+
| 1           | West  |
| 2           | East  |
| 3           | North |
+-------------+-------+

repair_jobs table:
+-----------+-------------+--------------+-------------+
| repair_id | facility_id | completed_on | repair_cost |
+-----------+-------------+--------------+-------------+
| 1         | 1           | 2026-09-01   | 100.00      |
| 2         | 1           | 2026-08-15   | 200.00      |
| 3         | 2           | 2026-07-10   | 150.00      |
| 4         | 3           | 2026-06-20   | 80.00       |
+-----------+-------------+--------------+-------------+
repair_jobs table:
+-----------+-------------+--------------+-------------+
| repair_id | facility_id | completed_on | repair_cost |
+-----------+-------------+--------------+-------------+
| 1         | 1           | 2026-09-01   | 100.00      |
| 2         | 1           | 2026-08-15   | 200.00      |
| 3         | 2           | 2026-07-10   | 150.00      |
| 4         | 3           | 2026-06-20   | 80.00       |
+-----------+-------------+--------------+-------------+

Output:

+-------+-------------------+
| zone  | total_repair_cost |
+-------+-------------------+
| West  | 300.00            |
| East  | 150.00            |
| North | 80.00             |
+-------+-------------------+
+-------+-------------------+
| zone  | total_repair_cost |
+-------+-------------------+
| West  | 300.00            |
| East  | 150.00            |
| North | 80.00             |
+-------+-------------------+

Explanation:

The two West jobs total 300.00. East totals 150.00, and North totals 80.00. Sorting by total repair cost places West first.