Query editor
WITH all_tx AS (-- Market Order VolumeSELECTVARBINARY_LTRIM(BYTEARRAY_SUBSTRING(data, 1+32*1, 32)) AS trader,(VARBINARY_TO_UINT256(VARBINARY_LTRIM(BYTEARRAY_SUBSTRING(data, 1+32*14, 32))) / 1e6) * (VARBINARY_TO_UINT256(VARBINARY_LTRIM(BYTEARRAY_SUBSTRING(data, 1+32*8, 32))) / 1e10) AS volumeFROM base.logsWHERE contract_address = 0x0C16ff40065Cc3Ab4bc55B60E447504AFB9C7970AND topic0 IN (0x5c00d8b4c6c92b4922d1bd61ef722ec9a29169acb95d956676b07be6a6643eea,0xdbe34a0ae1c9c65460a7efc44af9aadd9b07a8b3e112ee70a71eeeba287b2d62)UNION ALL-- Limit Order VolumeSELECTVARBINARY_LTRIM(BYTEARRAY_SUBSTRING(data, 1+32*2, 32)) AS trader,(VARBINARY_TO_UINT256(VARBINARY_LTRIM(BYTEARRAY_SUBSTRING(data, 1+32*15, 32))) / 1e6) * (VARBINARY_TO_UINT256(VARBINARY_LTRIM(BYTEARRAY_SUBSTRING(data, 1+32*9, 32))) / 1e10) AS volumeFROM base.logsWHERE contract_address = 0x0C16ff40065Cc3Ab4bc55B60E447504AFB9C7970AND topic0 IN (0xbf3d234454deff88435a11abb8501124ff9e6923fd2fdfc730d83474b6ffbe2c,0xa8f1c330dfd645b9efba2033f04003aa1793208fa11fa288d7cedacb5102d312)),normalized AS (WITH oneclick_map AS (SELECT DISTINCT useraddress, oneclickwallets0value AS wallet FROM dune.restaji.dataset_avantisuseronectschemas WHERE oneclickwallets0value IS NOT NULLUNION ALLSELECT DISTINCT useraddress, oneclickwallets1value AS wallet FROM dune.restaji.dataset_avantisuseronectschemas WHERE oneclickwallets1value IS NOT NULL)SELECTCOALESCE(b.useraddress, a.trader) AS trader,a.volumeFROM all_tx aLEFT JOIN oneclick_map b ON a.trader = b.wallet)SELECTtrader,SUM(volume) AS total_volume_usd,COUNT(*) AS total_tradesFROM normalizedGROUP BY 1ORDER BY 2 DESCLIMIT 100