Query editor
withfull_data as(select cast(_receptAmount as double) as _receptAmount ,evt_block_time,_market,'arbitrum' as chain from penpie_arbitrum.PendleStakingSideChain_evt_NewMarketDepositunion allselect -1*cast(_receptAmount as double)as _receptAmount,evt_block_time,_market,'arbitrum' as chain from penpie_arbitrum.PendleStakingSideChain_evt_NewMarketWithdrawunion allselect cast(_receptAmount as double) as _receptAmount ,evt_block_time,_market,'ethereum' as chain from penpie_ethereum.PendleStaking_evt_NewMarketDepositunion allselect -1*cast(_receptAmount as double)as _receptAmount,evt_block_time,_market,'ethereum' as chain from penpie_ethereum.PendleStaking_evt_NewMarketWithdrawunion allselect cast(_receptAmount as double) as _receptAmount ,evt_block_time,_market,'bnb' as chain from penpie_bnb.PendleStakingSideChain_evt_NewMarketDepositunion allselect -1*cast(_receptAmount as double)as _receptAmount,evt_block_time,_market,'bnb' as chain from penpie_bnb.PendleStakingSideChain_evt_NewMarketWithdrawunion allselect cast(_receptAmount as double) as _receptAmount ,evt_block_time,_market,'optimism' as chain from penpie_optimism.PendleStakingSideChain_evt_NewMarketDepositunion allselect -1*cast(_receptAmount as double)as _receptAmount,evt_block_time,_market,'optimism' as chain from penpie_optimism.PendleStakingSideChain_evt_NewMarketWithdraw),change as (select _market, chain, COALESCE(TRY(cast(sum(_receptAmount) FILTER (where evt_block_time <= now() - interval '1' hour) as DOUBLE)), cast(0 as DOUBLE)) as amount_1hr, COALESCE(TRY(cast(sum(_receptAmount) FILTER (where evt_block_time <= now() - interval '1' day)as DOUBLE)), cast(0 as DOUBLE)) as amount_1d, COALESCE(TRY(cast(sum(_receptAmount) FILTER (where evt_block_time <= now() - interval '7' day)as DOUBLE)), cast(0 as DOUBLE)) as amount_7d, COALESCE(TRY(cast(sum(_receptAmount) FILTER (where evt_block_time <= now() - interval '14' day)as DOUBLE)), cast(0 as DOUBLE)) as amount_14d, COALESCE(TRY(cast(sum(_receptAmount) FILTER (where evt_block_time <= now() - interval '30' day)as DOUBLE)), cast(0 as DOUBLE)) as amount_30dfrom full_datagroup by 1,2),_full as (select _market, chain, cast(sum(_receptAmount) as double) as total from full_datagroup by 1,2),-- select * from changedata as (select _full._market,_full.chain,COALESCE(TRY((total-amount_1hr)/amount_1hr), cast(0 as DOUBLE))*100 as amount_chg_1hr,COALESCE(TRY((total-amount_1d)/amount_1d), cast(0 as DOUBLE))*100 as amount_chg_1d,COALESCE(TRY((total-amount_7d)/amount_7d), cast(0 as DOUBLE))*100 as amount_chg_7d,COALESCE(TRY((total-amount_14d)/amount_14d), cast(0 as DOUBLE))*100 as amount_chg_14d,COALESCE(TRY((total-amount_30d)/amount_30d), cast(0 as DOUBLE))*100 as amount_chg_30d-- ,amount_chg_1d/total,amount_chg_7d/total,amount_chg_14d/total,amouLAST RUN TOOK 3 SECONDSfrom _full left join change ON_full._market=change._market),names as(select distinct _market,symbol,chain from(select _market,symbol,'arb' as chain from penpie_arbitrum.PendleStakingSideChain_call_registerPoolunion allselect _market,symbol,'bnb' as chain from penpie_bnb.PendleStakingSideChain_call_registerPoolunion allselect _market,symbol,'opt' as chain from penpie_optimism.PendleStakingSideChain_call_registerPoolunion allselect _market,symbol,'eth' as chain from penpie_ethereum.PendleStaking_call_registerPool))-- select * from _fullselect *, names.symbol fromdata left join nameson names._market=data._marketwhere data._market not in(0xb7ffe52ea584d2169ae66e7f0423574a5e15056f,0xc9becdbc62efb867cb52222b34c187fb170379c6,