SELECT ACCUMULATE(
 order_amount,
 (v)      -> [v, 1],
 (s, v)   -> [s[0] * v, s[1] + 1],
 (s1, s2) -> [s1, s2],
 (s)      ->  POWER(s[0], 1/s[1])
) AS geometric_mean
FROM orders_fact;


/* Example */
with orders_fact as (
  select 10 as order_amount
  union all
  select 20 as order_amount
  union all
  select 30 as order_amount
  union all
  select 40 as order_amount
  union all
  select 50000000 as order_amount
  union all
  select 60 as order_amount

)
SELECT ACCUMULATE(
    order_amount, -- input column
    (v)              -> [v, 1], -- init
    (state, v)       -> [state[0] * v, state[1] + 1], -- fold / accumulate
    (state1, state2) -> [state1[0] + state2[0], state1[1] + state2[1]], -- merge / combine
    (state)          -> POWER(state[0], 1/state[1])  -- terminate
) AS geometric_mean
, EXP(SUM(LN(order_amount))/COUNT(order_amount)) as geometricmeanalt -- alternate way of calculating the geometric mean
FROM orders_fact;

/*

INIT: Sets the starting state for the aggregation using an initial lambda expression
FOLD / ACCUMULATE: Defines the core logic to update the state as each row Is processed.
MERGE / COMBINE: Merges partial aggregations calculated in parallel in to a single global state.
TERM: Executes the final transformation to return the result in the desired format

*/