Query editor
WITH target_contracts AS (SELECT address AS contract_address, block_time AS created_atFROM ethereum.creation_tracesWHERE block_number > 4832000 AND block_number <= 11565019AND block_month >= DATE '2018-01-01' AND block_month <= DATE '2021-01-01'),eth_in AS (SELECT t."to" AS addr, SUM(CAST(t.value AS DOUBLE)) / 1e18 AS total_inFROM ethereum.traces tINNER JOIN target_contracts tc ON t."to" = tc.contract_addressWHERE t.success = true AND CAST(t.value AS DOUBLE) > 0GROUP BY t."to"),eth_out AS (SELECT t."from" AS addr, SUM(CAST(t.value AS DOUBLE)) / 1e18 AS total_outFROM ethereum.traces tINNER JOIN target_contracts tc ON t."from" = tc.contract_addressWHERE t.success = true AND CAST(t.value AS DOUBLE) > 0GROUP BY t."from")SELECTCOALESCE(i.addr, o.addr) AS contract_address,COALESCE(i.total_in, 0) - COALESCE(o.total_out, 0) AS estimated_balance,tc.created_at,cm.contract_project,cm.contract_nameFROM eth_in iFULL OUTER JOIN eth_out o ON i.addr = o.addrLEFT JOIN target_contracts tc ON COALESCE(i.addr, o.addr) = tc.contract_addressLEFT JOIN contracts.contract_mapping cmON cm.contract_address = COALESCE(i.addr, o.addr) AND cm.blockchain = 'ethereum'WHERE COALESCE(i.total_in, 0) - COALESCE(o.total_out, 0) > 100ORDER BY estimated_balance DESC