I need to generate average hold times for various stock of companies as follows:
The data looks like:
stock timestamp (sec) quantity
----- ---------------- ---------
GOOG 12459.6 -100 <-- SALE
GOOG 12634.0 +100 <-- PURCHASE
GOOG 12636.2 +200
GOOG 12464.8 -100
GOOG 12568.3 -300
GOOG 12678.0 +200
....
....
The rules are
1. begin and end day with balance 0
2. can short sell, i.e. can sell shares even if balance is currently 0
3. hold time is defined as number of seconds stock was held before it was sold
4. first stock purchased are sold first
I need to generate the average hold times seconds per share. I'd prefer to do this using SQL and NOT a procedure.
Any tips on how to go about calculating this? I have looked at various analytic functions, but still not sure.
Thank you.