SQLug.se challenge 2010 - the Swedish national championship for SQL Server developers
The Swedish national championship for SQL Server developers, SQLug.se challenge 2010, is now finished. The competition has been running on the Swedish SQL Server User Group's website, but I publish the competition problem also on my blog in case you want to test your skills on it!
The problem comes from a real world scenario that I've faced as a Business Intelligence consultant, but the data is randomly generated. The goal of the competition was to make the fastest solution. All the entries and performance statistics are published on our user group's website.
Consider a table of meter reading from a number of vehicles. The meter readings are a running total and measure fuel consumption (in centiliters).
| ID |
Tid |
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 |
In this example LIFT1 consumed 25 cl between 08:00 and 08:05. The consumption rate then was 5 cl per minute.
The task is to calculate the time weighted average consumption rate, in centiliters per minute, for the 20% periods that had the highest consumption rate (round the number of periods up to closest integer so you will for example include 3 periods if there are a total of 11 periods). If more than one period has the same consumption, use the latest period.
See example below 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 |
We have a total of 10 periods so 20% would be two periods. The two periods with highest consumption rate had 12 cl/minute and 9 cl/minute and their durations were 45 and 5 minutes. The time weighted average consumption rate for LIFT1 would be:(12 x 45 + 9 x 5) / (45 + 5) = 11,7 cl/minute. Same calculation for LIFT2 gives an average consumption rate of 15 cl/minut. The result should be written to a table called ConsumptionSummary.
| VehicleName |
Top20PctAveragePerMinute |
| LIFT1 |
11,7 |
| LIFT2 |
15 |
Files
Correct results for the smaller testdata
| VehicleName |
Top20PctAveragePerMinute |
| LIFT1 |
8202.18834892846 |
| LIFT2 |
27949.9640499379 |
| LIFT3 |
13310.5810794649 |
| LIFT4 |
19097.1263597138 |
Winners of the competition were:
|
1 |
Henrik Nilsson, Brummer & Partners |
9,50 s |
|
2 |
Zhong Yu, B3IT |
14,17 s |
|
3 |
Sergey Klimkevich, Memnon Networks AB |
16,20 s |
|
4 |
Magnus Sörin, Avanade |
35,32 s |
|
5 |
Peter Larsson, Developer Workshop |
58,87 s |