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:

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:

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

SQLUGChallenge2012_Setup

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: ***/