The national Swedish SQL Server developer championship was arranged or the third time in 2012. I was both the author of the competition problem as well as main responsible for running the competition that year. The competition problem 2012 was based on stock market trading (which I by coincidence had been working heavily with around that period).
Over 100 competition entries were sent in by participants and each entry was tested 25 times to give a very exact ranking. The winning solution made use of the new SQL Server 2012 high performance window functions.
Competition problem 2012
Task
A common strategy on the stock market is short selling (“blankning” in Swedish). It means that you sell shares that you have borrowed. If you can buy them back later at a lower price, you’ll make a profit.
A financial services company offers their customers this opportunity, but they charge a fee every time a customer goes short. The competition problem is to help the financial services company develop a solution that lists all cases where the customers “go short”. Each case begins where the customer’s accumulated quantity of a stock becomes negative and ends when the accumulated quantity reaches zero or higher again. These cases should be numbered and also the company wants a list of transactions per case.
The source data contains the following fields:
- TransId, a number which tells us the order of the transactions. It is a primary key.
- Konto (Account), is a customer number.
- Instrument, is a string of 6 characters that tells us which stock.
- Kvantitet (Quantity), is positive when buying and negative when selling.
Example:
TransId | Konto | Instrument | Kvantitet |
173891 | 552207 | ATCO A | 500 |
173892 | 552207 | ATCO A | -400 |
173893 | 552207 | ATCO A | -300 |
173994 | 552207 | ATCO A | 200 |
173995 | 552207 | ATCO A | -100 |
173996 | 552207 | ATCO A | 300 |
173997 | 552207 | ATCO A | -1000 |
173998 | 552207 | ATCO B | 100 |
174001 | 552207 | ATCO B | 200 |
174002 | 339104 | ATCO A | -1000 |
174055 | 339104 | ATCO A | 1100 |
174056 | 339104 | ATCO B | -1000 |
174057 | 339104 | ATCO B | 900 |
The result should have the following fields:
- TransId
- Konto (Account)
- Instrument
- AckKvantitet (Accumulated Quantity)
- BlankningsNr (Case number)
- RadNr (row number), number of the transaction within the case. Starts with 1 for every new case.
The result of our example would be:
TransId | Konto | Instrument | AckKvantitet | BlankningsNr | RadNr |
173893 | 552207 | ATCO A | -200 | 1 | 1 |
173994 | 552207 | ATCO A | 0 | 1 | 2 |
173995 | 552207 | ATCO A | -100 | 2 | 1 |
173996 | 552207 | ATCO A | 200 | 2 | 2 |
173997 | 552207 | ATCO A | -800 | 3 | 1 |
174002 | 339104 | ATCO A | -1000 | 1 | 1 |
174055 | 339104 | ATCO A | 100 | 1 | 2 |
174056 | 339104 | ATCO B | -1000 | 1 | 1 |
174057 | 339104 | ATCO B | -100 | 1 | 2 |
Your task is to make the search function as fast as possible (measured as duration).
Other restrictions in the competition:
- The solutions had to run on a SQL Server 2008 R2
- T-SQL or SQLCLR is allowed
- You are not allowed to make any changes to the MeterReadings table except creating non clustered indexes
- Indexed views are not allowed
Author
This competition problem was authored by Johan Åhlén and based on a real world scenario.
SQL Script & Test data
Winning solution
</pre> <pre>/*** Solution3 Christian Janssen */ WITH Step1 AS (SELECT tr.TransId, tr.Konto, tr.Instrument, AckKvantitet = sum(tr.Kvantitet) OVER (PARTITION BY tr.Konto, tr.Instrument ORDER BY tr.TransId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM dbo.Transaktioner AS tr), Step2 AS (SELECT s1.TransId, s1.Konto, s1.Instrument, s1.AckKvantitet, IsFirstInBlanking = CASE WHEN s1.AckKvantitet >= 0 OR isnull(Lag(s1.AckKvantitet,1) OVER (PARTITION BY s1.Konto, s1.Instrument ORDER BY s1.TransId),0) < 0 THEN 0 ELSE 1 END, IsNotInBlanking = CASE WHEN s1.AckKvantitet >= 0 AND isnull(Lag(s1.AckKvantitet,1) OVER (PARTITION BY s1.Konto, s1.Instrument ORDER BY s1.TransId),0) >= 0 THEN 1 ELSE 0 END FROM Step1 AS s1), Step3 AS (SELECT s2.TransId, s2.Konto, s2.Instrument, s2.AckKvantitet, BlankingsNr1 = sum(s2.IsFirstInBlanking) OVER (PARTITION BY s2.Konto, s2.Instrument ORDER BY s2.TransId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM Step2 AS s2 WHERE s2.IsNotInBlanking=0) SELECT s3.TransId, s3.Konto, s3.Instrument, s3.AckKvantitet, s3.BlankingsNr, RadNr = row_number() OVER (PARTITION BY s3.Konto, s3.Instrument, s3.BlankingsNr1 ORDER BY s3.TransId) FROM Step3 AS s3 ORDER BY s3.TransId GO /**** end of script :CJ: ***/