Solar Installer Three-Month Rolling Averages (2021)
Problem
Using the same solar dataset (`Installers`, `Requests`, `Bookings`), compute a forward three-month rolling average of monthly totals for 2021.
For each 2021 month, let the monthly totals be the sum of `panel_count` and the sum of `job_minutes` across bookings whose request was made that month (0 when none). Then for each month 1..10 report the average of that month's total and the next two months' totals (the current month plus the two following months).
Columns: `month`, `avg_panel_count`, `avg_job_minutes`, each rounded to 2 decimals. Months with no bookings contribute 0 to the window. Rows may be in any order.
Tables
Example rows — the live problem includes the full dataset.
| installer_id | onboarded_on |
|---|---|
| 10 | 2020-12-10 |
| 8 | 2021-01-13 |
| 5 | 2021-02-16 |
| request_id | household_id | requested_on |
|---|---|---|
| 6 | 75 | 2020-12-09 |
| 1 | 54 | 2021-02-09 |
| 10 | 63 | 2021-03-04 |
| request_id | installer_id | panel_count | job_minutes |
|---|---|---|---|
| 10 | 10 | 63 | 38 |
| 13 | 10 | 73 | 96 |
| 7 | 8 | 100 | 28 |
Expected output
Your answer should return 10 rows with the columns month, avg_panel_count, avg_job_minutes.
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