Which queries do we run over and over with the same result?

-- Group queries by normalised shape: strip literals, collapse whitespace.
WITH normalised AS (
  SELECT
    REGEXP_REPLACE(
      REGEXP_REPLACE(
        REGEXP_REPLACE(UPPER(query), r"'[^']*'", "'?'"),
        r'\b\d+\b', '?'
      ),
      r'\s+', ' '
    )                          AS query_shape,
    total_bytes_billed,
    total_slot_ms,
    cache_hit
  FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
    AND job_type = 'QUERY'
    AND state = 'DONE'
    AND error_result IS NULL
)
SELECT
  COUNT(*)                                                  AS runs,
  COUNTIF(cache_hit)                                        AS cached_runs,
  SUM(total_bytes_billed)                                   AS total_bytes_billed,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4) * 6.25, 2)   AS est_on_demand_usd,
  ROUND(AVG(total_slot_ms) / 1000, 1)                       AS avg_slot_seconds,
  SUBSTR(query_shape, 0, 300)                               AS shape_preview
FROM normalised
GROUP BY query_shape
HAVING runs >= 5
ORDER BY total_bytes_billed DESC
LIMIT 50;