Query editor
withsearcher_bids as (selectdistinct from_hex(CAST(json_extract(transactions, '$[1].hash') AS VARCHAR)) as searcher_txfrom mevblocker.raw_bundleswhere json_array_length(transactions) = 2and DATE(from_unixtime(cast(timestamp as double)/cast(1000 as double))) >= (timestamp '{{StartTime}}')and from_hex(CAST(json_extract(transactions, '$[1].from') AS VARCHAR))!=from_hex(CAST(json_extract(transactions, '$[0].from') AS VARCHAR))),searcher_txs as (select "from", index, et.block_time, et.block_number, hashfrom searcher_bids sbinner join ethereum.transactions eton searcher_tx = hash),-- assuming that searcher transactions are immediately-- after user's and then kickbacks immediately after that.possible_kickbacks as (select st.block_time, st.index, et."from", to, value, et.hash, st.block_number-- ,et.block_numberfrom searcher_txs stinner join ethereum.transactions eton st.block_number = et.block_numberand st.index + 1 = et.indexjoin ethereum.blocks bl on st.block_number = bl.numberand et."from" = bl.miner),user_tx as (select st.block_time, st.index, et."from", to, et.hash, st.block_numberfrom searcher_txs stinner join ethereum.transactions eton st.block_number = et.block_numberand st.index - 1 = et.index)selectDATE_TRUNC('{{Aggregate By}}',ut.block_time) AS time,CASEWHEN kb."from" = CAST(X'1f9090aae28b8a3dceadf281b0f12828e676c326' AS VARBINARY) THEN 'rsync-builder'WHEN kb."from" = CAST(X'333333f332a06ECB5D20D35da44ba07986D6E203' AS VARBINARY) THEN 'rsync-builder'WHEN kb."from" = CAST(X'77777A6C097a1cE65C61A96a49bd1100F660eC94' AS VARBINARY) THEN 'rsync-builder'WHEN kb."from" = CAST(X'95222290dd7278aa3ddd389cc1e1d165cc4bafe5' AS VARBINARY) THEN 'beaver-build'WHEN kb."from" = CAST(X'690b9a9e9aa1c9db991c7721a92d351db4fac990' AS VARBINARY) THEN 'builder0x69'WHEN kb."from" = CAST(X'4838b106fce9647bdf1e7877bf73ce8b0bad5f97' AS VARBINARY) THEN 'titanbuilder'WHEN kb."from" = CAST(X'BaF6dC2E647aeb6F510f9e318856A1BCd66C5e19' AS VARBINARY) THEN 'Blocknative'WHEN kb."from" = CAST(X'dafea492d9c6733ae3d56b7ed1adb60692c98bc5' AS VARBINARY) THEN 'Flashbots: Builder'WHEN kb."from" = CAST(X'5124fcC2B3F99F571AD67D075643C743F38f1C34' AS VARBINARY) THEN 'f1b.io'WHEN kb."from" = CAST(X'aab27b150451726ec7738aa1d0a94505c8729bd1' AS VARBINARY) THEN 'Eden Network'WHEN kb."from" = CAST(X'0aa8ebb6ad5a8e499e550ae2c461197624c6e667' AS VARBINARY) THEN 'Gambit Labs'WHEN kb."from" = CAST(X'88c6c46ebf353a52bdbab708c23d0c81daa8134a' AS VARBINARY) THEN 'Jet Builder'WHEN kb."from" = CAST(X'8186b214a917fb4922eb984fb80cfafa30ee8810' AS VARBINARY) THEN 'Loki Builder'WHEN kb."from" = CAST(X'f15689636571dba322b48e9ec9ba6cfb3df818e1' AS VARBINARY) THEN 'penguinbuild.eth'WHEN kb."from" = CAST(X'DccA982701a264e8d629A6E8CFBa9C1a27912623' AS VARBINARY) THEN 'tbuilder'ELSE CAST(kb."from" AS VARCHAR)