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.

## Task

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.