Стандартный SQL Google Bigquery - Как: если обращение содержит артикул, тогда выберите число попаданий, иначе макс (число попаданий)?

Я бы посоветовал провести группировку и найти максимальное количество совпадений в подзапросе. Если вы собираетесь использовать агрегатную функцию, такую ​​как MAX(), в предложении select, вам необходимо сгруппировать или иметь агрегатные функции для других полей в select. Может быть полезно выполнять агрегированные подзапросы с использованием общих табличных выражений.

WITH data AS (
  SELECT
    CONCAT(fullvisitorid, CAST(visitid AS string)) AS unique_session_id,
    h.hitNumber,
    h.item.transactionid,
    h.item.itemrevenue/POW(10,6) AS itemRevenue,
    h.item.productSku
  FROM
    `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`,
    UNNEST(hits) AS h
),
max_hits AS (
  SELECT
    unique_session_id,
    MAX(hitNumber) AS max_hit_number
  FROM data
  GROUP BY 1
)
SELECT
  d.unique_session_id,
  CASE
    WHEN d.productSku IS NOT NULL THEN d.hitNumber
    ELSE m.max_hit_number 
  END,
  d.transactionid,
  d.itemrevenue,
  d.productSku
FROM
  data AS d JOIN max_hits AS m
ON d.unique_session_id = m.unique_session_id