Johan Åhlén

Johan Åhlén's blog about life, universe and everything.

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

Comments

No Comments