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

From: Piyush <piyush.mehta_at_gmail.com>
Date: Wed, 23 Oct 2013 21:02:29 -0700 (PDT)

Hello Lennart:

Thank you for attempting.

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

```time	 qnt	cumul   hold_time  held-qnt  wted-hold-time
12459.6	-100	-100	    174.4	100           17440
12464.8	-100	-200	    171.4	100	      17140
12568.3	-100	-300	     67.9	100	       6790
12568.3	-200	-500	    109.7	200	      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:
>
> 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:02:29 CEST

Original text of this message