Your question is Dataset Growth Optimization. Take a moment with it on the right.
Talk me through your thinking if you like. When you're confident, submit your answer and I'll grade it like a real screen (7/10 or better passes).
REsurety analyzes renewable-energy production data at the level of individual generation readings. The query below powers an internal report showing each site's top output readings for the current year, alongside its running average output. It runs fine on the demo dataset but the analytics team is about to onboard 100x more sites and years of historical readings.
-- generation_readings(id, site_id, reading_time TIMESTAMP, output_mw NUMERIC)
-- sites(id, name, region_id)
-- site_certifications(site_id, certification_id)
SELECT DISTINCT r.id, r.site_id, r.reading_time, r.output_mw, s.name,
(SELECT AVG(output_mw)
FROM generation_readings r2
WHERE r2.site_id = r.site_id) AS site_avg_output
FROM generation_readings r
JOIN sites s ON s.id = r.site_id
JOIN site_certifications c ON c.site_id = r.site_id
WHERE YEAR(r.reading_time) = 2025
ORDER BY r.output_mw DESC;
How would you optimize this query if the dataset size increased by a factor of 100? Explain what makes it slow and what you would change, in terms of both the query and any indexes you would add.