Top Solar Installer per Panel Technology
Problem
A solar company tracks installers, the arrays they own, and the individual jobs run on those arrays. Each array uses one panel_tech. Each job records the kilowatts delivered and a customer satisfaction score from 1 to 5. For every panel_tech find the single best installer ranked by, in order: highest average satisfaction score (rounded to 2 decimals), then highest total kilowatts, then fewest safety_flags on the installer record. Return panel_tech, installer_id, score and kilowatts, ordered by panel_tech.
Tables
Example rows — the live problem includes the full dataset.
| installer_id | full_name | years_active | safety_flags |
|---|---|---|---|
| 1 | Nadia | 10 | 1 |
| 2 | Omar | 20 | 3 |
| 3 | Priya | 5 | 0 |
| array_id | installer_id | panel_tech | watt_rating |
|---|---|---|---|
| 100 | 1 | Mono | 20000 |
| 101 | 2 | Thin Film | 30000 |
| 102 | 3 | Mono | 15000 |
| job_id | array_id | kilowatts | minutes | score |
|---|---|---|---|---|
| 201 | 100 | 50 | 30 | 5 |
| 202 | 100 | 30 | 20 | 4 |
| 203 | 101 | 100 | 60 | 4 |
Expected output
Your answer should return 2 rows with the columns panel_tech, installer_id, score, kilowatts.
Starter code (SQL)
SELECT *
FROM installers;Solve this SQL question free
Write SQL and run it instantly in your browser — even on your phone. No signup needed to try.
Solution & explanation
Create a free account to unlock the optimal solution, a step-by-step explanation, and the hidden test cases that grade your answer.
Sign up free to unlock