Superchain coverage

superchain_bigquery_coverage.sql

WITH date_spine AS (
  SELECT dt
  FROM UNNEST(GENERATE_DATE_ARRAY(
    '2025-08-01', -- START DATE
    '2025-10-01', -- END DATE 
    INTERVAL 1 DAY
  )) AS dt
),
tx AS (
  SELECT DISTINCT dt, 'transactions' AS table_name
  FROM `optimism_superchain_raw_onchain_data.transactions`
  WHERE dt BETWEEN DATE '2025-08-01' AND DATE '2025-10-01'
),
aa AS (
  SELECT DISTINCT dt, '4337_logs' AS table_name
  FROM `optimism_superchain_4337_account_abstraction_data.useroperationevent_logs_v2`
  WHERE dt BETWEEN DATE '2025-08-01' AND DATE '2025-10-01'
),
present AS (
  SELECT * FROM tx
  UNION ALL
  SELECT * FROM aa
),
tables AS (
  SELECT table_name
  FROM UNNEST(['transactions','4337_logs']) AS table_name
),
report AS (
  SELECT
    s.dt,
    t.table_name,
    (p.dt IS NOT NULL) AS has_rows
  FROM date_spine s
  CROSS JOIN tables t
  LEFT JOIN present p
    ON p.dt = s.dt AND p.table_name = t.table_name
  ORDER BY 1,2
)
SELECT *
FROM report
WHERE NOT has_rows