# Re: Need some tips on query generating averages on time series data

From: <ecotaj_at_gmail.com>
Date: Wed, 23 Oct 2013 21:08:44 -0700 (PDT)

Thank you for attempting.

Here is my calculation of hold times and wted-avg hold times:

```time            qnt        cumul   hold_time        wted-hold-time
12459.6        -100        -100            174.4             17440
12464.8        -100        -200            171.4             17140
12568.3        -100        -300             67.9              6790
12568.3        -200        -500            109.7             21940
12634           100        -400
12636.2         100        -300
12636.2         100        -200
12678           200           0
totalqnt  total-w-h-t  wted-avg-hold-time
500        63310              126.62

```

As you can see, I had to break down some trades into smaller quantities in order to match the buy-sell and come up with the holdtime.

And as you can see, the order of first in first out is important to the calc. If I were to use LIFO, my numbers would be slightly different.

_at_Peter: you make an assumption that I was the guy that lost \$5B at JPM ;-) in a single day of trading.

On Wednesday, October 23, 2013 1:12:33 AM UTC-5, Lennart Jonsson wrote:
> On 10/23/2013 04:35 AM, TomGar wrote:
>
> I'm not entirely sure how you define hold time for a stock, is it per
> share? Otherwise why is rule 4 necessary? Can you provide the expected
> result for the sample data above?
>
> I don't have access to an Oracle instance so I ran on a competitor:
>
> with T(stock, ts, qnt) as (
> values ('GOOG', 12459.6, -100)
> , ('GOOG', 12634.0, 100)
> , ('GOOG', 12636.2, 200)
> , ('GOOG', 12464.8, -100)
> , ('GOOG', 12568.3, -300)
> , ('GOOG', 12678.0, 200)
> )
>
> select stock, ts, qnt, sum(qnt) over (partition by stock
> order by ts) as cumulative_sum
> from t;
>
> What is the average hold times above?
>
> /Lennart
Received on Thu Oct 24 2013 - 06:08:44 CEST

Original text of this message