We have collected data about fuel consumption for a number of vehicles.

 ID Timestamp VehicleName MeterValue 1 2010-02-01 08:00:00.000 LIFT1 10000 2 2010-02-01 08:05:00.000 LIFT1 10025 3 2010-02-01 08:15:00.000 LIFT1 10065 4 2010-02-01 08:15:00.000 LIFT2 20000 5 2010-02-01 08:30:00.000 LIFT2 20150 6 2010-02-01 09:05:00.000 LIFT1 10315 7 2010-02-01 09:10:00.000 LIFT2 20510 8 2010-02-01 09:55:00.000 LIFT2 20810 9 2010-02-01 10:00:00.000 LIFT1 10480 10 2010-02-01 10:00:00.000 LIFT2 20885 11 2010-02-01 10:10:00.000 LIFT1 10530 12 2010-02-01 10:15:00.000 LIFT1 10575 13 2010-02-01 11:00:00.000 LIFT1 10725 14 2010-02-01 11:05:00.000 LIFT2 21445 15 2010-02-01 11:05:00.000 LIFT1 10755 16 2010-02-01 11:50:00.000 LIFT1 11295 17 2010-02-01 12:00:00.000 LIFT1 11325

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

## Example

Let’s look at the data for LIFT1.

 VehicleName StartTime EndTime Consumption TimeInMinutes ConsumptionPerMinute LIFT1 08:00:00 08:05:00 25 5 5 LIFT1 08:05:00 08:15:00 40 10 4 LIFT1 08:15:00 09:05:00 250 50 5 LIFT1 09:05:00 10:00:00 165 55 3 LIFT1 10:00:00 10:10:00 50 10 5 LIFT1 10:10:00 10:15:00 45 5 9 LIFT1 10:15:00 11:00:00 150 45 3,33333333333333 LIFT1 11:00:00 11:05:00 30 5 6 LIFT1 11:05:00 11:50:00 540 45 12 LIFT1 11:50:00 12:00:00 30 10 3

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.

## Author

This competition problem was authored by Johan Åhlén and based on a real world scenario.