Normalized URL in bigquery for Next.js routes

query.sql

SELECT
  normalizedUrl,
  COUNT(1)
FROM (
  SELECT
    httpRequest.requestUrl,
    REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(httpRequest.requestUrl, r'/_next/data/[^/]+', ''), r'\d+', '[id]'), r'\.json', '') AS normalizedUrl
  FROM
    `greenmobility.cloudrun_requests.run_googleapis_com_requests`
  WHERE
    TIMESTAMP_TRUNC(timestamp, DAY) > TIMESTAMP("2023-01-28") )
WHERE
  normalizedUrl LIKE '%.com/admin/%'
#  AND normalizedUrl LIKE '%package%'
GROUP BY
  normalizedUrl
ORDER BY
  2 desc