Example: UDF in BigQuery

gistfile1.txt

CREATE TEMPORARY FUNCTION allPairs(posts ARRAY<INT64>)
RETURNS ARRAY<STRUCT<post1 INT64, post2 INT64>>
LANGUAGE js AS """
  const len = posts.length;
  // assume `posts` are sorted
  let i, j;
  let pairs = [];
  for(i=0; i < len-1; i++) {
    for(j=i+1; j < len; j++) {
      pairs.push({post1: posts[i], post2: posts[j]});
    }
  }
  return pairs;
""";

SELECT 
  pairs
FROM (
  SELECT 
    allPairs(ARRAY_AGG(post_id ORDER BY post_id)) AS pairs
  FROM subscriptions.speaker_likes_2017 
  GROUP BY id
) t, t.pairs AS pairs