We have collected data about fuel consumption for a number of vehicles.
We can calculate the consumption for a given time period by taking the difference in MeterValue. For example LIFT1 had a consumption of 25 between 08:00 and 08:05.
Calculate the average consumption for each vehicle for the 20% periods that had the highest consumption per minute. Calculate the time-weighted average of these periods.
- The 20% number of periods should be rounded upwards. If there are 11 periods, the 20% should be rounded to 3 periods.
- If multiple periods has the same consumption per minute, chose the latest periods.
Your task is to calculate this time-weighted average for all vehicles as fast as possible using T-SQL or SQLCLR (safe mode only).
Other restrictions in the competition:
- The solutions had to run on a SQL Server 2008 R2
- You are not allowed to make any changes to the MeterReadings table except creating non clustered indexes
- Indexed views are not allowed
Let’s look at the data for LIFT1.
This example contains 10 periods, which means the 20% will be 2 periods. The 2 periods with the highest consumption per minute are marked in bold above.
The time-weighted average consumption for these periods is:
(12 x 45 + 9 x 5) / (45 + 5) = 11,7
For LIFT2 the time-weighted average consumption would be 15.
This competition problem was authored by Johan Åhlén and based on a real world scenario.