Query editor
with transaction_list as (select distinct hash, value, block_time::date dt,casewhen (txs.from in('0x7bc8b1b5aba4df3be9f9a32dae501214dc0e4f3f') or txs.to in ( '0x7bc8b1b5aba4df3be9f9a32dae501214dc0e4f3f')) then 'TofuNFT'when (txs.from in (lower('0xca645290E07595a9338d9CbFfDAbd37F1B15468D')) or txs.to in (lower('0xca645290E07595a9338d9CbFfDAbd37F1B15468D'))) then 'OKX'when (txs.from in ('0x2e3b85f85628301a0bce300dee3a6b04195a15ee') or txs.to in ('0x2e3b85f85628301a0bce300dee3a6b04195a15ee') ) then 'Treasure DAO'-- when (txs.from in (-- lower('0x6a0959cd80fc9e2c1be958df1f8207c5b03f2a84'), lower('0xfb1f65955E168e0eF500b170EEd4A4EFEB99ae32'),-- lower('0x558BD058a9AC4FFE5B51d4dd61019d18Dab8844d'), lower('0x8580264236569DbE1523817CA22Bd14c6752D195'),-- lower('0x9925130735a30Cae869230A9f19eEdbAA71a2C02'), lower('0x2DB80F43Ec4CAC4758876CC2C671c1e5Fb89c3B2')-- ) or txs.to in (-- lower('0x6a0959cd80fc9e2c1be958df1f8207c5b03f2a84'), lower('0xfb1f65955E168e0eF500b170EEd4A4EFEB99ae32'),-- lower('0x558BD058a9AC4FFE5B51d4dd61019d18Dab8844d'), lower('0x8580264236569DbE1523817CA22Bd14c6752D195'),-- lower('0x9925130735a30Cae869230A9f19eEdbAA71a2C02'), lower('0x2DB80F43Ec4CAC4758876CC2C671c1e5Fb89c3B2')-- ) ) then 'Gumball Protocol'when (txs.from in ('0x00000000006c3852cbef3e08e8df289169ede581') or txs.to in ('0x00000000006c3852cbef3e08e8df289169ede581') ) then 'OpenSea'when (txs.from in (lower('0xEae46d2578B270d0fa89c4761BC0b3855959C14D'), lower('0xd34bEcE48f35E3695804a354B8F2868AA7b9AD83'))or txs.to in (lower('0xEae46d2578B270d0fa89c4761BC0b3855959C14D'), lower('0xd34bEcE48f35E3695804a354B8F2868AA7b9AD83')) )then 'Bitkeep'else null endas marketplacefrom arbitrum.transactions txswhere date(block_time) = '2023-03-01'and success = True),transfer_unique_ERC721 as(select distinct evt_tx_hash, contract_address from erc721_arbitrum.evt_Transfer where evt_block_time >= '2022-02-01'),transfer_magic_ERC20 as(select distinct evt_tx_hash, contract_address, value from erc20_arbitrum.evt_Transfer where contract_address = '0x539bde0d7dbd336b79148aa742883198bbf60342'),transfer_transactions as(selecttrs.*, txs.*,transfer_magic_ERC20.value as magicfrom transfer_unique_ERC721 trsinner join transaction_list txs on trs.evt_tx_hash = txs.hashleft outer join transfer_magic_ERC20 on transfer_magic_ERC20.evt_tx_hash = txs.hash),transaction_master as (selectdistinct dt,case when value is not null then value else 0 END as value,case when magic is not null then magic else 0 END as magic,marketplace,contract_address,evt_tx_hashfrom transfer_transactions)selectmarketplace,sum(value)*0.000000000000000001 * 1650 + sum(magic) * 0.000000000000000001 * 1.58 trading_vol_USD,count(distinct evt_tx_hash) as trading_countfrom transaction_mastergroup by 1having marketplace is not nullorder by trading_vol_USD desc