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

Improved example
(two cte ---> one cte. join 3 tables in final SELECT ---> join 2 tables.) Note: trades table was used.

WITH
 add_cum AS (
SELECT t.*

, LAG(cumulative , 1 , 0)

          OVER( PARTITION BY position
                           , direction
                    ORDER BY time
              ) AS lag_cumul
 FROM  (SELECT t.*
             , SUM(quantity)
                  OVER( PARTITION BY position
                                   , direction
                            ORDER BY time
                      ) AS cumulative
         FROM  trades AS t
       ) AS t

, SUM(cumul - lag_cumul) AS qnt
, cumul
, hold_time
, SUM( hold_time * (cumul - lag_cumul) ) AS weighted_hold_time
, NULLIF(GROUPING(cumul) , 0)

• SUM( hold_time * (cumul - lag_cumul) ) / SUM(cumul - lag_cumul) AS weighted_avg_hold_time FROM (SELECT b.time , MAX(s.lag_cumul , b.lag_cumul) AS lag_cumul , MIN(s.cumulative , b.cumulative) AS cumul , s.time - b.time AS hold_time FROM add_cum AS s INNER JOIN add_cum AS b ON s.direction = 'S' AND b.direction = 'B' AND s.cumulative > b.lag_cumul AND b.cumulative > s.lag_cumul ) GROUP BY ROLLUP ( ( time , cumul , lag_cumul , hold_time ) ) ORDER BY time
  , cumul
  ;