-- Get runtime and creation bytecodes by address
SELECT 
    cd.chain_id,
    cd.address,
    -- rec_runtime.code AS rec_runtime_bytecode,
    rec_runtime.code_hash as rec_runtime_hash,
    -- onc_runtime.code as onc_runtime_bytecode,
    onc_runtime.code_hash as onc_runtime_hash,
    -- onc_creation.code as onc_creation_bytecode,
    onc_creation.code_hash as onc_creation_hash,
    -- rec_creation.code AS rec_creation_bytecode,
    rec_creation.code_hash as rec_creation_hash
FROM contract_deployments cd
JOIN verified_contracts vc ON cd.id = vc.deployment_id
JOIN compiled_contracts cc ON vc.compilation_id = cc.id
join contracts on cd.contract_id = contracts.id 
JOIN code rec_creation ON cc.creation_code_hash = rec_creation.code_hash
JOIN code rec_runtime ON cc.runtime_code_hash = rec_runtime.code_hash
join code onc_creation on contracts.creation_code_hash = onc_creation.code_hash 
join code onc_runtime on contracts.runtime_code_hash = onc_runtime.code_hash
where cd.address=decode('B1A868BE8ABCB386400820355B3D6944AAD53224', 'hex');

SELECT COUNT(*) AS matching_contracts_count
FROM contracts
WHERE creation_code_hash = runtime_code_hash;

SELECT cd.chain_id, COUNT(*) AS contract_count
FROM contracts c
JOIN contract_deployments cd ON c.id = cd.contract_id
WHERE c.creation_code_hash = c.runtime_code_hash
GROUP BY cd.chain_id
ORDER BY contract_count DESC;