SQL Top Factories MoM Reduction | Dataford Interview Questions

How would you use SQL to identify the top 3 factories by month-over-month reduction in energy consumption?

Return the top three factories for each month where consecutive monthly measurements exist. Consider only positive reductions and use a deterministic tie-breaker.

Output

One row per selected factory and month, with month_start, factory_id, factory_name, previous_month_kwh, current_month_kwh, and reduction_kwh.
Include at most three rows per month, ordered by month_start, then reduction_kwh descending, then factory_id ascending.

Schema

factories

Column	Type	Description
factory_idPK	INTEGER	Unique factory identifier
factory_name	VARCHAR(100)	Factory name
country	VARCHAR(80)	Country where the factory operates

energy_consumption

Column	Type	Description
reading_idPK	INTEGER	Unique energy reading identifier
factory_id	INTEGER	Factory associated with the reading
reading_date	DATE	Date of the energy reading
energy_kwh	NUMERIC(12,2)	Energy consumed in kilowatt-hours
measurement_note	VARCHAR(200)	Optional note about the measurement

Your question is SQL Top Factories MoM Reduction. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem