-- Contract chain-address pairs in jobs that fail multiple times
SELECT 
  chain_id,
  ENCODE(contract_address, 'hex') AS address_hex,
  COUNT(*) AS job_count
FROM verification_jobs
inner join verification_jobs_ephemeral vje on vje.id = verification_jobs.id
where error_code = 'no_match'
GROUP BY chain_id, contract_address
HAVING COUNT(*) > 1
ORDER BY job_count desc;