0-MIDsize
    Updated 2025-03-14
    with tab1 as (
    select BLOCK_TIMESTAMP::date as day
    ,DECODED_LOG:bridgeData:bridge as protocol
    ,DECODED_LOG:bridgeData:sendingAssetId as token_address
    ,DECODED_LOG:bridgeData:minAmount as volume
    ,DECODED_LOG:bridgeData:destinationChainId as dst_chain
    ,ORIGIN_FROM_ADDRESS
    ,TX_HASH
    from base.core.ez_decoded_event_logs
    where EVENT_NAME='LiFiTransferStarted'
    and DECODED_LOG:bridgeData:integrator='jumper.exchange'
    and DECODED_LOG:bridgeData:destinationChainId='146'
    ),
    tab2 as (
    select HOUR::date as day
    ,TOKEN_ADDRESS
    ,SYMBOL
    ,DECIMALS
    ,avg(PRICE) as usd_price
    from base.price.ez_prices_hourly
    group by 1,2,3,4
    union all
    select HOUR::date as day
    ,'0x0000000000000000000000000000000000000000' as TOKEN_ADDRESS
    ,'ETH' as SYMBOL
    ,'18' as DECIMALS
    ,avg(PRICE) as usd_price
    from base.price.ez_prices_hourly
    where SYMBOL='WETH'
    group by 1,2,3,4
    )
    select case
    when (volume/pow(10,DECIMALS)*usd_price)>0 and (volume/pow(10,DECIMALS)*usd_price)<10 then 'Below 10 $'
    when (volume/pow(10,DECIMALS)*usd_price)>=10 and (volume/pow(10,DECIMALS)*usd_price)<=100 then '10~100 $'
    when (volume/pow(10,DECIMALS)*usd_price)>100 and (volume/pow(10,DECIMALS)*usd_price)<=1000 then '100~1K $'
    when (volume/pow(10,DECIMALS)*usd_price)>1000 and (volume/pow(10,DECIMALS)*usd_price)<=10000 then '1K~10K $'
    QueryRunArchived: QueryRun has been archived