I am currently facing the following issue: I have 1 Table with my Broker Trading Data similar to this:
TickerId Id Ticker Shares OrderType ... ... ... ... ... 01.01.20 ABC 5 ABC 500 Buy 01.01.20 ABC 6 ABC 250 Sell 01.01.20 ABC 7 ABC 250 Sell ... ... ... ... ...
The Goal is to say IF first OrderType (lowest Id where TradeId is the same) was a Buy, it’s a LONG Trade ELSE a Short Trade … output should be like this:
TickerId Position Ticker Volume (=SUM(Shares)) ... ... ... ... 01.01.20 ABC Long ABC 1000 ... ... ... ...
What am I missing? How can I construct my Query to accomplish this task?
Thanks for looking into this 😉
If you want to add this to all rows, use a window function. One method is:
select t.*, (case when first_value(orderType) over (partition by tickerid order by id) = 'Buy' then 'Long' else 'Short' end) as position from t;
If you just want one row per
tickerid, you can use aggregation:
select tickerid, (case when min(case when orderType = 'Buy' then id end) = min(id) then 'Long' else 'Short' end) as position from t group by tickerid;
The logic here is that the first “Buy” id is compared to the first “id”. If they are the same, you have a “Long” trade.