┌──────┐           
        ▼      │           
      ┌──►Fold─┴─┐       
Init──┤          ├─►Merge
      └──►Fold─┬─┘    │  
        ▲      │      ▼  
        └──────┘    Term     


/*

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

*/


/* 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;