Query editor
WITH-- get all eth and erc20 transfersget_transfers AS (SELECTtx_hash,"from","to",contract_address,amountFROM(-- gets all erc20 transfers (from events)SELECTevt_tx_hash AS tx_hash,"from","to",erc20transfers.contract_address,value as amountFROMerc20_ethereum.evt_Transfer erc20transfersWHERE0x0000000000000000000000000000000000000000 not in ("from", "to") -- remove mint and burnsAND evt_block_time > now() - interval '7' dayUNION ALL-- gets all transfers that transferred ether (from traces)SELECTtx_hash,"from","to",0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 as contract_address, -- replaces eth as weth!value as amountfromethereum.traces tracesWHEREvalue > uint256 '0' -- this is the ether filterAND block_time > now() - interval '7' day)),logs as (select *from uniswap_ethereum.V2DutchOrderReactor_evt_Fillwhere evt_block_time > now() - interval '7' dayunion allselect *from uniswap_ethereum.ExclusiveDutchOrderReactor_evt_Fillwhere evt_block_time > now() - interval '7' day),-- get all successful transactions hashes that have emitted a fill eventtxs_with_evt_fill AS (SELECTtx.hash as tx_hash,tx."from" as from_address,tx."to" as to_address,tx.block_time,x.swapper,coalesce(labels.name, cast(x.filler as varchar)) as filler,x.filler as filler_address,-- sometimes there is more than one fill event per transaction hash, causing transaction hashes to appear more than once-- however, our methodology calculates top level trades via transfers to and from the swapper-- so, we simply need to record all transaction hashes with at least one fill event once, and this ensures one row per transaction hash