sql arrays, event occurrences with respect prior event

so FLATTEN, ARRAY_SLICE and ARRAY_SIZE are the primary tools needed here.

The CTE is just to fake a data table, so flattening the array loops across the array which we alias as a. We could at this point to a sub-select to see the next layer, but we can just join to the results of that straight away, so I have. Thus we that the tail of the array and flatten that also, now we have our pairs, which we can count

WITH data AS (
    SELECT parse_json(column1) as array FROM VALUES
      ( '["event A", "event B", "event C", "event D", "event E"]' ),
      ( '["event A", "event D", "event N"]' ),
      ( '["event C", "event E", "event P"]' ),
      ( '["event C", "event E", "event Q"]' )
)
SELECT 
    a.value as e_s
    ,t.value as e_o
    ,count(*) as frequency
FROM data d,
    table(flatten(input=> d.array)) a,
    table(flatten(input=> array_slice(d.array, a.index+1, ARRAY_SIZE(d.array)))) t
GROUP BY 1,2
ORDER BY 1,2;

gives:

E_S E_O FREQUENCY
"event A"   "event B"   1
"event A"   "event C"   1
"event A"   "event D"   2
"event A"   "event E"   1
"event A"   "event N"   1
"event B"   "event C"   1
"event B"   "event D"   1
"event B"   "event E"   1
"event C"   "event D"   1
"event C"   "event E"   3
"event C"   "event P"   1
"event C"   "event Q"   1
"event D"   "event E"   1
"event D"   "event N"   1
"event E"   "event P"   1
"event E"   "event Q"   1

a longer version where each step is more explicit and one at a time is:

SELECT f.e_s,
    f.e_o,
    count(*) as frequency
FROM (    
    SELECT e.e_s,
        t.value as e_o
    FROM (

        SELECT
            d.array,
            a.value as e_s,
            array_slice(d.array, a.index+1, d.len) as tail
        FROM (
            SELECT array,
                ARRAY_SIZE(array) as len
            FROM data
        ) d, 
            TABLE(FLATTEN(input=> d.array)) a
    ) e,
        TABLE(FLATTEN(input=> e.tail)) t
) f
GROUP BY 1,2
ORDER BY 1,2;

I wasn't quick enough to beat Simeon to it but we ended up using different approaches anyway so I guess pick which one suits you best!

I flatten the array into rows within a CTE and then join the CTE back onto itself then sum up the results.

The Query

with flat as (
    select *
    from test_table,
         table (flatten(test_table.col_x)) f
)
select
    a.value  as E_S,
    b.value  as E_O,
    count(1) as FREQUENCY
from flat a
         join flat b on a.seq = b.seq and a.INDEX < b.INDEX
group by a.value, b.value
order by a.value, b.value

Full Example

-- create sample table
create or replace transient table test_table
(
    col_x array
);

-- insert sample data
insert overwrite into test_table (col_x)
SELECT
    parse_json(column1)
FROM
VALUES ('["event A", "event B", "event C", "event D", "event E"]'),
       ('["event A", "event D", "event N"]'),
       ('["event C", "event E", "event P"]'),
       ('["event C", "event E", "event Q"]')
;

with flat as (
    select *
    from test_table,
         table (flatten(test_table.col_x)) f
)
select
    a.value  as E_S,
    b.value  as E_O,
    count(1) as FREQUENCY
from flat a
         join flat b on a.seq = b.seq and a.INDEX < b.INDEX
group by a.value, b.value
order by a.value, b.value
;

Results

+---------+---------+---------+
|E_S      |E_O      |FREQUENCY|
+---------+---------+---------+
|"event A"|"event B"|1        |
|"event A"|"event C"|1        |
|"event A"|"event D"|2        |
|"event A"|"event E"|1        |
|"event A"|"event N"|1        |
|"event B"|"event C"|1        |
|"event B"|"event D"|1        |
|"event B"|"event E"|1        |
|"event C"|"event D"|1        |
|"event C"|"event E"|3        |
|"event C"|"event P"|1        |
|"event C"|"event Q"|1        |
|"event D"|"event E"|1        |
|"event D"|"event N"|1        |
|"event E"|"event P"|1        |
|"event E"|"event Q"|1        |
+---------+---------+---------+