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
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.