with stable_token_dim as ( select '0xdac17f958d2ee523a2206206994597c13d831ec7' as contract_address,'usdt' as symbol ,'Ethereum' as chain union all select '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' as contract_address,'usdc' as symbol ,'Ethereum' as chain union all select '0x6b175474e89094c44da98b954eedeac495271d0f' as contract_address,'dai' as symbol ,'Ethereum' as chain union all select '0x0000000000085d4780b73119b644ae5ecd22b376' as contract_address,'tusd' as symbol ,'Ethereum' as chain union all select '0x7b4b0b9b024109d182dcf3831222fbda81369423' as contract_address,'busd' as symbol ,'Ethereum' as chain union all select '0x0c10bf8fcb7bf5412187a595ab97a3609160b5c6' as contract_address,'usdd' as symbol ,'Ethereum' as chain union all select '0x853d955acef822db058eb8505911ed77f175b99e' as contract_address,'frax' as symbol ,'Ethereum' as chain union all select '0x8e870d67f660d95d5be530380d0ec0bd388289e1' as contract_address,'usdp' as symbol ,'Ethereum' as chain union all select '0xc5f0f7b66764f6ec8c8dff7ba683102295e16409' as contract_address,'fdusd' as symbol ,'Ethereum' as chain union all select '0x056fd409e1d7a124bd7017459dfea2f387b6d5cd' as contract_address,'gusd' as symbol ,'Ethereum' as chain union all select '0x5f98805a4e8be255a32880fdec7f6728c6568ba0' as contract_address,'lusd' as symbol ,'Ethereum' as chain union all select '0x833589fCD6eDb6E08f4c7C32D4f71b54bdA02913' as contract_address,'usdc' as symbol ,'Base' as chain union all select '0x833589fcd6edb6e08f4c7c32d4f71b54bda02913' as contract_address,'usdc' as symbol ,'Base' as chain union all select '0xda3de145054ed30ee937865d31b500505c4bdfe7' as contract_address,'USD+' as symbol ,'Base' as chain union all select '0xd9aaec86b65d86f6a7b5b1b0c42ffa531710b6ca' as contract_address,'USDbC' as symbol ,'Base' as chain union all select '0xb79dd08ea68a908a97220c76d19a6aa9cbde4376' as contract_address,'USD+v2' as symbol ,'Base' as chain union all select '0x50c5725949a6f0c72e6c4a641f24049a917db0cb' as contract_address,'DAI' as symbol ,'Base' as chain union all select '0x4a3a6dd60a34bb2aba60d73b4c88315e9ceb6a3d' as contract_address,'MIN' as symbol ,'Base' as chain union all select '0x65a2508c429a6078a7bc2f7df81ab575bd9d9275' as contract_address,'DAI+' as symbol ,'Base' as chain ), deployedBots AS ( SELECT TO_ADDRESS as address, RANK() OVER (ORDER BY BLOCK_NUMBER ASC ) AS botVersion, BLOCK_NUMBER as deploymentBlockNumber, BLOCK_TIMESTAMP as deploymentTimestamp, FROM_ADDRESS as deployer, TX_HASH as deploymentTxHash ,'Ethereum' as chain FROM ethereum.core.fact_traces WHERE TYPE in ('create','CREATE') AND TX_STATUS = 'SUCCESS' -- /* BotDeployerAddress */ AND FROM_ADDRESS = '0x40800a492a04ae99c7fa6a0c2b52e3beb6675a0d' -- /* BotDeployerFirstTransactionBlockNumber */ AND BLOCK_NUMBER >= 17300043 union all SELECT TO_ADDRESS as address, RANK() OVER (ORDER BY BLOCK_NUMBER ASC ) AS botVersion, BLOCK_NUMBER as deploymentBlockNumber, BLOCK_TIMESTAMP as deploymentTimestamp, FROM_ADDRESS as deployer, TX_HASH as deploymentTxHash ,'Base' as chain FROM base.core.fact_traces WHERE TYPE in ('create','CREATE') AND TX_STATUS = 'SUCCESS' -- /* BotDeployerAddress */ and FROM_ADDRESS = '0x260f9a750a063728e539bec8028d11da48e1cbef' AND BLOCK_NUMBER >= 2993119 ), dex_swap_history as ( SELECT * ,case when chain='Ethereum' and TOKEN_IN in ('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2') or TOKEN_OUT in ('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2') then 'ETH_Pair' when chain='Base' and TOKEN_IN in ('0x4200000000000000000000000000000000000006') or TOKEN_OUT in ('0x4200000000000000000000000000000000000006') then 'ETH_Pair' when chain='Ethereum' and TOKEN_IN in (select contract_address from stable_token_dim where chain='Ethereum' group by 1) or TOKEN_OUT in (select contract_address from stable_token_dim where chain='Ethereum' group by 1) then 'Stable_Pair' when chain='Base' and TOKEN_IN in (select contract_address from stable_token_dim where chain='Base' group by 1) or TOKEN_OUT in (select contract_address from stable_token_dim where chain='Base' group by 1) then 'Stable_Pair' else 'Other' end as pair_type from ( SELECT PLATFORM ,SYMBOL_IN ,SYMBOL_OUT ,TOKEN_IN ,TOKEN_OUT ,AMOUNT_OUT_USD ,AMOUNT_IN_USD ,AMOUNT_IN ,AMOUNT_OUT ,CONTRACT_ADDRESS ,BLOCK_TIMESTAMP ,ORIGIN_TO_ADDRESS ,ORIGIN_FROM_ADDRESS ,TX_HASH ,'Ethereum' as chain from ethereum.defi.ez_dex_swaps where ORIGIN_TO_ADDRESS in (select address from deployedBots where chain='Ethereum' group by 1) union all SELECT PLATFORM ,SYMBOL_IN ,SYMBOL_OUT ,TOKEN_IN ,TOKEN_OUT ,AMOUNT_OUT_USD ,AMOUNT_IN_USD ,AMOUNT_IN ,AMOUNT_OUT ,CONTRACT_ADDRESS ,BLOCK_TIMESTAMP ,ORIGIN_TO_ADDRESS ,ORIGIN_FROM_ADDRESS ,TX_HASH ,'Base' as chain from base.defi.ez_dex_swaps where ORIGIN_TO_ADDRESS in (select address from deployedBots where chain='Base' group by 1) ) a ), Friend_Tech_Shares_trade as ( SELECT BLOCK_TIMESTAMP ,FROM_ADDRESS ,TO_ADDRESS ,a.TX_HASH as TX_HASH ,EVENT_NAME ,isBuy ,ethAmount/1e18 as ethAmount ,protocolEthAmount /1e18 as protocolEthAmount ,subjectEthAmount /1e18 as subjectEthAmount ,subject ,supply ,trader ,shareAmount from ( SELECT TX_HASH ,EVENT_NAME ,DECODED_LOG['ethAmount'] as ethAmount ,cast(DECODED_LOG['isBuy'] as float) as isBuy ,DECODED_LOG['protocolEthAmount'] as protocolEthAmount ,DECODED_LOG['subject'] as subject ,DECODED_LOG['subjectEthAmount'] as subjectEthAmount ,DECODED_LOG['supply'] as supply ,DECODED_LOG['trader'] as trader ,DECODED_LOG['shareAmount'] as shareAmount ,BLOCK_TIMESTAMP from base.core.fact_decoded_event_logs where CONTRACT_ADDRESS='0xcf205808ed36593aa40a44f10c7f7c2f67d4a4d4' and BLOCK_TIMESTAMP>'2023-08-01' ) a JOIN ( SELECT TX_HASH ,FROM_ADDRESS ,TO_ADDRESS from base.core.fact_transactions where BLOCK_TIMESTAMP>'2023-08-01' and STATUS='SUCCESS' ) b on a.TX_HASH=b.TX_HASH ), eth_price_dim as ( select date_trunc('day',BLOCK_TIMESTAMP) as stat_date ,avg(LATEST_ANSWER_UNADJ) /1e8 as eth_price from ethereum.chainlink.ez_oracle_feeds where FEED_NAME ='ETH / USD' group by 1 ), unibot_user_friendtech_trade as ( SELECT a.stat_date as stat_date ,TX_HASH ,trader ,total_volume_eth ,total_volume_eth * eth_price as volume ,case when TO_ADDRESS in ('0x93b265f3a206a8114786155bb887f240d1164f9b') then 'Sniper' else 'Unibot User' end as trading_type ,'Base' as Chain ,BLOCK_TIMESTAMP from ( SELECT BLOCK_TIMESTAMP ,FROM_ADDRESS ,TO_ADDRESS ,TX_HASH ,EVENT_NAME ,isBuy ,ethAmount ,protocolEthAmount ,subjectEthAmount ,subject ,supply ,trader ,shareAmount ,ethAmount+ protocolEthAmount+subjectEthAmount as total_volume_eth ,date_trunc('day',BLOCK_TIMESTAMP) as stat_date from Friend_Tech_Shares_trade where trader in (select ORIGIN_FROM_ADDRESS from dex_swap_history group by 1) or TO_ADDRESS in ('0x93b265f3a206a8114786155bb887f240d1164f9b') ) a left JOIN ( SELECT stat_date ,eth_price from eth_price_dim )b on a.stat_date=b.stat_date ), dex_swap_history_value as ( SELECT stat_date ,SYMBOL_A ,SYMBOL_B ,TOKEN_A ,TOKEN_B ,AMOUNT_USD_A ,AMOUNT_USD_B ,AMOUNT_A ,AMOUNT_B ,PLATFORM ,CONTRACT_ADDRESS ,BLOCK_TIMESTAMP ,ORIGIN_TO_ADDRESS ,ORIGIN_FROM_ADDRESS as user_address ,TX_HASH ,chain ,eth_price ,eth_amount ,stable_amount ,case when pair_type ='ETH_Pair' then eth_amount * eth_price when pair_type ='Stable_Pair' then stable_amount * 1 else GREATEST(AMOUNT_USD_A, AMOUNT_USD_B) end as usd_value from ( SELECT a.stat_date as stat_date ,SYMBOL_A ,SYMBOL_B ,TOKEN_A ,TOKEN_B ,AMOUNT_USD_A ,AMOUNT_USD_B ,AMOUNT_A ,AMOUNT_B ,PLATFORM ,CONTRACT_ADDRESS ,BLOCK_TIMESTAMP ,ORIGIN_TO_ADDRESS ,ORIGIN_FROM_ADDRESS ,TX_HASH ,chain ,eth_price ,pair_type , case when TOKEN_A in ('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2','0x4200000000000000000000000000000000000006') then AMOUNT_A else AMOUNT_B end as eth_amount , case when TOKEN_A in (select contract_address from stable_token_dim group by 1) then AMOUNT_A else AMOUNT_B end as stable_amount from ( SELECT case when AMOUNT_OUT_USD is null and AMOUNT_IN_USD is null then 1 else 0 end as is_no_price ,date_trunc('day',BLOCK_TIMESTAMP) as stat_date ,CASE WHEN SYMBOL_IN < SYMBOL_OUT THEN SYMBOL_IN ELSE SYMBOL_OUT END AS SYMBOL_A ,CASE WHEN SYMBOL_IN < SYMBOL_OUT THEN SYMBOL_OUT ELSE SYMBOL_IN END AS SYMBOL_B ,CASE WHEN SYMBOL_IN < SYMBOL_OUT THEN TOKEN_IN ELSE TOKEN_OUT END AS TOKEN_A ,CASE WHEN SYMBOL_IN < SYMBOL_OUT THEN TOKEN_OUT ELSE TOKEN_IN END AS TOKEN_B ,CASE WHEN SYMBOL_IN < SYMBOL_OUT THEN AMOUNT_IN_USD ELSE AMOUNT_OUT_USD END AS AMOUNT_USD_A ,CASE WHEN SYMBOL_IN < SYMBOL_OUT THEN AMOUNT_OUT_USD ELSE AMOUNT_IN_USD END AS AMOUNT_USD_B ,CASE WHEN SYMBOL_IN < SYMBOL_OUT THEN AMOUNT_IN ELSE AMOUNT_OUT END AS AMOUNT_A ,CASE WHEN SYMBOL_IN < SYMBOL_OUT THEN AMOUNT_OUT ELSE AMOUNT_IN END AS AMOUNT_B ,PLATFORM ,SYMBOL_IN ,SYMBOL_OUT ,TOKEN_IN ,TOKEN_OUT ,AMOUNT_OUT_USD ,AMOUNT_IN_USD ,AMOUNT_IN ,AMOUNT_OUT ,CONTRACT_ADDRESS ,BLOCK_TIMESTAMP ,ORIGIN_TO_ADDRESS ,ORIGIN_FROM_ADDRESS ,TX_HASH ,chain ,pair_type from dex_swap_history ) a left join ( SELECT stat_date ,eth_price from eth_price_dim )b on a.stat_date=b.stat_date ) a ), unibot_swap_all as ( SELECT stat_date ,SYMBOL_A ,SYMBOL_B ,TOKEN_A ,TOKEN_B ,AMOUNT_USD_A ,AMOUNT_USD_B ,AMOUNT_A ,AMOUNT_B ,PLATFORM ,CONTRACT_ADDRESS ,BLOCK_TIMESTAMP ,ORIGIN_TO_ADDRESS ,user_address ,TX_HASH ,chain ,eth_price ,eth_amount ,stable_amount ,usd_value ,'dex' as trading_type from dex_swap_history_value union all SELECT stat_date as stat_date ,'' as SYMBOL_A ,'' as SYMBOL_B ,'' as TOKEN_A ,'' as TOKEN_B ,'' as AMOUNT_USD_A ,'' as AMOUNT_USD_B ,'' as AMOUNT_A ,'' as AMOUNT_B ,'' as PLATFORM ,'' as CONTRACT_ADDRESS ,BLOCK_TIMESTAMP ,'' as ORIGIN_TO_ADDRESS ,trader as user_address ,TX_HASH ,'Base' as chain ,0 as eth_price ,0 as eth_amount ,0 as stable_amount ,volume as usd_value ,'friendtech' as trading_type from unibot_user_friendtech_trade ), new_user_dim as ( SELECT user_address ,min(stat_date) as create_date from unibot_swap_all group by 1 ), pool_detail_dim as ( SELECT POOL_ADDRESS ,max(trader_count) as max_trader_count from ( SELECT CONTRACT_ADDRESS as POOL_ADDRESS ,date_trunc('day',BLOCK_TIMESTAMP) as stat_date ,count(distinct ORIGIN_FROM_ADDRESS) as trader_count from ethereum.defi.ez_dex_swaps group by 1,2 ) a group by 1 ) SELECT stat_date ,count(distinct user_address) as user_count ,round(sum(usd_value),2) as Volume ,round(sum(case when date_gap<=3 then usd_value else 0 end ),2) as New_User_Volume ,round(sum(case when date_gap>3 then usd_value else 0 end ),2) as Old_User_Volume ,round(sum(case when max_trader_count>=1000 then usd_value else 0 end ),2) as hot_Volume ,round(sum(case when max_trader_count<1000 then usd_value else 0 end ),2) as code_Volume ,round(sum(case when trading_type ='friendtech' then usd_value else 0 end ),2) as friendtech_Volume ,round(sum(case when trading_type ='dex' then usd_value else 0 end ),2) as dex_Volume ,round(sum(case when chain ='Ethereum' then usd_value else 0 end ),2) as ethereum_Volume ,round(sum(case when chain ='Base' then usd_value else 0 end ),2) as base_Volume ,round(count(distinct case when chain ='Ethereum' then user_address else null end ),2) as ethereum_user_count ,count(distinct case when chain ='Base' then user_address else null end ) as base_user_count from ( SELECT stat_date ,a.user_address ,create_date ,DATEDIFF(day, CAST(create_date AS TIMESTAMP), CAST(stat_date AS TIMESTAMP)) as date_gap ,trading_type ,chain ,usd_value ,BLOCK_TIMESTAMP ,max_trader_count from ( SELECT stat_date ,BLOCK_TIMESTAMP ,user_address ,TX_HASH ,chain ,usd_value ,trading_type ,CONTRACT_ADDRESS from unibot_swap_all ) a left join ( SELECT create_date ,user_address as address from new_user_dim )b on (a.user_address=b.address) left JOIN ( SELECT POOL_ADDRESS ,max_trader_count from pool_detail_dim )c on (a.CONTRACT_ADDRESS=c.POOL_ADDRESS) ) a group by 1 order by 1

Query	Creator	Created
shib check uniswap kyberswap hashflow maverick dodo	0xbi	Jun 7, 2023
Unibot_dex_candle uniswap kyberswap hashflow maverick dodo	0xbi	Oct 19, 2023
Unibot_trade uniswap kyberswap woofi hashflow maverick	0xbi	Oct 14, 2023

0xbi