FiFO with SQL

I am going to create small system/website that will allow me to manage my stocks, and monitor the portfolio. I do not have much of it, but I cannot access the data from wherever I want and it is not so dynamic.

Beside the design itself I came across a problem of recreating a FIFO (first in first out) type of queue. Why that? Because if I buy and sell stock and I want to see the current value of my opened position that is how it should be calculated: the first stock I buy that is also the first that is out of the book when I sell. The solution below was made in PostgreSQL 9.1.

So, assume we have a table called transactions consisting of fields:

id – trade id
tr_date – trade date
price – stock price
type – 1-sell; 0-buy
amount – number of stocks
is_opened – flag indicating that transactions have not been closed
id_stock – foreign key to stock table
id_user – foreign key to user table

SELECT
     id, tr_date, price, amount_left
   , amount_left*price AS value
FROM
(
  SELECT
      id, tr_date, price, amount
      , GREATEST(SUM(amount)
        OVER(ORDER BY type, tr_date), 0)
        AS amount_left
  FROM
  (
    SELECT id, tr_date, type, price, amount FROM
    (
     SELECT
        0 as id
      , to_date('1900-01-01','YYYY-MM-DD') AS tr_date
      , 0 As price
      , type
      , -SUM(amount) AS amount
     FROM transactions
     WHERE is_opened IS TRUE
       AND type IS FALSE
       AND id_user = 3
       AND id_stock = 3
     GROUP BY type
    UNION
    SELECT
       id, tr_date, price, type, amount
    FROM transactions
    WHERE is_opened IS TRUE
      AND type IS TRUE
      AND id_user = 3
      AND id_stock = 3
    ) AS x
  ) AS y
) AS z
WHERE amount_left > 0
ORDER BY tr_date;

How does it work? For a given id_user = 3 and id_stock = 3 take all transactions that are opened, is_opened IS TRUE – that is a common part of two SELECTs joined by UNION statement. The difference between them is that the upper one sums up all the sell transactions, whereas the bottom one has only buy transactions.

For example.

id |   tr_date  | price | type | amount
----+------------+-------+------+--------
0  | 1900-01-01 |   0   |   f  |  -150
1  | 2013-09-01 |   7   |   t  |   100
2  | 2013-09-03 |   8.1 |   t  |    50
6  | 2013-06-05 |   7.8 |   t  |    60

In the next step, just for clarity, I pick all the important values. The next one is a window function that given the order by type and tr_date makes all the calculations. This way in new column, amount_left, we get a current number of a given stock we own.


id |   tr_date  | price | amount | amount_left
----+------------+-------+--------+-------------
0  | 1900-01-01 |   0   |   -150 |    0
6  | 2013-06-05 |   7.8 |     60 |    0
1  | 2013-09-01 |   7   |    100 |    10
2  | 2013-09-03 |   8.1 |     50 |    60

The very last step just picks the transactions that have some shares left in the portfolio, strong>amount_left>0, and orders them by trade date, tr_date.

id |   tr_date  | price | amount_left | value
----+------------+-------+-------------+--------
1  | 2013-09-01 |    7  |       10    |    70
2  | 2013-09-03 |   8.1 |       60    |   486

From the performance point of view, assuming I have many users, many stock, and much more transactions. I have created two partial indexes on “opened” buy/sell transactions. By opened transaction I understand those transactions that have not been closed. Transaction will be marked as closed if after entering new sell transaction all amounts of previous sell/buy transactions sum up to 0. In other words all transactions are OUT. Below the code for the partial indexes.

CREATE INDEX CONCURRENTLY idx_trans_user_stock_f
ON transactions(id_user, id_stock)
WHERE is_opened IS TRUE AND type IS FALSE;

CREATE INDEX CONCURRENTLY idx_trans_user_stock_t
ON transactions(id_user, id_stock)
WHERE is_opened IS TRUE AND type IS TRUE;

I thought about making bitmap indexes, but as I found out they are not implemented in Postgres. Besides, doing two partial indexes that cover exclusive parts of table seems a reasonable idea.