Re: SQL Puzzler, Capital Gains FIFO stack implementation

Roberto Devo wrote:
> 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

Use a sorted hash cluster.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)