Wix Origins passing INP using 3P Categories

gistfile1.txt

CREATE TEMP FUNCTION IS_GOOD(good FLOAT64, needs_improvement FLOAT64, poor FLOAT64) RETURNS BOOL AS (
  good / (good + needs_improvement + poor) >= 0.75
);

WITH WIX_ORIGINS AS (
  SELECT
    origin,
    fast_inp,
    good_inp
  FROM (
    SELECT
      rank,
      CONCAT(origin, '/') AS origin,
      #IF(device = 'desktop', 'desktop', 'mobile') AS client,
      fast_inp,
      IS_GOOD(fast_inp, avg_inp, slow_inp) AS good_inp
    FROM
      `chrome-ux-report.materialized.metrics_summary`
    WHERE
      date = '2022-12-01'
    AND fast_inp IS NOT NULL
  ) JOIN (
    SELECT DISTINCT
      page as origin,
      technologies.technology as app,
      category
    FROM
      `httparchive.all.pages`, 
      UNNEST(technologies) as technologies,
      UNNEST(technologies.categories) as category
    WHERE
      date = "2022-12-01" AND
      is_root_page = TRUE AND
      technologies.technology = 'Wix' AND
      category = "CMS"
  ) USING (origin)
  #WHERE rank = 50000000
)
SELECT
  category,
  COUNT(DISTINCT origin) AS origins,
  ROUND(SAFE_DIVIDE(COUNT(DISTINCT IF(good_inp, origin, NULL)),COUNT(DISTINCT origin)),4) AS origins_with_good_inp
FROM WIX_ORIGINS
JOIN (
  SELECT DISTINCT
    page as origin,
    category
  FROM
    `httparchive.all.pages`, 
    UNNEST(technologies) as technologies,
    UNNEST(technologies.categories) as category
  WHERE
    date = "2022-12-01" AND
    is_root_page = TRUE AND
    technologies.technology IS NOT NULL AND
    technologies.technology != '' AND
    (
      category IN (
        "Advertising",
        "Analytics",
        "Authentication",
        "Cookie compliance",
        "JavaScript frameworks",
        "Maps",
        "Tag managers",
        "Video players"
      )
    OR
      (
        technologies.technology = "Next.js"
      AND
        category = "Web frameworks"
      )
    )
) USING (origin)
GROUP BY 1
ORDER BY origins DESC