Query editor
WITH GTX_CORE_ADDRESSES AS (SELECTADDRESSFROM dune.elric.result_gtx_step_4_core_addresses), TOKEN_SENDERS AS (SELECTmt."from" AS TOKEN_SENDER, varbinary_substring(mt.data, 17, 20) AS GTX_HOT_WALLET, COUNT(DISTINCT mt.hash) AS TXN_COUNTFROM ethereum.transactions mtWHERE mt.BLOCK_NUMBER >= 12965000AND mt.TYPE = 'Legacy'AND mt.gas_limit = 90000AND mt.to IN (0x6b175474e89094c44da98b954eedeac495271d0f -- DAI, 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48 -- USDC, 0xdac17f958d2ee523a2206206994597c13d831ec7 -- USDT)AND varbinary_substring(mt.data, 17, 20) IN (SELECT ADDRESS FROM GTX_CORE_ADDRESSES)GROUP BY 1, 2), TOKEN_SENDERS_STAT AS (SELECTmt."from" AS TOKEN_SENDER-- 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 mt.type = 'Legacy' AND varbinary_starts_with(mt.data, 0xa9059cbb) THEN mt.hash END) AS LEGACY_TYPE_ERC20_COUNT, COUNT(DISTINCT CASE WHEN mt.gas_limit = 90000 AND varbinary_starts_with(mt.data, 0xa9059cbb) THEN mt.hash END) AS GAS_90K_ERC20_COUNT, COUNT(DISTINCT CASE WHEN varbinary_starts_with(mt.data, 0xa9059cbb) THEN mt.hash END) AS ERC20_TRANSFER_COUNT-- all ETH sent are in legacy type, COUNT(DISTINCT CASE WHEN mt.type = 'Legacy' AND mt.gas_limit = 21000 AND mt.VALUE <> 0 THEN mt.hash END) AS LEGACY_TYPE_ETH_COUNT, COUNT(DISTINCT CASE WHEN mt.gas_limit = 21000 AND mt.value <> 0 THEN mt.hash END) AS ETH_COUNTFROM ethereum.transactions mtWHERE mt.BLOCK_NUMBER >= 12965000AND mt."from" IN (SELECT TOKEN_SENDER FROM TOKEN_SENDERS)GROUP BY 1), GAS_STAT AS (SELECTmt.to AS TOKEN_SENDER, COUNT(DISTINCT "from") AS ETH_SENDER, COUNT(DISTINCT CASE WHEN mt."from" IN (SELECT ADDRESS FROM GTX_CORE_ADDRESSES) THEN "from" END) AS ETH_SENDER_FROM_KNOWN_GARANTEX, COUNT(DISTINCT CASE WHEN mt.TYPE = 'Legacy' THEN mt.hash END) AS LEGACY_TYPE_ETH_TRANSFER, COUNT(DISTINCT mt.to) AS ETH_RECEIVING_TXN_COUNTFROM ethereum.transactions mt-- gas funderWHERE BLOCK_NUMBER >= 12965000AND VALUE <> 0-- disregard self transferAND mt.to IN (SELECT TOKEN_SENDER FROM TOKEN_SENDERS)GROUP BY 1), GTX_DEPOSIT_ADDRESS AS (SELECTts.TOKEN_SENDER AS DEPOSIT_ADDRESSESFROM TOKEN_SENDERS ts