Таблица соединения BigQuery после оператора case в подзапросе

Я пытаюсь написать запрос, который можно запускать еженедельно или ежемесячно, с некоторыми числовыми изменениями, которые можно сделать в таблицах Google. Однако, чтобы это сработало, мне нужно изменить названия некоторых продуктов, чтобы они были более единообразными и соответствовали данным из таблиц Google. В этом случае что-то вроде старшего или младшего перед названием продукта должно стать одним конкретным именем, например, «инженер» вместо старшего инженера, инженера, младшего инженера и т.д. пункт, следующий за ним.

Краткий сводный код того, как я пытаюсь это сделать, таков (группа по и т. д. Не указана):

SELECT 
      t.fullVisitorId,
      t.product,
      t.transaction,
      t.transactiondate,
      cc.productvalue   
FROM t LEFT JOIN
`othertable` AS cc ON t.product = cc.product    
FROM(
SELECT
CASE 
WHEN product = '%engineer%' THEN 'Engineer'
END AS product,
fullVisitorId,
transaction,
value,
transactiondate
    FROM ( SELECT
     fullVisitorId,
              hit.transaction.transactionId AS transaction,
              prod.v2ProductName AS product,
           FORMAT_TIMESTAMP("%Y-%m-%d", TIMESTAMP_SECONDS(SAFE_CAST(visitStartTime AS INT64)), "Europe/London") AS transactiondate
            FROM
          `tableID.ga_sessions_*` AS t
        CROSS JOIN
          UNNEST(hits) AS hit            
        WHERE
          _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', '2018-11-01') 
          AND FORMAT_DATE("%E4Y%m%d", '2018-11-30')