Query editor
WITH ADDRESS_CANDIDATES_RAW AS (SELECTmt."from" AS FROM_ADDRESS-- only transacted with DAI/USDC/USDT, COUNT(DISTINCT CASE WHEN varbinary_starts_with(mt.data, 0xa9059cbb) THEN mt.to END) AS DISTINCT_TOKEN_ADDRESS, COUNT(DISTINCT (CASE WHEN varbinary_starts_with(mt.data, 0xa9059cbb) AND mt.to IN (0x6b175474e89094c44da98b954eedeac495271d0f -- DAI, 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48 -- USDC, 0xdac17f958d2ee523a2206206994597c13d831ec7 -- USDT) THEN mt.to END) )AS DISTINCT_INSCOPE_TOKEN_ADDRESS-- all ERC20 are with 90k gas and legacy type, COUNT(DISTINCT CASE WHEN type = 'Legacy' AND varbinary_starts_with(mt.data, 0xa9059cbb) THEN hash END) AS LEGACY_TYPE_ERC20_COUNT, COUNT(DISTINCT CASE WHEN gas_limit = 90000 AND varbinary_starts_with(mt.data, 0xa9059cbb) THEN hash END) AS GAS_90K_ERC20_COUNT, COUNT(DISTINCT CASE WHEN varbinary_starts_with(mt.data, 0xa9059cbb) THEN hash END) AS ERC20_TRANSFER_COUNT-- all ETH sent are in legacy type, COUNT(DISTINCT CASE WHEN type = 'Legacy' AND gas_limit = 21000 AND value <> 0 THEN hash END) AS LEGACY_TYPE_ETH_COUNT, COUNT(DISTINCT CASE WHEN gas_limit = 21000 AND VALUE <> 0 THEN hash END) AS ETH_COUNT-- should not have other outward txns, COUNT(DISTINCT hash) AS TOTAL_TXN_COUNTFROM ethereum.transactions mtWHERE mt.block_number >= 12965000 -- post London-- remove other known exchangesAND mt."from" NOT IN (-- gas funded from known cex gas supplierSELECTet.toFROM ethereum.transactions etINNER JOIN cex.addresses caON et."from" = ca.addressWHERE et.value <> 0AND lower(ca.distinct_name) LIKE '%gas supplier%'-- sent tokens to other known cexUNION ALLSELECTet."from"FROM erc20_ethereum.evt_transfer etWHERE et.to IN (SELECT address FROM cex.addressesUNION ALLSELECT * FROM (VALUES (0x9797a7eab75a9e872838e2a5105059ddfd0568b9)))-- manually adding exceptional casesUNION ALLSELECTet.toFROM ethereum.transactions etWHERE et."from" IN (0x7a1f519064b7e5b7db931f84977b5c2278e8a5e5 -- busha.co gas funder, 0x73ee21be16426fa73780492feb497b922dd06c2f -- busha.co gas funder, 0xabc74170f3cb8ab352820c39cc1d1e05ce9e41d3 -- paribu gas funder, 0x876eabf441b2ee5b5b0554fd502a8e0600950cfa -- bitfinex gas funder, 0x77134cbc06cb00b66f4c7e623d5fdbf6777635ec -- bitfinex gas funder, 0xce0df2ba6c2bd425f3ffbbea708f0b5f21ae1e6a -- bityard, 0xe4617f0ac3e9105e906c774d4dfd5ef5e37705a6 -- bitkan))GROUP BY 1), ADDRESS_CANDIDATES AS (SELECT