--------------------------------------------------------------------Ethereum-------------------------------------------------------------------- with ethereum_dexs as ( select ADDRESS, LABEL as PROJECT_NAME from ethereum.core.dim_labels where LABEL_TYPE='dex' and LABEL_SUBTYPE in ('general_contract','swap_contract') and PROJECT_NAME not in ('idex','kybernetwork','opyn','dexe network','dex.ag','hegic','idle finance','luaswap','airswap','loopring','hakka finance') union select $1 AS ADDRESS, $2 as PROJECT_NAME FROM (VALUES ('0x7a250d5630b4cf539739df2c5dacb4c659f2488d','uniswap'), ('0xa2398842f37465f89540430bdc00219fa9e4d28a','dodo'), ('0x1111111254fb6c44bac0bed2854e76f90643097d','1inch'), ('0x1111111254eeb25477b68fb85ed929f73a960582','1inch'), ('0x111111125434b319222cdbf8c261674adb56f3ae','1inch'), ('0x03f7724180aa6b939894b5ca4314783b0b36b329','shibaswap'), ('0x53e0e51b5ed9202110d7ecd637a4581db8b9879f','dooar'), ('0xdef171fe48cf0115b1d80b88dc8eab59176fee57','paraswap'), ('0x76f4eed9fe41262669d0250b2a97db79712ad855','odos'), ('0xba12222222228d8ba445958a75a0704d566bf2c8','balancer'), ('0x25553828f22bdd19a20e4f12f052903cb474a335','uniswap'), ('0x03f34be1bf910116595db1b11e9d1b2ca5d59659','tokenlon'), ('0x617dee16b86534a5d792a4d7a62fb491b544111e','kyberswap'), ('0xdf1a1b60f2d438842916c0adc43748768353ec25','kyberswap'), ('0x00555513acf282b42882420e5e5ba87b44d8fa6e','kyberswap'), ('0xf6a94dfd0e6ea9ddfdffe4762ad4236576136613','hashflow'), ('0x55b916ce078ea594c10a874ba67ecc3d62e29822','curve fi'))), ethereum_txs as ( select BLOCK_TIMESTAMP, TX_HASH, ORIGIN_FUNCTION_SIGNATURE, ORIGIN_FROM_ADDRESS, ORIGIN_TO_ADDRESS, CONTRACT_ADDRESS , FROM_ADDRESS, TO_ADDRESS, RAW_AMOUNT from ethereum.core.fact_token_transfers where BLOCK_TIMESTAMP::date>=CURRENT_DATE-{{Days_Before}} union all select BLOCK_TIMESTAMP, TX_HASH, ORIGIN_FUNCTION_SIGNATURE, ORIGIN_FROM_ADDRESS, ORIGIN_TO_ADDRESS, '' as CONTRACT_ADDRESS , ETH_FROM_ADDRESS as FROM_ADDRESS, ETH_TO_ADDRESS as TO_ADDRESS, AMOUNT as RAW_AMOUNT from ethereum.core.ez_eth_transfers where BLOCK_TIMESTAMP::date>=CURRENT_DATE-{{Days_Before}}), ethereum_contracts as ( select ADDRESS, SYMBOL, DECIMALS from ethereum.core.dim_contracts where DECIMALS>0 union all select '', 'ETH', 0), ethereum_prices as ( select HOUR, TOKEN_ADDRESS, SYMBOL, PRICE from ethereum.core.fact_hourly_token_prices where TOKEN_ADDRESS is not null union all select HOUR, '' as TOKEN_ADDRESS, 'ETH' as SYMBOL, PRICE from ethereum.core.fact_hourly_token_prices where TOKEN_ADDRESS is null ), ethereum_swaps_raw_tmp as ( select a.BLOCK_TIMESTAMP, a.ORIGIN_FUNCTION_SIGNATURE, a.ORIGIN_FROM_ADDRESS as SWAPPER, a.CONTRACT_ADDRESS as TOKEN_FROM, c.SYMBOL as SYMBOL_FROM, a.RAW_AMOUNT as RAW_AMOUNT_FROM, a.RAW_AMOUNT/pow(10,c.DECIMALS) as AMOUNT_FROM, ifnull(x.PRICE,div0((b.RAW_AMOUNT/pow(10,d.DECIMALS)*y.PRICE),AMOUNT_FROM)) as PRICE_FROM, b.CONTRACT_ADDRESS as TOKEN_TO, d.SYMBOL as SYMBOL_TO, b.RAW_AMOUNT as RAW_AMOUNT_TO, b.RAW_AMOUNT/pow(10,d.DECIMALS) as AMOUNT_TO, ifnull(y.PRICE,div0((a.RAW_AMOUNT/pow(10,c.DECIMALS)*x.PRICE),AMOUNT_TO)) as PRICE_TO, a.TX_HASH, a.ORIGIN_TO_ADDRESS as DEX_ADDRESS, e.PROJECT_NAME, case when (PRICE_TO*AMOUNT_TO is null or PRICE_TO*AMOUNT_TO>100000000) and (PRICE_FROM*AMOUNT_FROM is null or PRICE_FROM*AMOUNT_FROM>100000000) then 0 when PRICE_TO*AMOUNT_TO is null or PRICE_TO*AMOUNT_TO>PRICE_FROM*AMOUNT_FROM then PRICE_FROM*AMOUNT_FROM else PRICE_TO*AMOUNT_TO end as VALUE_USD from ethereum_txs a join ethereum_txs b on a.TX_HASH=b.TX_HASH and a.ORIGIN_FROM_ADDRESS=a.FROM_ADDRESS and a.ORIGIN_FROM_ADDRESS=b.TO_ADDRESS and b.FROM_ADDRESS!='0x0000000000000000000000000000000000000000' and a.TO_ADDRESS!='0x0000000000000000000000000000000000000000' left join ethereum_contracts c on a.CONTRACT_ADDRESS=c.ADDRESS-- and c.SYMBOL!='UNI-V2' left join ethereum_contracts d on b.CONTRACT_ADDRESS=d.ADDRESS-- and c.SYMBOL!='UNI-V2' join ethereum_dexs e on a.ORIGIN_TO_ADDRESS=e.ADDRESS left join ethereum_prices x on x.TOKEN_ADDRESS= a.CONTRACT_ADDRESS and date_trunc(x.hour, a.BLOCK_TIMESTAMP)=x.hour left join ethereum_prices y on y.TOKEN_ADDRESS= b.CONTRACT_ADDRESS and date_trunc(y.hour, a.BLOCK_TIMESTAMP)=y.hour where c.SYMBOL not in ('UNI-V2','VOLT') and d.SYMBOL not in('UNI-V2','VOLT') and TOKEN_TO!=TOKEN_FROM ), ethereum_swaps_raw as ( select * from ethereum_swaps_raw_tmp where tx_hash in (select TX_HASH from (select TX_HASH, count(*) as txs from ethereum_swaps_raw_tmp group by 1) where txs=1)), ethereum_swaps as ( select BLOCK_TIMESTAMP, 'Ethereum' as chain, PROJECT_NAME, SWAPPER, TOKEN_FROM, SYMBOL_FROM, AMOUNT_FROM, TOKEN_TO, SYMBOL_TO, AMOUNT_TO, VALUE_USD, TX_HASH from ethereum_swaps_raw), --------------------------------------------------------------------Optimism-------------------------------------------------------------------- optimism_dexs as ( select ADDRESS, PROJECT_NAME from optimism.core.dim_labels where LABEL_TYPE='dex' and LABEL_SUBTYPE in ('general_contract','swap_contract') and PROJECT_NAME not in ('wardenswap','beethoven x','xy finance','bongswap','radioshack swap','pickle finance','nex','saddle finance') union select $1 AS ADDRESS, $2 as PROJECT_NAME FROM (VALUES ('0xdef1abe32c034e558cdd535791643c58a13acc10','dexguru'), ('0x00c0184c0b5d42fba6b7ca914b31239b419ab80b','slingshot'), ('0xedd118e091eee0e7fa9eeb9b4db717518f56cb15','slingshot'), ('0xabbc5f99639c9b6bcb58544ddf04efa6802f4064','gmx'), ('0x69dd38645f7457be13571a847ffd905f9acbaf6d','odos'), ('0xd7f1dd5d49206349cae8b585fcb0ce3d96f1696f','zerion'), ('0x1111111254fb6c44bac0bed2854e76f90643097d','1inch'), ('0x7ea8c22e6dcd7bd69eb180664da68e1f1f11d696','warden'), ('0xfd9d2827ad469b72b69329daa325ba7afbdb3c98','dodo'), ('0xdbd4ffc32b34f630dd8ac18d37162ec8462db7db','clipper'), ('0xf0e2dcbb4a3dd38d33903a42388bfdd489fdb113','clipper'))), optimism_txs as ( select BLOCK_TIMESTAMP, TX_HASH, ORIGIN_FUNCTION_SIGNATURE, ORIGIN_FROM_ADDRESS, ORIGIN_TO_ADDRESS, CONTRACT_ADDRESS , FROM_ADDRESS, TO_ADDRESS, RAW_AMOUNT from optimism.core.fact_token_transfers where BLOCK_TIMESTAMP::date>=CURRENT_DATE-{{Days_Before}} union all select BLOCK_TIMESTAMP, TX_HASH, ORIGIN_FUNCTION_SIGNATURE, ORIGIN_FROM_ADDRESS, ORIGIN_TO_ADDRESS, '' as CONTRACT_ADDRESS , ETH_FROM_ADDRESS as FROM_ADDRESS, ETH_TO_ADDRESS as TO_ADDRESS, AMOUNT as RAW_AMOUNT from optimism.core.ez_eth_transfers where BLOCK_TIMESTAMP::date>=CURRENT_DATE-{{Days_Before}}), optimism_contracts as ( select ADDRESS, SYMBOL, DECIMALS from optimism.core.dim_contracts where NAME not ilike '%lp token%' and SYMBOL not ilike '%/%' and SYMBOL not ilike 'LP-%' and SYMBOL not ilike '%-ZS' and DECIMALS is not null union all select '', 'ETH', 0), optimism_prices as ( select HOUR, TOKEN_ADDRESS, SYMBOL, PRICE from optimism.core.fact_hourly_token_prices union all select HOUR, '' as TOKEN_ADDRESS, 'ETH' as SYMBOL, PRICE from ethereum.core.fact_hourly_token_prices where TOKEN_ADDRESS is null ), optimism_swaps_raw_tmp as ( select a.BLOCK_TIMESTAMP, a.ORIGIN_FUNCTION_SIGNATURE, a.ORIGIN_FROM_ADDRESS as SWAPPER, a.CONTRACT_ADDRESS as TOKEN_FROM, c.SYMBOL as SYMBOL_FROM, a.RAW_AMOUNT as RAW_AMOUNT_FROM, a.RAW_AMOUNT/pow(10,c.DECIMALS) as AMOUNT_FROM, ifnull(x.PRICE,div0((b.RAW_AMOUNT/pow(10,d.DECIMALS)*y.PRICE),AMOUNT_FROM)) as PRICE_FROM, b.CONTRACT_ADDRESS as TOKEN_TO, d.SYMBOL as SYMBOL_TO, b.RAW_AMOUNT as RAW_AMOUNT_TO, b.RAW_AMOUNT/pow(10,d.DECIMALS) as AMOUNT_TO, ifnull(y.PRICE,div0((a.RAW_AMOUNT/pow(10,c.DECIMALS)*x.PRICE),AMOUNT_TO)) as PRICE_TO, a.TX_HASH, a.ORIGIN_TO_ADDRESS as DEX_ADDRESS, e.PROJECT_NAME, ifnull(PRICE_TO*AMOUNT_TO,PRICE_FROM*AMOUNT_FROM) as VALUE_USD from optimism_txs a join optimism_txs b on a.TX_HASH=b.TX_HASH and a.ORIGIN_FROM_ADDRESS=a.FROM_ADDRESS and a.ORIGIN_FROM_ADDRESS=b.TO_ADDRESS and b.FROM_ADDRESS!='0x0000000000000000000000000000000000000000' and a.TO_ADDRESS!='0x0000000000000000000000000000000000000000' join optimism_contracts c on a.CONTRACT_ADDRESS=c.ADDRESS join optimism_contracts d on b.CONTRACT_ADDRESS=d.ADDRESS join optimism_dexs e on a.ORIGIN_TO_ADDRESS=e.ADDRESS left join optimism_prices x on x.TOKEN_ADDRESS= a.CONTRACT_ADDRESS and date_trunc(hour, a.BLOCK_TIMESTAMP)=x.hour left join optimism_prices y on y.TOKEN_ADDRESS= b.CONTRACT_ADDRESS and date_trunc(hour, a.BLOCK_TIMESTAMP)=y.hour where TOKEN_TO!=TOKEN_FROM), optimism_swaps_raw as ( select * from optimism_swaps_raw_tmp where tx_hash in (select TX_HASH from (select TX_HASH, count(*) as txs from optimism_swaps_raw_tmp group by 1) where txs=1)), optimism_swaps as ( select BLOCK_TIMESTAMP, 'Optimism' as chain, PROJECT_NAME, SWAPPER, TOKEN_FROM, SYMBOL_FROM, AMOUNT_FROM, TOKEN_TO, SYMBOL_TO, AMOUNT_TO, VALUE_USD, TX_HASH from optimism_swaps_raw ), --------------------------------------------------------------------Avalanche-------------------------------------------------------------------- avalanche_dexs as ( select ADDRESS, PROJECT_NAME from avalanche.core.dim_labels where LABEL_TYPE='dex' and LABEL_SUBTYPE in ('general_contract','swap_contract') and PROJECT_NAME not in ('blackbullswap','kyber swap') union select $1 AS ADDRESS, $2 as PROJECT_NAME FROM (VALUES ('0xb099ed146fad4d0daa31e3810591fc0554af62bb','bogged finance'), ('0xfe7ce93ac0f78826cd81d506b07fe9f459c00214','odos'), ('0x1b02da8cb0d097eb8d57a175b88c7d8b47997506','sushiswap'), ('0x64d2f9f44fe26c157d552ae7eaa613ca6587b59e','hashflow'), ('0x1a1ec25dc08e98e5e93f1104b5e5cdd298707d31','metamask'), ('0x1111111254fb6c44bac0bed2854e76f90643097d','1inch'), ('0xc7f372c62238f6a5b79136a9e5d16a2fd7a3f0f5','swapsicle'), ('0xe3ffc583dc176575eea7fd9df2a7c65f7e23f4c3','trader joe'), ('0x9e4aabd2b3e60ee1322e94307d0776f2c8e6cfbb','elk'), ('0xdef1c0ded9bec7f1a1670819833240f027b25eff','zerox'), ('0xd79138c49c49200a1afc935171d1bdad084fdc95','curve fi'), ('0x617dee16b86534a5d792a4d7a62fb491b544111e','kyberswap'), ('0xdf1a1b60f2d438842916c0adc43748768353ec25','kyberswap'), ('0x00555513acf282b42882420e5e5ba87b44d8fa6e','kyberswap'), ('0x6352a56caadc4f1e25cd6c75970fa768a3304e64','openocean'), ('0xd7f1dd5d49206349cae8b585fcb0ce3d96f1696f','zerion'), ('0x409e377a7affb1fd3369cfc24880ad58895d1dd9','dodo'), ('0xe0c38b2a8d09aad53f1c67734b9a95e43d5981c0','0xe0c38b2a8d09aad53f1c67734b9a95e43d5981c0'))), avalanche_txs as ( select BLOCK_TIMESTAMP, TX_HASH, ORIGIN_FUNCTION_SIGNATURE, ORIGIN_FROM_ADDRESS, ORIGIN_TO_ADDRESS, CONTRACT_ADDRESS , FROM_ADDRESS, TO_ADDRESS, RAW_AMOUNT from avalanche.core.fact_token_transfers where BLOCK_TIMESTAMP::date>=CURRENT_DATE-{{Days_Before}} union all select BLOCK_TIMESTAMP, TX_HASH, ORIGIN_FUNCTION_SIGNATURE, ORIGIN_FROM_ADDRESS, ORIGIN_TO_ADDRESS, '' as CONTRACT_ADDRESS , ETH_FROM_ADDRESS as FROM_ADDRESS, ETH_TO_ADDRESS as TO_ADDRESS, AMOUNT as RAW_AMOUNT from avalanche.core.ez_avax_transfers where BLOCK_TIMESTAMP::date>=CURRENT_DATE-{{Days_Before}}), avalanche_contracts as ( select ADDRESS, case when address='0x5c49b268c9841aff1cc3b0a418ff5c3442ee3f3b' then 'MAI' else SYMBOL end as SYMBOL, DECIMALS from avalanche.core.dim_contracts where NAME not ilike '%lp token%' and SYMBOL not ilike '%/USD%' and SYMBOL not ilike 'LP-%' and SYMBOL not ilike '%-LP%' and ADDRESS!='0x017fe17065b6f973f1bad851ed8c9461c0169c31' and SYMBOL not in ('PGL','ELP','CHAM','WAVAX') and DECIMALS>0 union all select '', 'AVAX', 0 union all select '0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7', 'WAVAX', 18), avalanche_prices_raw as ( select date_trunc(day,HOUR) as day, '' as TOKEN_ADDRESS,'AVAX' as SYMBOL, avg(PRICE) as PRICE from ethereum.core.fact_hourly_token_prices where TOKEN_ADDRESS='0x85f138bfee4ef8e540890cfb48f620571d67eda3' group by 1,2,3 union all select date_trunc(day,HOUR) as day, '0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7' as TOKEN_ADDRESS,'WAVAX' as SYMBOL, avg(PRICE) as PRICE from ethereum.core.fact_hourly_token_prices where TOKEN_ADDRESS='0x85f138bfee4ef8e540890cfb48f620571d67eda3' group by 1,2,3 union all select date_trunc(day,HOUR) as day, '0x49d5c2bdffac6ce2bfdb6640f4f80f226bc10bab' as TOKEN_ADDRESS, 'WETH.e' as SYMBOL, avg(PRICE) as PRICE from ethereum.core.fact_hourly_token_prices where TOKEN_ADDRESS is null group by 1,2,3 ), avalanche_swaps_raw_tmp as (select a.BLOCK_TIMESTAMP, a.ORIGIN_FUNCTION_SIGNATURE, a.ORIGIN_FROM_ADDRESS as SWAPPER, a.CONTRACT_ADDRESS as TOKEN_FROM, c.SYMBOL as SYMBOL_FROM, a.RAW_AMOUNT as RAW_AMOUNT_FROM, a.RAW_AMOUNT/pow(10,c.DECIMALS) as AMOUNT_FROM, case when SYMBOL_FROM in ('USDC.e','USDC','USDT.e','USDt','DAI.e','BUSD.e') then 1 else x.PRICE end as PRICE_FROM, b.CONTRACT_ADDRESS as TOKEN_TO, d.SYMBOL as SYMBOL_TO, b.RAW_AMOUNT as RAW_AMOUNT_TO, b.RAW_AMOUNT/pow(10,d.DECIMALS) as AMOUNT_TO, case when SYMBOL_TO in ('USDC.e','USDC','USDT.e','USDt','DAI.e','BUSD.e') then 1 else y.PRICE end as PRICE_TO, a.TX_HASH, a.ORIGIN_TO_ADDRESS as DEX_ADDRESS, e.PROJECT_NAME from avalanche_txs a join avalanche_txs b on a.TX_HASH=b.TX_HASH and a.ORIGIN_FROM_ADDRESS=a.FROM_ADDRESS and a.ORIGIN_FROM_ADDRESS=b.TO_ADDRESS and b.FROM_ADDRESS!='0x0000000000000000000000000000000000000000' and a.TO_ADDRESS!='0x0000000000000000000000000000000000000000' join avalanche_contracts c on a.CONTRACT_ADDRESS=c.ADDRESS join avalanche_contracts d on b.CONTRACT_ADDRESS=d.ADDRESS join avalanche_dexs e on a.ORIGIN_TO_ADDRESS=e.ADDRESS left join avalanche_prices_raw x on x.TOKEN_ADDRESS= a.CONTRACT_ADDRESS and date_trunc(day, a.BLOCK_TIMESTAMP)=x.day left join avalanche_prices_raw y on y.TOKEN_ADDRESS= b.CONTRACT_ADDRESS and date_trunc(day, a.BLOCK_TIMESTAMP)=y.day where TOKEN_TO!=TOKEN_FROM), avalanche_swaps_raw as ( select * from avalanche_swaps_raw_tmp where tx_hash in (select TX_HASH from (select TX_HASH, count(*) as txs from avalanche_swaps_raw_tmp group by 1) where txs=1)), avalanche_prices as ( select date_trunc(day,BLOCK_TIMESTAMP) as day, TOKEN_ADDRESS,SYMBOL, avg(PRICE) as PRICE from (select BLOCK_TIMESTAMP, TOKEN_FROM as TOKEN_ADDRESS, SYMBOL_FROM as SYMBOL, div0((AMOUNT_TO*PRICE_TO),AMOUNT_FROM) as PRICE from avalanche_swaps_raw where PRICE_FROM is null and AMOUNT_FROM>0 and AMOUNT_TO>0 and PRICE_TO is not null union all select BLOCK_TIMESTAMP, TOKEN_TO as TOKEN_ADDRESS, SYMBOL_TO as SYMBOL, div0((AMOUNT_FROM*PRICE_FROM),AMOUNT_TO) as PRICE from avalanche_swaps_raw where PRICE_TO is null and AMOUNT_TO>0 and AMOUNT_FROM>0 and PRICE_FROM is not null) where PRICE<10000 group by 1,2,3), avalanche_swaps_raw_price as ( select BLOCK_TIMESTAMP, PROJECT_NAME, SWAPPER, TOKEN_FROM, SYMBOL_FROM, AMOUNT_FROM, ifnull(PRICE_FROM, x.PRICE) as PRICE_FROM, TOKEN_TO, SYMBOL_TO, AMOUNT_TO, ifnull(PRICE_TO,y.PRICE) as PRICE_TO, TX_HASH from avalanche_swaps_raw left join avalanche_prices x on x.TOKEN_ADDRESS=TOKEN_FROM and date_trunc(day, BLOCK_TIMESTAMP)=x.day left join avalanche_prices y on y.TOKEN_ADDRESS=TOKEN_TO and date_trunc(day, BLOCK_TIMESTAMP)=y.day), avalanche_swaps as ( select BLOCK_TIMESTAMP, 'Avalanche' as chain, PROJECT_NAME, SWAPPER, TOKEN_FROM, SYMBOL_FROM, AMOUNT_FROM, TOKEN_TO, SYMBOL_TO, AMOUNT_TO, case when (PRICE_TO*AMOUNT_TO is null or PRICE_TO*AMOUNT_TO>100000000) and (PRICE_FROM*AMOUNT_FROM is null or PRICE_FROM*AMOUNT_FROM>100000000) then 0 when PRICE_TO*AMOUNT_TO is null or PRICE_TO*AMOUNT_TO>PRICE_FROM*AMOUNT_FROM then PRICE_FROM*AMOUNT_FROM else PRICE_TO*AMOUNT_TO end as VALUE_USD, TX_HASH from avalanche_swaps_raw_price ), --------------------------------------------------------------------Polygon-------------------------------------------------------------------- polygon_dexs as ( select ADDRESS, PROJECT_NAME from polygon.core.dim_labels where LABEL_TYPE='dex' and LABEL_SUBTYPE in ('general_contract','swap_contract') and PROJECT_NAME not in ('rubic exchange','firebird finance') union all select $1 AS ADDRESS, $2 as PROJECT_NAME FROM (VALUES ('0xa32ee1c40594249eb3183c10792bcf573d4da47c','odos'), ('0xba12222222228d8ba445958a75a0704d566bf2c8','balancer'), ('0xdf1a1b60f2d438842916c0adc43748768353ec25','kyberswap'), ('0x00555513acf282b42882420e5e5ba87b44d8fa6e','kyberswap'), ('0x617dee16b86534a5d792a4d7a62fb491b544111e','kyberswap'), ('0x10f4a785f458bc144e3706575924889954946639','meshswap'), ('0x68b3465833fb72a70ecdf485e0e4c7bd8665fc45','uniswap'), ('0x51aba405de2b25e5506dea32a6697f450ceb1a17','meerkat'), ('0x7e5e5957de93d00c352df75159fbc37d5935f8bf','meerkat'), ('0x72550597dc0b2e0bec24e116add353599eff2e35','hashflow'), ('0x7e277614644409a69ea70df5cfcb114e4a51676d','hashflow'), ('0x1d8b86e3d88cdb2d34688e87e72f388cb541b7c8','curve fi'), ('0x70270c228c5b4279d1578799926873aa72446ccd','krystal'), ('0x6bfce69d1df30fd2b2c8e478edec9daa643ae3b8','clipper'), ('0xd01e3549160c62acabc4d0eb89f67aafa3de8eed','clipper'), ('0xbe75dd16d029c6b32b7ad57a0fd9c1c20dd2862e','dystopia'), ('0xa102072a4c07f06ec3b4900fdc4c7b80b6c57429','dfyn'), ('0xa222e6a71d1a1dd5f279805fbe38d5329c1d0e70','dodo'), ('0xb099ed146fad4d0daa31e3810591fc0554af62bb','bogged finance'), ('0xc5017be80b4446988e8686168396289a9a62668e','trident'), ('0xb31d1b1ea48ce4bf10ed697d44b747287e785ad4','firebird'), ('0xf6463845b0b9d9d33d8e2bcb6c628bc5cb1ad133','bitkeep'), ('0x1111111254fb6c44bac0bed2854e76f90643097d','1inch'), ('0xd7f1dd5d49206349cae8b585fcb0ce3d96f1696f','zerion'), ('0xdef1c0ded9bec7f1a1670819833240f027b25eff','0x'), ('0x6352a56caadc4f1e25cd6c75970fa768a3304e64','openocean'), ('0xdef171fe48cf0115b1d80b88dc8eab59176fee57','paraswap'), ('0x07e56b727e0eacfa53823977599905024c2de4f0','slingshot'), ('0x59c004ccca9a790f50785d859da991b4d9713025','slingshot'), ('0x3b6067d4caa8a14c63fdbe6318f27a0bbc9f9237','dodo'))), polygon_txs as ( select BLOCK_TIMESTAMP, TX_HASH, ORIGIN_FUNCTION_SIGNATURE, ORIGIN_FROM_ADDRESS, ORIGIN_TO_ADDRESS, CONTRACT_ADDRESS , FROM_ADDRESS, TO_ADDRESS, RAW_AMOUNT from polygon.core.fact_token_transfers where BLOCK_TIMESTAMP::date>=CURRENT_DATE-{{Days_Before}} union all select BLOCK_TIMESTAMP, TX_HASH, ORIGIN_FUNCTION_SIGNATURE, ORIGIN_FROM_ADDRESS, ORIGIN_TO_ADDRESS, '' as CONTRACT_ADDRESS , MATIC_FROM_ADDRESS as FROM_ADDRESS, MATIC_TO_ADDRESS as TO_ADDRESS, AMOUNT as RAW_AMOUNT from polygon.core.ez_matic_transfers where BLOCK_TIMESTAMP::date>=CURRENT_DATE-{{Days_Before}}), polygon_contracts as ( select ADDRESS, SYMBOL, DECIMALS from polygon.core.dim_contracts where NAME not ilike '%lp token%' and SYMBOL not ilike '%/USD%' and SYMBOL not ilike '%LP-%' and SYMBOL not ilike 'vAMM-%' and SYMBOL not ilike 'sAMM-%' and SYMBOL not ilike '%-LP%' and SYMBOL!='UNI-V2' and SYMBOL not in('WETH','WMATIC') union all select '', 'MATIC', 0 union all select '0x7ceb23fd6bc0add59e62ac25578270cff1b9f619', 'WETH', 18 union all select '0x0d500b1d8e8ef31e21c99d1db9a6444d3adf1270', 'WMATIC', 18), polygon_prices_raw as ( select date_trunc(day,HOUR) as day, '' as TOKEN_ADDRESS,'MATIC' as SYMBOL, avg(PRICE) as PRICE from ethereum.core.fact_hourly_token_prices where TOKEN_ADDRESS='0x7d1afa7b718fb893db30a3abc0cfc608aacfebb0' group by 1,2,3 union all select date_trunc(day,HOUR) as day, '0x0d500b1d8e8ef31e21c99d1db9a6444d3adf1270' as TOKEN_ADDRESS,'WMATIC' as SYMBOL, avg(PRICE) as PRICE from ethereum.core.fact_hourly_token_prices where TOKEN_ADDRESS='0x7d1afa7b718fb893db30a3abc0cfc608aacfebb0' group by 1,2,3 union all select date_trunc(day,HOUR) as day, '0x3f364853f01d32d581fc9734110b21c77aeea024' as TOKEN_ADDRESS,'oMATIC' as SYMBOL, avg(PRICE) as PRICE from ethereum.core.fact_hourly_token_prices where TOKEN_ADDRESS='0x7d1afa7b718fb893db30a3abc0cfc608aacfebb0' group by 1,2,3 union all select date_trunc(day,HOUR) as day, '0x7ceb23fd6bc0add59e62ac25578270cff1b9f619' as TOKEN_ADDRESS, 'WETH' as SYMBOL, avg(PRICE) as PRICE from ethereum.core.fact_hourly_token_prices where TOKEN_ADDRESS is null group by 1,2,3 union all select date_trunc(day,HOUR) as day, '0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7' as TOKEN_ADDRESS,'WBTC' as SYMBOL, avg(PRICE) as PRICE from ethereum.core.fact_hourly_token_prices where TOKEN_ADDRESS='0x2260fac5e5542a773aa44fbcfedf7c193bc2c599' group by 1,2,3 union all select date_trunc(day,HOUR) as day, '0x53e0bca35ec356bd5dddfebbd1fc0fd03fabad39' as TOKEN_ADDRESS,'LINK' as SYMBOL, avg(PRICE) as PRICE from ethereum.core.fact_hourly_token_prices where TOKEN_ADDRESS='0x514910771af9ca656af840dff83e8264ecf986ca' group by 1,2,3 union all select date_trunc(day,HOUR) as day, '0xd6df932a45c0f255f85145f286ea0b292b21c90b' as TOKEN_ADDRESS,'AAVE' as SYMBOL, avg(PRICE) as PRICE from ethereum.core.fact_hourly_token_prices where TOKEN_ADDRESS='0x7fc66500c84a76ad7e9c93437bfc5ac33e2ddae9' group by 1,2,3 ), polygon_swaps_raw_tmp as (select a.BLOCK_TIMESTAMP, a.ORIGIN_FUNCTION_SIGNATURE, a.ORIGIN_FROM_ADDRESS as SWAPPER, a.CONTRACT_ADDRESS as TOKEN_FROM, c.SYMBOL as SYMBOL_FROM, a.RAW_AMOUNT as RAW_AMOUNT_FROM, a.RAW_AMOUNT/pow(10,c.DECIMALS) as AMOUNT_FROM, case when TOKEN_FROM in ('0x2791bca1f2de4661ed88a30c99a7a9449aa84174','0xc2132d05d31c914a87c6611c10748aeb04b58e8f','0x8f3cf7ad23cd3cadbd9735aff958023239c6a063','0x957da9ebbcdc97dc4a8c274dd762ec2ab665e15f') then 1 else x.PRICE end as PRICE_FROM, b.CONTRACT_ADDRESS as TOKEN_TO, d.SYMBOL as SYMBOL_TO, b.RAW_AMOUNT as RAW_AMOUNT_TO, b.RAW_AMOUNT/pow(10,d.DECIMALS) as AMOUNT_TO, case when TOKEN_TO in ('0x2791bca1f2de4661ed88a30c99a7a9449aa84174','0xc2132d05d31c914a87c6611c10748aeb04b58e8f','0x8f3cf7ad23cd3cadbd9735aff958023239c6a063','0x957da9ebbcdc97dc4a8c274dd762ec2ab665e15f') then 1 else y.PRICE end as PRICE_TO, a.TX_HASH, a.ORIGIN_TO_ADDRESS as DEX_ADDRESS, e.PROJECT_NAME from polygon_txs a join polygon_txs b on a.TX_HASH=b.TX_HASH and a.ORIGIN_FROM_ADDRESS=a.FROM_ADDRESS and a.ORIGIN_FROM_ADDRESS=b.TO_ADDRESS and b.FROM_ADDRESS!='0x0000000000000000000000000000000000000000' and a.TO_ADDRESS!='0x0000000000000000000000000000000000000000' join polygon_contracts c on a.CONTRACT_ADDRESS=c.ADDRESS join polygon_contracts d on b.CONTRACT_ADDRESS=d.ADDRESS join polygon_dexs e on a.ORIGIN_TO_ADDRESS=e.ADDRESS left join polygon_prices_raw x on x.TOKEN_ADDRESS= a.CONTRACT_ADDRESS and date_trunc(day, a.BLOCK_TIMESTAMP)=x.day left join polygon_prices_raw y on y.TOKEN_ADDRESS= b.CONTRACT_ADDRESS and date_trunc(day, a.BLOCK_TIMESTAMP)=y.day where TOKEN_TO!=TOKEN_FROM), polygon_swaps_raw as ( select * from polygon_swaps_raw_tmp where tx_hash in (select TX_HASH from (select TX_HASH, count(*) as txs from polygon_swaps_raw_tmp group by 1) where txs=1)), polygon_prices as ( select * from ( select date_trunc(day,BLOCK_TIMESTAMP) as day, TOKEN_ADDRESS,SYMBOL, avg(PRICE) as PRICE, count(*) as swaps from (select BLOCK_TIMESTAMP, TOKEN_FROM as TOKEN_ADDRESS, SYMBOL_FROM as SYMBOL, div0((AMOUNT_TO*PRICE_TO),AMOUNT_FROM) as PRICE from polygon_swaps_raw where PRICE_FROM is null and AMOUNT_FROM>0 and AMOUNT_TO*PRICE_TO>10 union all select BLOCK_TIMESTAMP, TOKEN_TO as TOKEN_ADDRESS, SYMBOL_TO as SYMBOL, div0((AMOUNT_FROM*PRICE_FROM),AMOUNT_TO) as PRICE from polygon_swaps_raw where PRICE_TO is null and AMOUNT_TO>0 and AMOUNT_FROM*PRICE_FROM>10) where PRICE<10000 group by 1,2,3)where swaps>10), polygon_swaps_raw_price as ( select BLOCK_TIMESTAMP, PROJECT_NAME, SWAPPER, TOKEN_FROM, SYMBOL_FROM, AMOUNT_FROM, ifnull(PRICE_FROM, x.PRICE) as PRICE_FROM, TOKEN_TO, SYMBOL_TO, AMOUNT_TO, ifnull(PRICE_TO,y.PRICE) as PRICE_TO, TX_HASH from polygon_swaps_raw left join polygon_prices x on x.TOKEN_ADDRESS=TOKEN_FROM and date_trunc(day, BLOCK_TIMESTAMP)=x.day left join polygon_prices y on y.TOKEN_ADDRESS=TOKEN_TO and date_trunc(day, BLOCK_TIMESTAMP)=y.day), polygon_swaps as ( select BLOCK_TIMESTAMP, 'Polygon' as chain, PROJECT_NAME, SWAPPER, TOKEN_FROM, SYMBOL_FROM, AMOUNT_FROM, TOKEN_TO, SYMBOL_TO, AMOUNT_TO, case when (PRICE_TO*AMOUNT_TO is null or PRICE_TO*AMOUNT_TO>5000000) and (PRICE_FROM*AMOUNT_FROM is null or PRICE_FROM*AMOUNT_FROM>5000000) then 0 when PRICE_TO*AMOUNT_TO is null or PRICE_TO*AMOUNT_TO>PRICE_FROM*AMOUNT_FROM then PRICE_FROM*AMOUNT_FROM else PRICE_TO*AMOUNT_TO end as VALUE_USD, TX_HASH from polygon_swaps_raw_price ), --------------------------------------------------------------------Arbitrum-------------------------------------------------------------------- arbitrum_dexs as ( select ADDRESS, PROJECT_NAME from arbitrum.core.dim_labels where LABEL_TYPE='dex' and LABEL_SUBTYPE in ('general_contract','swap_contract') and PROJECT_NAME not in ('swapr','kromatika finance','dodo dex','curve finance') union all select $1 AS ADDRESS, $2 as PROJECT_NAME FROM (VALUES ('0xba12222222228d8ba445958a75a0704d566bf2c8','balancer'), ('0xabbc5f99639c9b6bcb58544ddf04efa6802f4064','gmx'), ('0xdd94018f54e565dbfc939f7c44a16e163faab331','odos'), ('0x1111111254fb6c44bac0bed2854e76f90643097d','1inch'), ('0x960ea3e3c7fb317332d990873d354e18d7645590','curve fi'), ('0x5543550d65813c1fa76242227cbba0a28a297771','slingshot'), ('0xd7f1dd5d49206349cae8b585fcb0ce3d96f1696f','zerion'), ('0xdef1c0ded9bec7f1a1670819833240f027b25eff','0x'), ('0x3b6067d4caa8a14c63fdbe6318f27a0bbc9f9237','dodo'), ('0x1111111254eeb25477b68fb85ed929f73a960582','0x1111111254eeb25477b68fb85ed929f73a960582'), ('0x1f772fa3bc263160ea09bb16ce1a6b8fc0fab36a','hashflow'), ('0x6352a56caadc4f1e25cd6c75970fa768a3304e64','openocean'), ('0xdef171fe48cf0115b1d80b88dc8eab59176fee57','paraswap'))), arbitrum_txs as ( select BLOCK_TIMESTAMP, TX_HASH, ORIGIN_FUNCTION_SIGNATURE, ORIGIN_FROM_ADDRESS, ORIGIN_TO_ADDRESS, CONTRACT_ADDRESS , FROM_ADDRESS, TO_ADDRESS, RAW_AMOUNT from arbitrum.core.fact_token_transfers where BLOCK_TIMESTAMP::date>=CURRENT_DATE-{{Days_Before}} union all select BLOCK_TIMESTAMP, TX_HASH, ORIGIN_FUNCTION_SIGNATURE, ORIGIN_FROM_ADDRESS, ORIGIN_TO_ADDRESS, '' as CONTRACT_ADDRESS , ETH_FROM_ADDRESS as FROM_ADDRESS, ETH_TO_ADDRESS as TO_ADDRESS, AMOUNT as RAW_AMOUNT from arbitrum.core.ez_eth_transfers where BLOCK_TIMESTAMP::date>=CURRENT_DATE-{{Days_Before}}), arbitrum_contracts as ( select ADDRESS, SYMBOL, DECIMALS from arbitrum.core.dim_contracts where NAME not ilike '%lp token%' and SYMBOL not ilike '%/USD%' and SYMBOL not ilike 'LP-%' union all select '', 'ETH', 0), arbitrum_prices_raw as ( select date_trunc(day,HOUR) as day, '' as TOKEN_ADDRESS, 'ETH' as SYMBOL, avg(PRICE) as PRICE from ethereum.core.fact_hourly_token_prices where TOKEN_ADDRESS is null group by 1,2,3 union all select date_trunc(day,HOUR) as day, '0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7' as TOKEN_ADDRESS,'WMATIC' as SYMBOL, avg(PRICE) as PRICE from ethereum.core.fact_hourly_token_prices where TOKEN_ADDRESS='0x7d1afa7b718fb893db30a3abc0cfc608aacfebb0' group by 1,2,3 union all select date_trunc(day,HOUR) as day, '0x7ceb23fd6bc0add59e62ac25578270cff1b9f619' as TOKEN_ADDRESS, 'WETH' as SYMBOL, avg(PRICE) as PRICE from ethereum.core.fact_hourly_token_prices where TOKEN_ADDRESS is null group by 1,2,3 union all select date_trunc(day,HOUR) as day, '0x2f2a2543b76a4166549f7aab2e75bef0aefc5b0f' as TOKEN_ADDRESS,'WBTC' as SYMBOL, avg(PRICE) as PRICE from ethereum.core.fact_hourly_token_prices where TOKEN_ADDRESS='0x2260fac5e5542a773aa44fbcfedf7c193bc2c599' group by 1,2,3 union all select date_trunc(day,HOUR) as day, '0xf97f4df75117a78c1a5a0dbb814af92458539fb4' as TOKEN_ADDRESS,'LINK' as SYMBOL, avg(PRICE) as PRICE from ethereum.core.fact_hourly_token_prices where TOKEN_ADDRESS='0x514910771af9ca656af840dff83e8264ecf986ca' group by 1,2,3 union all select date_trunc(day,HOUR) as day, '0xd4d42f0b6def4ce0383636770ef773390d85c61a' as TOKEN_ADDRESS,'SUSHI' as SYMBOL, avg(PRICE) as PRICE from ethereum.core.fact_hourly_token_prices where TOKEN_ADDRESS='0x6b3595068778dd592e39a122f4f5a5cf09c90fe2' group by 1,2,3 union all select date_trunc(day,HOUR) as day, '0xfa7f8980b0f1e64a2062791cc3b0871572f1f7f0' as TOKEN_ADDRESS,'UNI' as SYMBOL, avg(PRICE) as PRICE from ethereum.core.fact_hourly_token_prices where TOKEN_ADDRESS='0x1f9840a85d5af5bf1d1762f925bdaddc4201f984' group by 1,2,3 ), arbitrum_swaps_raw_tmp as (select a.BLOCK_TIMESTAMP, a.ORIGIN_FUNCTION_SIGNATURE, a.ORIGIN_FROM_ADDRESS as SWAPPER, a.CONTRACT_ADDRESS as TOKEN_FROM, c.SYMBOL as SYMBOL_FROM, a.RAW_AMOUNT as RAW_AMOUNT_FROM, a.RAW_AMOUNT/pow(10,c.DECIMALS) as AMOUNT_FROM, case when TOKEN_FROM in ('0xff970a61a04b1ca14834a43f5de4533ebddb5cc8','0xfd086bc7cd5c481dcc9c85ebe478a1c0b69fcbb9','0xda10009cbd5d07dd0cecc66161fc93d7c9000da1','0x625e7708f30ca75bfd92586e17077590c60eb4cd','0x6ab707aca953edaefbc4fd23ba73294241490620') then 1 else x.PRICE end as PRICE_FROM, b.CONTRACT_ADDRESS as TOKEN_TO, d.SYMBOL as SYMBOL_TO, b.RAW_AMOUNT as RAW_AMOUNT_TO, b.RAW_AMOUNT/pow(10,d.DECIMALS) as AMOUNT_TO, case when TOKEN_TO in ('0xff970a61a04b1ca14834a43f5de4533ebddb5cc8','0xfd086bc7cd5c481dcc9c85ebe478a1c0b69fcbb9','0xda10009cbd5d07dd0cecc66161fc93d7c9000da1','0x625e7708f30ca75bfd92586e17077590c60eb4cd','0x6ab707aca953edaefbc4fd23ba73294241490620') then 1 else y.PRICE end as PRICE_TO, a.TX_HASH, a.ORIGIN_TO_ADDRESS as DEX_ADDRESS, e.PROJECT_NAME from arbitrum_txs a join arbitrum_txs b on a.TX_HASH=b.TX_HASH and a.ORIGIN_FROM_ADDRESS=a.FROM_ADDRESS and a.ORIGIN_FROM_ADDRESS=b.TO_ADDRESS and b.FROM_ADDRESS!='0x0000000000000000000000000000000000000000' and a.TO_ADDRESS!='0x0000000000000000000000000000000000000000' join arbitrum_contracts c on a.CONTRACT_ADDRESS=c.ADDRESS join arbitrum_contracts d on b.CONTRACT_ADDRESS=d.ADDRESS join arbitrum_dexs e on a.ORIGIN_TO_ADDRESS=e.ADDRESS left join arbitrum_prices_raw x on x.TOKEN_ADDRESS= a.CONTRACT_ADDRESS and date_trunc(day, a.BLOCK_TIMESTAMP)=x.day left join arbitrum_prices_raw y on y.TOKEN_ADDRESS= b.CONTRACT_ADDRESS and date_trunc(day, a.BLOCK_TIMESTAMP)=y.day where TOKEN_TO!=TOKEN_FROM), arbitrum_swaps_raw as ( select * from arbitrum_swaps_raw_tmp where tx_hash in (select TX_HASH from (select TX_HASH, count(*) as txs from arbitrum_swaps_raw_tmp group by 1) where txs=1)), arbitrum_prices as ( select date_trunc(day,BLOCK_TIMESTAMP) as day, TOKEN_ADDRESS,SYMBOL, avg(PRICE) as PRICE from (select BLOCK_TIMESTAMP, TOKEN_FROM as TOKEN_ADDRESS, SYMBOL_FROM as SYMBOL, div0((AMOUNT_TO*PRICE_TO),AMOUNT_FROM) as PRICE from arbitrum_swaps_raw where PRICE_FROM is null and AMOUNT_FROM>0 and AMOUNT_TO>0 and PRICE_TO is not null union all select BLOCK_TIMESTAMP, TOKEN_TO as TOKEN_ADDRESS, SYMBOL_TO as SYMBOL, div0((AMOUNT_FROM*PRICE_FROM),AMOUNT_TO) as PRICE from arbitrum_swaps_raw where PRICE_TO is null and AMOUNT_TO>0 and AMOUNT_FROM>0 and PRICE_FROM is not null) where PRICE<10000 group by 1,2,3), arbitrum_swaps_raw_price as ( select BLOCK_TIMESTAMP, PROJECT_NAME, SWAPPER, TOKEN_FROM, SYMBOL_FROM, AMOUNT_FROM, ifnull(PRICE_FROM, x.PRICE) as PRICE_FROM, TOKEN_TO, SYMBOL_TO, AMOUNT_TO, ifnull(PRICE_TO,y.PRICE) as PRICE_TO, TX_HASH from arbitrum_swaps_raw left join arbitrum_prices x on x.TOKEN_ADDRESS=TOKEN_FROM and date_trunc(day, BLOCK_TIMESTAMP)=x.day left join arbitrum_prices y on y.TOKEN_ADDRESS=TOKEN_TO and date_trunc(day, BLOCK_TIMESTAMP)=y.day), arbitrum_swaps as ( select BLOCK_TIMESTAMP, 'Arbitrum' as chain, PROJECT_NAME, SWAPPER, TOKEN_FROM, SYMBOL_FROM, AMOUNT_FROM, TOKEN_TO, SYMBOL_TO, AMOUNT_TO, case when (PRICE_TO*AMOUNT_TO is null or PRICE_TO*AMOUNT_TO>100000000) and (PRICE_FROM*AMOUNT_FROM is null or PRICE_FROM*AMOUNT_FROM>100000000) then 0 when PRICE_TO*AMOUNT_TO is null or PRICE_TO*AMOUNT_TO>PRICE_FROM*AMOUNT_FROM then PRICE_FROM*AMOUNT_FROM else PRICE_TO*AMOUNT_TO end as VALUE_USD, TX_HASH from arbitrum_swaps_raw_price), --------------------------------------------------------------------Algorand-------------------------------------------------------------------- algorand_prices as ( select date_trunc(day, BLOCK_HOUR) as BLOCK_DAY, ASSET_ID, ASSET_NAME, avg(PRICE_USD) as PRICE_USD from (select BLOCK_HOUR, ASSET_ID, ASSET_NAME, PRICE_USD from algorand.defi.ez_price_pool_balances union all select BLOCK_HOUR, ASSET_ID, ASSET_NAME, PRICE_USD from algorand.defi.ez_price_swap union all select BLOCK_HOUR, 0, 'ALGO', _ALGO_PRICE as PRICE_USD from algorand.defi.ez_price_pool_balances) group by 1,2,3 ), algorand_swaps as ( select BLOCK_TIMESTAMP, chain, PROJECT_NAME, SWAPPER, TOKEN_FROM, SYMBOL_FROM, SWAP_FROM_AMOUNT as AMOUNT_FROM, TOKEN_TO, SYMBOL_TO, SWAP_TO_AMOUNT as AMOUNT_TO, VALUE_USD, TX_HASH from (select BLOCK_TIMESTAMP, 'Algorand' as chain, SWAP_PROGRAM as PROJECT_NAME, TX_GROUP_ID as TX_HASH, SWAPPER, b.ASSET_NAME as SYMBOL_FROM, b.PRICE_USD as ASSET_FROM_PRICE, b.ASSET_ID as TOKEN_FROM, SWAP_FROM_AMOUNT, ASSET_FROM_PRICE*SWAP_FROM_AMOUNT as SWAP_FROM_AMOUNT_USD, C.ASSET_NAME as SYMBOL_TO, c.PRICE_USD as ASSET_TO_PRICE, c.ASSET_ID as TOKEN_TO, SWAP_TO_AMOUNT, ASSET_TO_PRICE*SWAP_TO_AMOUNT as SWAP_TO_AMOUNT_USD, least(ifnull(SWAP_FROM_AMOUNT_USD,99999999999999),ifnull(SWAP_TO_AMOUNT_USD,99999999999999)) as VALUE_USD from algorand.defi.fact_swap a left join algorand_prices b on b.ASSET_ID=a.SWAP_FROM_ASSET_ID and date_trunc(day, BLOCK_TIMESTAMP)=date_trunc(day, b.BLOCK_DAY) left join algorand_prices c on c.ASSET_ID=a.SWAP_TO_ASSET_ID and date_trunc(day, BLOCK_TIMESTAMP)=date_trunc(day, c.BLOCK_DAY) where VALUE_USD<200000000 and BLOCK_TIMESTAMP::date>=CURRENT_DATE-{{Days_Before}})), --------------------------------------------------------------------Solana-------------------------------------------------------------------- sol_prices as ( select RECORDED_HOUR, SYMBOL, case when SYMBOL='USDT' then 'Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB' when SYMBOL='SRM' then 'SRMuApVNdxXokk5GT7XD5cUUgXMBCoAz2LHeuAoKWRt' when SYMBOL='MSOL' then 'mSoLzYCxHdYgdzU16g5QSh3i5K3z3KZK7ytfqcJm7So' when SYMBOL='RAY' then '4k3Dyjzvzp8eMZWUXbBCjEvwSkkk59S5iCNLY3QrkX6R' when SYMBOL='SBR' then 'Saber2gLauYim4Mvftnrasomsv6NvAuncvMEZwcLpD1' when SYMBOL='SOL' then 'So11111111111111111111111111111111111111112' when SYMBOL='FIDA' then 'EchesyfXePKdLtoiZSL8pBe8Myagyy8ZRqsACNCFGnvp' when SYMBOL='MER' then 'MERt85fc5boKw3BW1eYdxonEuJNvXbiMbs6hvheau5K' when SYMBOL='MNGO' then 'MangoCzJ36AjZyKwVj3VnYU4GTonjfVEnJmvvWaxLac' when SYMBOL='JET' then 'JET6zMJWkCN9tpRT2v2jfAmm5VnQFDpUBCyaKojmGtz' when SYMBOL='STSOL' then '7dHbWXmci3dT8UFYWYZweBLXgycu7Y3iL6trKn1Y7ARj' when SYMBOL='USTC' then '9vMJfxuKxXBoEa7rM12mYLMwTacLMLDJqHozw96WQL8i' when SYMBOL='FTT' then 'AGFEad2et2ZJif9jaGpdMixQqvW5i81aBdvKe7PHNfz3' when SYMBOL='USDC' then 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' when SYMBOL='SLND' then 'SLNDpmoWTVADgEdndyvWzroNL7zSi1dF9PC3xHGtPwp' when SYMBOL='COPE' then '8HGyAAB1yoM1ttS7pXjHMa3dukTFGQggnFFH3hJZgzQh' when SYMBOL='GST' then 'AFbX8oGjGpmVFywbVouvhQSRmiW2aR1mohfahi4Y2AdB' when SYMBOL='GMT' and ID='stepn' then '7i5KKsX2weiTkry7jA4ZwSuXGhs5eJBEjY8vVxR4pfRx' when SYMBOL='STEP' and ID='step-finance' then 'StepAscQoEioFxxWGnh2sLBDFp9d8rvKz2Yp39iDpyT' when SYMBOL='ATLAS' and ID='star-atlas' then 'ATLASXmbPQxBUYbxPsV97usA3fPQYEqzQBUHgiFCUsXx' when SYMBOL='WETH' and ID='2396' then '7vfCXTUXx5WJV5JADk17DUJ4ksgau7utNKj4b963voxs' when SYMBOL='POLIS' and ID='11213' then 'poLisWXnNRwC6oBu1vHiuKQzFjGL4XDSu4g9qjz9qVk' end as address, avg(CLOSE) as price from solana.core.fact_token_prices_hourly where RECORDED_HOUR>=CURRENT_DATE-{{Days_Before}} group by 1,2,3), solana_swaps_raw as ( select BLOCK_TIMESTAMP, SWAP_PROGRAM as PROJECT_NAME, SWAPPER, SWAP_FROM_MINT as TOKEN_FROM, ifnull(b.SYMBOL,d.ADDRESS_NAME) as SYMBOL_FROM, SWAP_FROM_AMOUNT as AMOUNT_FROM, b.price as PRICE_FROM, SWAP_TO_MINT as TOKEN_TO, ifnull(c.SYMBOL,e.ADDRESS_NAME) as SYMBOL_TO, SWAP_TO_AMOUNT as AMOUNT_TO, c.price as PRICE_TO, TX_ID as TX_HASH from solana.core.fact_swaps a left join sol_prices b on a.SWAP_FROM_MINT=b.address and date_trunc(hour,a.BLOCK_TIMESTAMP)=b.RECORDED_HOUR left join sol_prices c on a.SWAP_TO_MINT=c.address and date_trunc(hour,a.BLOCK_TIMESTAMP)=c.RECORDED_HOUR left join solana.core.dim_labels d on a.SWAP_FROM_MINT=d.address left join solana.core.dim_labels e on a.SWAP_TO_MINT=e.address where SUCCEEDED=TRUE and BLOCK_TIMESTAMP::date>=CURRENT_DATE-{{Days_Before}} and TOKEN_TO!=TOKEN_FROM), solana_prices as ( select date_trunc(day,BLOCK_TIMESTAMP) as day, TOKEN_ADDRESS,SYMBOL, avg(PRICE) as PRICE from (select BLOCK_TIMESTAMP, TOKEN_FROM as TOKEN_ADDRESS, SYMBOL_FROM as SYMBOL, div0((AMOUNT_TO*PRICE_TO),AMOUNT_FROM) as PRICE from solana_swaps_raw where PRICE_FROM is null and AMOUNT_FROM>0 and AMOUNT_TO>0 and PRICE_TO is not null union all select BLOCK_TIMESTAMP, TOKEN_TO as TOKEN_ADDRESS, SYMBOL_TO as SYMBOL, div0((AMOUNT_FROM*PRICE_FROM),AMOUNT_TO) as PRICE from solana_swaps_raw where PRICE_TO is null and AMOUNT_TO>0 and AMOUNT_FROM>0 and PRICE_FROM is not null) where PRICE<10000 group by 1,2,3), solana_swaps_raw_price as ( select BLOCK_TIMESTAMP, PROJECT_NAME, SWAPPER, TOKEN_FROM, SYMBOL_FROM, AMOUNT_FROM, ifnull(PRICE_FROM, x.PRICE) as PRICE_FROM, TOKEN_TO, SYMBOL_TO, AMOUNT_TO, ifnull(PRICE_TO,y.PRICE) as PRICE_TO, TX_HASH from solana_swaps_raw left join solana_prices x on x.TOKEN_ADDRESS=TOKEN_FROM and date_trunc(day, BLOCK_TIMESTAMP)=x.day left join solana_prices y on y.TOKEN_ADDRESS=TOKEN_TO and date_trunc(day, BLOCK_TIMESTAMP)=y.day), solana_swaps as ( select BLOCK_TIMESTAMP, 'Solana' as chain, PROJECT_NAME, SWAPPER, TOKEN_FROM, SYMBOL_FROM, AMOUNT_FROM, TOKEN_TO, SYMBOL_TO, AMOUNT_TO, case when (PRICE_TO*AMOUNT_TO is null or PRICE_TO*AMOUNT_TO>100000000) and (PRICE_FROM*AMOUNT_FROM is null or PRICE_FROM*AMOUNT_FROM>100000000) then 0 when PRICE_TO*AMOUNT_TO is null or PRICE_TO*AMOUNT_TO>PRICE_FROM*AMOUNT_FROM then PRICE_FROM*AMOUNT_FROM else PRICE_TO*AMOUNT_TO end as VALUE_USD, TX_HASH from solana_swaps_raw_price ), --------------------------------------------------------------------Near-------------------------------------------------------------------- near_prices_raw as ( select date_trunc(day, TIMESTAMP) as day, SYMBOL, TOKEN_CONTRACT, avg(PRICE_USD) as PRICE from near.core.fact_prices group by 1,2,3 ), near_swaps_raw as ( select BLOCK_TIMESTAMP, case when PLATFORM ilike '%ref-finance%' then 'Ref Finance' when PLATFORM ilike '%jumbo%' then 'Jumbo' else 'Others' end as PROJECT_NAME, TRADER as SWAPPER, TOKEN_IN_CONTRACT as TOKEN_FROM, TOKEN_IN as SYMBOL_FROM, AMOUNT_IN as AMOUNT_FROM, b.PRICE as PRICE_FROM, TOKEN_OUT_CONTRACT as TOKEN_TO, TOKEN_OUT as SYMBOL_TO, AMOUNT_OUT as AMOUNT_TO, c.PRICE as PRICE_TO, TX_HASH from near.core.ez_dex_swaps a left join near_prices_raw b on a.TOKEN_IN_CONTRACT=b.TOKEN_CONTRACT and date_trunc(day,a.BLOCK_TIMESTAMP)=b.day left join near_prices_raw c on a.TOKEN_OUT_CONTRACT=c.TOKEN_CONTRACT and date_trunc(day,a.BLOCK_TIMESTAMP)=c.day where BLOCK_TIMESTAMP::date>=CURRENT_DATE-{{Days_Before}} and TOKEN_TO!=TOKEN_FROM), near_prices as ( select date_trunc(day,BLOCK_TIMESTAMP) as day, TOKEN_ADDRESS,SYMBOL, avg(PRICE) as PRICE from (select BLOCK_TIMESTAMP, TOKEN_FROM as TOKEN_ADDRESS, SYMBOL_FROM as SYMBOL, div0((AMOUNT_TO*PRICE_TO),AMOUNT_FROM) as PRICE from near_swaps_raw where PRICE_FROM is null and AMOUNT_FROM>0 and AMOUNT_TO>0 and PRICE_TO is not null union all select BLOCK_TIMESTAMP, TOKEN_TO as TOKEN_ADDRESS, SYMBOL_TO as SYMBOL, div0((AMOUNT_FROM*PRICE_FROM),AMOUNT_TO) as PRICE from near_swaps_raw where PRICE_TO is null and AMOUNT_TO>0 and AMOUNT_FROM>0 and PRICE_FROM is not null) where PRICE<10000 group by 1,2,3), near_swaps_raw_price as ( select BLOCK_TIMESTAMP, PROJECT_NAME, SWAPPER, TOKEN_FROM, SYMBOL_FROM, AMOUNT_FROM, ifnull(PRICE_FROM, x.PRICE) as PRICE_FROM, TOKEN_TO, SYMBOL_TO, AMOUNT_TO, ifnull(PRICE_TO,y.PRICE) as PRICE_TO, TX_HASH from near_swaps_raw left join near_prices x on x.TOKEN_ADDRESS=TOKEN_FROM and date_trunc(day, BLOCK_TIMESTAMP)=x.day left join near_prices y on y.TOKEN_ADDRESS=TOKEN_TO and date_trunc(day, BLOCK_TIMESTAMP)=y.day), near_swaps as ( select BLOCK_TIMESTAMP, 'Near' as chain, PROJECT_NAME, SWAPPER, TOKEN_FROM, SYMBOL_FROM, AMOUNT_FROM, TOKEN_TO, SYMBOL_TO, AMOUNT_TO, case when (PRICE_TO*AMOUNT_TO is null or PRICE_TO*AMOUNT_TO>100000000) and (PRICE_FROM*AMOUNT_FROM is null or PRICE_FROM*AMOUNT_FROM>100000000) then 0 when PRICE_TO*AMOUNT_TO is null or PRICE_TO*AMOUNT_TO>PRICE_FROM*AMOUNT_FROM then PRICE_FROM*AMOUNT_FROM else PRICE_TO*AMOUNT_TO end as VALUE_USD, TX_HASH from near_swaps_raw_price ), --------------------------------------------------------------------Flow-------------------------------------------------------------------- flow_prices_raw as ( select date_trunc(day, TIMESTAMP) as day, SYMBOL, TOKEN_CONTRACT, avg(PRICE_USD) as PRICE from flow.core.fact_prices where TIMESTAMP::date>=CURRENT_DATE-{{Days_Before}} group by 1,2,3 union all select date_trunc(day, TIMESTAMP) as day, 'USDC' as SYMBOL, 'A.b19436aae4d94622.FiatToken' as TOKEN_CONTRACT, avg(1) as PRICE from flow.core.fact_prices where TIMESTAMP::date>=CURRENT_DATE-{{Days_Before}} group by 1,2,3 ), flow_swaps_raw as ( select BLOCK_TIMESTAMP, 'BloctoSwap' as PROJECT_NAME, TRADER as SWAPPER, TOKEN_IN_CONTRACT as TOKEN_FROM, b.SYMBOL as SYMBOL_FROM, TOKEN_IN_AMOUNT as AMOUNT_FROM, b.PRICE as PRICE_FROM, TOKEN_OUT_CONTRACT as TOKEN_TO, c.SYMBOL as SYMBOL_TO, TOKEN_OUT_AMOUNT as AMOUNT_TO, c.PRICE as PRICE_TO, TX_ID as TX_HASH from flow.core.ez_swaps a left join flow_prices_raw b on a.TOKEN_IN_CONTRACT=b.TOKEN_CONTRACT and date_trunc(day,a.BLOCK_TIMESTAMP)=b.day left join flow_prices_raw c on a.TOKEN_OUT_CONTRACT=c.TOKEN_CONTRACT and date_trunc(day,a.BLOCK_TIMESTAMP)=c.day where BLOCK_TIMESTAMP::date>=CURRENT_DATE-{{Days_Before}} and TOKEN_TO!=TOKEN_FROM), flow_prices as ( select date_trunc(day,BLOCK_TIMESTAMP) as day, TOKEN_ADDRESS,SYMBOL, avg(PRICE) as PRICE from (select BLOCK_TIMESTAMP, TOKEN_FROM as TOKEN_ADDRESS, SYMBOL_FROM as SYMBOL, div0((AMOUNT_TO*PRICE_TO),AMOUNT_FROM) as PRICE from flow_swaps_raw where PRICE_FROM is null and AMOUNT_FROM>0 and AMOUNT_TO>0 and PRICE_TO is not null union all select BLOCK_TIMESTAMP, TOKEN_TO as TOKEN_ADDRESS, SYMBOL_TO as SYMBOL, div0((AMOUNT_FROM*PRICE_FROM),AMOUNT_TO) as PRICE from flow_swaps_raw where PRICE_TO is null and AMOUNT_TO>0 and AMOUNT_FROM>0 and PRICE_FROM is not null) where PRICE<10000 group by 1,2,3), flow_swaps_raw_price as ( select BLOCK_TIMESTAMP, PROJECT_NAME, SWAPPER, TOKEN_FROM, SYMBOL_FROM, AMOUNT_FROM, ifnull(PRICE_FROM, x.PRICE) as PRICE_FROM, TOKEN_TO, SYMBOL_TO, AMOUNT_TO, ifnull(PRICE_TO,y.PRICE) as PRICE_TO, TX_HASH from flow_swaps_raw left join flow_prices x on x.TOKEN_ADDRESS=TOKEN_FROM and date_trunc(day, BLOCK_TIMESTAMP)=x.day left join flow_prices y on y.TOKEN_ADDRESS=TOKEN_TO and date_trunc(day, BLOCK_TIMESTAMP)=y.day), flow_swaps as ( select BLOCK_TIMESTAMP, 'Flow' as chain, PROJECT_NAME, SWAPPER, TOKEN_FROM, SYMBOL_FROM, AMOUNT_FROM, TOKEN_TO, SYMBOL_TO, AMOUNT_TO, case when (PRICE_TO*AMOUNT_TO is null or PRICE_TO*AMOUNT_TO>100000000) and (PRICE_FROM*AMOUNT_FROM is null or PRICE_FROM*AMOUNT_FROM>100000000) then 0 when PRICE_TO*AMOUNT_TO is null or PRICE_TO*AMOUNT_TO>PRICE_FROM*AMOUNT_FROM then PRICE_FROM*AMOUNT_FROM else PRICE_TO*AMOUNT_TO end as VALUE_USD,TX_HASH from flow_swaps_raw_price ), --------------------------------------------------------------------Osmosis-------------------------------------------------------------------- osmosis_prices_raw as ( select date_trunc(day, RECORDED_AT) as day, SYMBOL, avg(PRICE) as PRICE from osmosis.core.dim_prices where RECORDED_AT::date>=CURRENT_DATE-{{Days_Before}} group by 1,2 union all select date_trunc(day, RECORDED_AT) as day, 'USDC' as SYMBOL, avg(1) as PRICE from osmosis.core.dim_prices where RECORDED_AT::date>=CURRENT_DATE-{{Days_Before}} group by 1,2 union all select date_trunc(day, RECORDED_AT) as day, 'DAI' as SYMBOL, avg(1) as PRICE from osmosis.core.dim_prices where RECORDED_AT::date>=CURRENT_DATE-{{Days_Before}} group by 1,2 union all select date_trunc(day, RECORDED_AT) as day, 'USDT.grv' as SYMBOL, avg(1) as PRICE from osmosis.core.dim_prices where RECORDED_AT::date>=CURRENT_DATE-{{Days_Before}} group by 1,2 ), osmosis_swaps_raw as ( select BLOCK_TIMESTAMP, 'Osmosis' as PROJECT_NAME, TRADER as SWAPPER, FROM_CURRENCY as TOKEN_FROM, b.PROJECT_NAME as SYMBOL_FROM, FROM_AMOUNT/pow(10,FROM_DECIMAL) as AMOUNT_FROM, x.PRICE as PRICE_FROM, TO_CURRENCY as TOKEN_TO, c.PROJECT_NAME as SYMBOL_TO, TO_AMOUNT/pow(10,TO_DECIMAL) as AMOUNT_TO, y.PRICE as PRICE_TO, TX_ID as TX_HASH from osmosis.core.fact_swaps a left join osmosis.core.dim_labels b on a.FROM_CURRENCY=b.ADDRESS left join osmosis.core.dim_labels c on a.TO_CURRENCY=c.ADDRESS --and date_trunc(day,a.BLOCK_TIMESTAMP)=c.day left join osmosis_prices_raw x on b.PROJECT_NAME=x.SYMBOL and date_trunc(day,a.BLOCK_TIMESTAMP)=x.day left join osmosis_prices_raw y on c.PROJECT_NAME=y.SYMBOL and date_trunc(day,a.BLOCK_TIMESTAMP)=y.day where BLOCK_TIMESTAMP::date>=CURRENT_DATE-{{Days_Before}} and TOKEN_TO!=TOKEN_FROM and TX_STATUS='SUCCEEDED'), osmosis_prices as ( select date_trunc(day,BLOCK_TIMESTAMP) as day, TOKEN_ADDRESS,SYMBOL, avg(PRICE) as PRICE from (select BLOCK_TIMESTAMP, TOKEN_FROM as TOKEN_ADDRESS, SYMBOL_FROM as SYMBOL, div0((AMOUNT_TO*PRICE_TO),AMOUNT_FROM) as PRICE from osmosis_swaps_raw where PRICE_FROM is null and AMOUNT_FROM>0 and AMOUNT_TO>0 and PRICE_TO is not null union all select BLOCK_TIMESTAMP, TOKEN_TO as TOKEN_ADDRESS, SYMBOL_TO as SYMBOL, div0((AMOUNT_FROM*PRICE_FROM),AMOUNT_TO) as PRICE from osmosis_swaps_raw where PRICE_TO is null and AMOUNT_TO>0 and AMOUNT_FROM>0 and PRICE_FROM is not null) where PRICE<10000 group by 1,2,3), osmosis_swaps_raw_price as ( select BLOCK_TIMESTAMP, PROJECT_NAME, SWAPPER, TOKEN_FROM, SYMBOL_FROM, AMOUNT_FROM, ifnull(PRICE_FROM, x.PRICE) as PRICE_FROM, TOKEN_TO, SYMBOL_TO, AMOUNT_TO, ifnull(PRICE_TO,y.PRICE) as PRICE_TO, TX_HASH from osmosis_swaps_raw left join osmosis_prices x on x.TOKEN_ADDRESS=TOKEN_FROM and date_trunc(day, BLOCK_TIMESTAMP)=x.day left join osmosis_prices y on y.TOKEN_ADDRESS=TOKEN_TO and date_trunc(day, BLOCK_TIMESTAMP)=y.day), osmosis_swaps as ( select BLOCK_TIMESTAMP, 'Osmosis' as chain, PROJECT_NAME, SWAPPER, TOKEN_FROM, SYMBOL_FROM, AMOUNT_FROM, TOKEN_TO, SYMBOL_TO, AMOUNT_TO, case when (PRICE_TO*AMOUNT_TO is null or PRICE_TO*AMOUNT_TO>100000000) and (PRICE_FROM*AMOUNT_FROM is null or PRICE_FROM*AMOUNT_FROM>100000000) then 0 when PRICE_TO*AMOUNT_TO is null or PRICE_TO*AMOUNT_TO>PRICE_FROM*AMOUNT_FROM then PRICE_FROM*AMOUNT_FROM else PRICE_TO*AMOUNT_TO end as VALUE_USD,TX_HASH from osmosis_swaps_raw_price ), --------------------------------------------------------------------Thorchain-------------------------------------------------------------------- thorchain_swaps as ( select BLOCK_TIMESTAMP, 'Thorchain' as chain, 'ThorChain' as PROJECT_NAME, FROM_ADDRESS as SWAPPER, FROM_ASSET as TOKEN_FROM, case when FROM_ASSET ilike '%/%' then split(split(FROM_ASSET,'-')[0],'/')[1] else split(split(FROM_ASSET,'-')[0],'.')[1] end as SYMBOL_FROM, FROM_AMOUNT as AMOUNT_FROM, TO_ASSET as TOKEN_TO, case when TO_ASSET ilike '%/%' then split(split(TO_ASSET,'-')[0],'/')[1] else split(split(TO_ASSET,'-')[0],'.')[1] end as SYMBOL_TO, TO_AMOUNT as AMOUNT_TO, TO_AMOUNT_USD as VALUE_USD, TX_ID as TX_HASH from thorchain.core.fact_swaps where BLOCK_TIMESTAMP::date>=CURRENT_DATE-{{Days_Before}} and TOKEN_TO!=TOKEN_FROM), --------------------------------------------------------------------#####-------------------------------------------------------------------- ---ethereum,optimism,avalanche,polygon,arbitrum,algorand,solana,flow,near,osmosis,thorchain swaps_tagged as (select PROJECT_NAME, tx_hash, case when VALUE_USD<=1 then '(0 - 1] $' when VALUE_USD>1 and VALUE_USD<=10 then '(1 - 10] $' when VALUE_USD>10 and VALUE_USD<=100 then '(10 - 100] $' when VALUE_USD>100 and VALUE_USD<=1000 then '(100 - 1,000] $' when VALUE_USD>1000 and VALUE_USD<=10000 then '(1,000 - 10,000] $' else '(10,000 - ...] $' end as VALUE_USD_tag, value_usd from {{Chain}}_swaps) select PROJECT_NAME, VALUE_USD_tag, count(distinct tx_hash) as swaps, sum(value_usd) as volume from swaps_tagged group by 1,2

Chain Swaps Analyzer Tool