mattjwj.sql

WITH

t AS (
  SELECT 
    '20190101' AS _TABLE_SUFFIX,
    DATE '2019-01-01' AS date,
    1 AS fullVisitorId,
    1 AS visitId,
    [
      STRUCT(
        'PAGE' AS type,
        1 AS hitNumber,
        STRUCT(
          '(not set)' AS contentGroup1
        ) AS contentGroup,
        STRUCT(
            'join/complete' AS pagePath
        ) AS page
      ) 
    ] AS hits
  
  UNION ALL
  
  SELECT
    ...
) 


SELECT
  date,
  fullVisitorId,
  visitId,
  STRING_AGG(
    CASE 
      WHEN hit.contentGroup.contentGroup1 = '(not set)' THEN
        CASE
          WHEN hit.page.pagePath LIKE '%join/complete%' THEN 'join_complete' 
          ELSE hit.contentGroup.contentGroup1
        END
    END, 
    '#' 
    ORDER BY 
      hit.hitNumber
  )
FROM 
  t
LEFT JOIN
  UNNEST(hits) AS hit
WHERE
  _TABLE_SUFFIX BETWEEN '20190101' AND '20190101'
  AND hit.type='PAGE'
GROUP BY
  1, 2, 3
ORDER BY 
  1, 2