SQLug.se Challenge 2012

By | October 1, 2015
0 Comment

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.

SQLug.se Challenge Award

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

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

Related Posts