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.
sqlug.se 