【BigQuery】組み合わせ数の算出方法

-- まずは必要なデータだけ取得する
WITH brand_list AS (
  SELECT
    sync_date
    , user_id
    , brand
  FROM
    buy_data
)
-- 日付・ユーザーでグループ化し、ブランドを配列化
, brand_list_by_user AS (
  SELECT
    sync_date
    , user_id
    , ARRAY_AGG(brand) AS brand_arr
  FROM
    brand_list
  GROUP BY
    sync_date, user_id
)
-- 組み合わせを列挙(UU数の算出のためDISTINCTを入れている)
, brand_comb AS (
  SELECT DISTINCT
    user_id
    , brand
    , brand_together
  FROM
    brand_list_by_user
    , UNNEST(brand_arr) AS brand
    , UNNEST(brand_arr) AS brand_together
  WHERE
    brand < brand_together
  UNION ALL
  SELECT DISTINCT
    user_id
    , brand
    , brand_together
  FROM
    brand_list_by_user
    , UNNEST(brand_arr) AS brand
    , UNNEST(brand_arr) AS brand_together
  WHERE
    brand_together < brand
)
-- brandとbrand_togetherでグループ化し、UU数を算出
SELECT
  brand
  , brand_together
  , COUNT(*) AS uu
FROM
  brand_comb
GROUP BY
  brand, brand_together