SQL Puzzler, Capital Gains FIFO stack implementation

From: Roberto Devo <jdevroberto_at_yahoo.com>
Date: 26 Sep 2004 15:26:48 -0700
Message-ID: <b4942975.0409261426.7117b5ab@posting.google.com>

Hi,

I'm working on an SQL puzzler I call the Capital Gains FIFO stack.

Here is what the data looks like:

14:57:14 SQL> l
  1 SELECT
  2 tkr

  3  ,action
  4  ,quant
  5  ,price
  6  ,tradeDate
  7  ,SUM(
  8    CASE
  9	 WHEN action = 'BUY'  THEN quant
 10	 WHEN action = 'SELL' THEN -quant
 11	 WHEN action = 'SSH'  THEN -quant
 12	 WHEN action = 'BTC'  THEN quant

 13 END
 14 )
 15 OVER(ORDER BY tkr, transID ) "quantRunningTotal"  16 FROM opxTradesST
 17 WHERE tradeDate < '2004-09-18'
 18 AND tradeDate > '2004-08-23'
 19 AND tkr='RIMM'
 20* ORDER BY tkr, transID
14:57:15 SQL> / TKR ACTION QUANT PRICE TRADEDATE quantRunningTotal

------- --------- ---------- ---------- ---------- -----------------
RIMM	BUY		 400	  61.51 2004-09-02		 400
RIMM	BUY		 100	  61.51 2004-09-02		 500
RIMM	BUY		 500	  61.63 2004-09-02		1000
RIMM	SELL		 700	  72.89 2004-09-17		 300
RIMM	SELL		 100	  72.87 2004-09-17		 200
RIMM	SELL		 157	  72.87 2004-09-17		  43
RIMM	SELL		  43	  72.87 2004-09-17		   0

7 rows selected.

Elapsed: 00:00:00.58
14:57:23 SQL> You can see from the above data that the trader started buying RIMM on Sep 2.
Then, he started selling it on Sep 17.

The SQL puzzler is this:

Write a query which reports on the above data as a FIFO stack:

TKR     ACTION         QUANT      PRICE TRADEDATE   ACTION        
QUANT      PRICE TRADEDATE

------- --------- ---------- ---------- ---------- --------- ---------- ---------- ----------

RIMM    BUY              400      61.51 2004-09-02  SELL            
400      72.89 2004-09-17
RIMM    BUY              100      61.51 2004-09-02  SELL            
100      72.89 2004-09-17
RIMM    BUY              200      61.63 2004-09-02  SELL            
200      72.89 2004-09-17
RIMM    BUY              100      61.63 2004-09-02  SELL            
100      72.87 2004-09-17
RIMM    BUY              157      61.63 2004-09-02  SELL            
157      72.87 2004-09-17
RIMM    BUY               43      61.63 2004-09-02  SELL             
43      72.87 2004-09-17

Notice how the first SELL of 700 shares got split into 3 pieces: 400, 100, 200
Notice how the last BUY of 500 shares got split into 4 pieces: 200, 100, 157, 43

Here is useful SQL to get you started:
DROP TABLE dropme;
CREATE TABLE dropme
(
tkr VARCHAR(4)
,action VARCHAR(4)
,quant NUMBER
,price NUMBER
,tradedate VARCHAR(10)

)
/

INSERT INTO dropme VALUES('RIMM', 'BUY', 400, 61.51, '2004-09-02');
INSERT INTO dropme VALUES('RIMM', 'BUY', 100, 61.51, '2004-09-02');
INSERT INTO dropme VALUES('RIMM', 'BUY', 500, 61.63, '2004-09-02');

INSERT INTO dropme VALUES('RIMM', 'SELL', 700, 72.89, '2004-09-17');
INSERT INTO dropme VALUES('RIMM', 'SELL', 100, 72.87, '2004-09-17');

INSERT INTO dropme VALUES('RIMM', 'SELL', 157, 72.87, '2004-09-17'); INSERT INTO dropme VALUES('RIMM', 'SELL', 43, 72.87, '2004-09-17');

SELECT
tkr
,action
,quant
,price
,tradedate

FROM dropme
ORDER BY tradedate
/

-moi Received on Sun Sep 26 2004 - 17:26:48 CDT