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

Though, essentially same algorithm as the previous example, reduced some redundant expressions/phrases by adding nested subqueries.

WITH
 add_cum AS (
SELECT t.*

, cumulative - quantity 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(qnt) AS qnt
, cumul
, hold_time
, SUM(hold_time * qnt) AS weighted_hold_time
, NULLIF(GROUPING(cumul) , 0)

• SUM(hold_time * qnt) / SUM(qnt) AS weighted_avg_hold_time FROM (SELECT t.* , cumul - lag_cumul AS qnt 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 ) AS t ) GROUP BY ROLLUP ( ( time , cumul , hold_time ) ) ORDER BY cumul ;