# Postgres window function to calculate summary position of orders

I tried windows functions, CTE, RECURSIVE but I still can't get my head around it

If I have rows of stock orders buy/sell and I want to know what was position after each trade.

If each next order is increasing existing position - I want to have average price,

If the order is decreasing - I want to calculate ProfitLoss and leave price of position as it was

If the order is changing direction of the position - I want to calculate ProfitLoss and new price of position

``````order_profit_loss = LEAST(ABS(qua), lag(ABS(position_qua))) * price
position_price = (LAG(position_price) * LAG(position_qua) - order_profit_loss) / position_qua
``````

DDL

``````CREATE TABLE trades
(
id VARCHAR(255) PRIMARY KEY NOT NULL,
symbol VARCHAR(10) NOT NULL,
price NUMERIC NOT NULL,
qua INTEGER NOT NULL,
dt TIMESTAMP WITH TIME ZONE
);

INSERT INTO public.trades (id, symbol, price, qua, dt) VALUES ('1', 'FB', 124.89, 116, '2016-07-29 23:01:46.334000');
INSERT INTO public.trades (id, symbol, price, qua, dt) VALUES ('2', 'FB', 123.56, -104, '2016-07-30 23:41:13.297000');
INSERT INTO public.trades (id, symbol, price, qua, dt) VALUES ('3', 'FB', 125.23, 20, '2016-07-31 00:20:41.092000');
INSERT INTO public.trades (id, symbol, price, qua, dt) VALUES ('4', 'FB', 123.56, -21, '2016-08-01 12:25:34.482000');
``````

How to get such outcome

``````id | symbol | price  | qua  |             dt             | order_profit_loss | position_qua | position_price
---+--------+--------+------+----------------------------+-------------------+--------------+----------------
1  | FB     | 124.89 |  116 | 2016-07-29 23:01:46.334+03 |                 0 |          116 |         124.89
2  | FB     | 123.56 | -104 | 2016-07-30 23:41:13.297+03 |           -138.32 |           12 |         124.89
3  | FB     | 125.23 |   20 | 2016-07-31 00:20:41.092+03 |                 0 |           32 |       125.1025
4  | FB     | 123.56 |  -21 | 2016-08-01 12:25:34.482+03 |          -32.3925 |           11 |       125.1025
``````

Solution:

``````CREATE OR REPLACE VIEW v_pos AS
WITH RECURSIVE pos AS
( SELECT * FROM (
SELECT DISTINCT ON (symbol)
t.*,  0::numeric  AS pnl,
qua         AS pos,
price       AS p_price
FROM public.trades AS t
ORDER BY symbol, dt
) AS starting
UNION ALL
SELECT
n.*,
c.pnl,
p.pos + n.qua,
CASE
WHEN p.pos > 0 AND n.qua > 0 THEN (p.p_price * p.pos + n.price * n.qua) / (p.pos + n.qua)
WHEN p.pos < 0 AND n.qua < 0 THEN (p.p_price * p.pos + n.price * n.qua) / (p.pos + n.qua)
WHEN p.pos / n.qua < 0 AND p.pos > n.qua THEN p.p_price
WHEN p.pos / n.qua < 0 AND p.pos < n.qua THEN n.price
ELSE 0
END
FROM
pos AS p,
LATERAL
( SELECT t.* FROM trades AS t
WHERE t.symbol = p.symbol AND t.dt > p.dt ORDER BY t.dt  LIMIT 1
) AS n,
LATERAL (
SELECT
CASE
WHEN p.pos < 0 AND n.qua > 0 AND -p.pos > n.qua THEN n.qua * (p.p_price - n.price)
WHEN p.pos < 0 AND n.qua > 0 AND -p.pos < n.qua THEN p.pos * (p.p_price - n.price)
WHEN p.pos > 0 AND n.qua < 0 AND p.pos > -n.qua THEN n.qua * (p.p_price - n.price)
WHEN p.pos > 0 AND n.qua < 0 AND p.pos < -n.qua THEN p.pos * (p.p_price - n.price)
ELSE 0
END pnl,
p.pos * p.price cb
) AS c
)
SELECT symbol, qua::BIGINT, price FROM (
SELECT
DISTINCT ON (symbol)
symbol,
pos qua,
p_price price
FROM pos
WHERE qua != 0
ORDER BY symbol, dt DESC
) as tmp
WHERE tmp.qua != 0
``````
• The `position_qua` is obviously a running total but you need to explain a bit how the other numbers (eg the -138.32 and the 125.1025) are calculated. Aug 3, 2016 at 20:05

## 1 Answer

Not sure if I have got all the logic right but you'll at least have a starting point:

``````WITH RECURSIVE pos AS
( SELECT *
FROM ( SELECT DISTINCT ON (symbol)
t.*,  0::numeric  AS order_profit_loss,
qua         AS position_qua,
price       AS position_price
FROM public.trades AS t
ORDER BY symbol, dt
) AS starting
UNION ALL
SELECT
n.id, n.symbol, n.price, n.qua, n.dt,
CASE WHEN n.qua < 0
THEN n.qua * (p.position_price - n.price)
ELSE 0
END                     AS order_profit_loss,
p.position_qua + n.qua  AS position_qua,
CASE WHEN n.qua < 0
THEN p.position_price
ELSE (p.position_qua * p.position_price + n.price * n.qua)
/ (p.position_qua + n.qua)
END                     AS position_price
FROM
pos AS p,
LATERAL
( SELECT t.*
FROM public.trades AS t
WHERE t.symbol = p.symbol
AND t.dt > p.dt
ORDER BY t.dt    LIMIT 1
) AS n
)
SELECT *
FROM pos
ORDER BY symbol, dt ;
``````
• Thank you VERY much, this is what I need. I will post complete version here. Aug 3, 2016 at 21:19