Your Enterprise Gatekeeping Just Made Me Build a Better Solution

SQL++
,
5

Also check validity of SQL because you are using basic aggregates and will not able to use any non group by columns

As your description The following query might satisfy your requirement without Window functions.
FYI: Key here is MIN/MAX calculated over array [t._t, t._v3] and from final result extract index 1

WITH range_start AS (${ts_start}),
     range_end AS (${ts_end})
SELECT
  ts,
  MIN(t._v2) AS low,
  MAX(t._v1) AS high,
  SUM(t._v4) AS volume,
  MIN([t._t, t._v3])[1] AS open,
  MAX([t._t, t._v4])[1] AS close
FROM `${cbConnection.bucketName}` AS d
UNNEST _timeseries(d, {'ts_ranges': [range_start, range_end]}) AS t
LET ts = IDIV(t._t, ${intervalMilliseconds})
WHERE d.ticker = '${symbol}'
GROUP BY ts;
1 Like