Find the open and the most updated trade positions

[yunpeng]

Hi All,

I have a simplified dataset like this. Assuming today is 20201216. Task is to find all the open trades as of today.

Several characteristics of the trade data

1. Every day there could be new trades booked (like C)
2. If a trade is not terminated today, it is considered open trades and should be included in the final result (like B)
3. If there's termination for a trade as of today, then this trade should NOT be included in the final result (like A)
4. If there's trade update on the same trade, only the latest update will be included in the final result (like D)
5. For example, if a trade was booked 2 weeks ago and has no update/terminate since then it should still be considered as open trade

I've been thinking about the best logic to address this and in a scalable way. Please let me know if you have any good idea. Thanks!

Also, the original data comes from SQL database. I'm planning to download the fullbook of data for the past 30 days to analyze. But you know a better way of writing the relevant SQL to reduce incoming data size, that'd be much appreciated as well.

Date TradeID Action VersionID

20201215	A	New	1
20201216	A	Terminated	2
20201215	B	New	1
20201216	C	New	1
20201216	D	New	1
20201216	D	Update	2

[Tyro_abc · Accepted solution]

Hi @yunpeng 

From your data, it looks like we can use the "version" to sort the data set and take the latest record by trade id. However, I still sorted the data by date descending and version descending and then took the first record by trade ID. This way we would get the latest record for trade ID and then simply apply the filter as per our need. 

If you have millions of records in database, it would be easier to do the same in DB. You can use Rank window function in database and take the first rank only.