Need some tips on query generating averages on time series data

From: TomGar <>
Date: Tue, 22 Oct 2013 19:35:52 -0700 (PDT)
Message-ID: <>

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        <-- SELL
GOOG          12634.0                    +100        <-- BUY
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. Received on Wed Oct 23 2013 - 04:35:52 CEST

Original text of this message