fabre-baseacross-BridgeTransaction
    Updated 2022-06-22

    with hour_price as (
    select token_address, median(price) price from ethereum.core.fact_hourly_token_prices
    where
    hour > current_date - 1
    group by 1
    ), spokepool as (
    select
    tx_hash _tx_hash,
    date_trunc('day', block_timestamp) date,
    tokenflow_eth.hextoint(substr(data,3,64)) as amount ,
    tokenflow_eth.hextoint(substr(data,67,64)) as originChainId ,
    tokenflow_eth.hextoint(substr(data,131,64)) as destinationChainId,
    tokenflow_eth.hextoint(substr(data,195,64)) as relayerFeePct,
    tokenflow_eth.hextoint(substr(data,259,64)) as quoteTimestamp ,
    concat('0x', substr(data,347,64)) as recipient , *
    from ethereum.core.fact_event_logs
    where
    contract_address = '0x4d9079bb4165aeb4084c526a32695dcfd2f77381'
    and destinationChainId in ('10', '137', '288', '42161')
    and block_timestamp > CURRENT_DATE - 14
    )
    select

    date,
    event.contract_address as transfer_token,
    case when spokepool.destinationChainId = 10 then 'Optimism'
    when spokepool.destinationChainId = 137 then 'Polygon'
    when spokepool.destinationChainId = 288 then 'Boba'
    when spokepool.destinationChainId = 42161 then 'Arbitrum' end destinationChainName,
    c.symbol,
    hour_price.price,
    sum(amount / power(10, decimals)) as amt_token,
    sum(amount / power(10, decimals) * hour_price.price) as tvl,
    count( distinct event.tx_hash) as cnt_tx
    from spokepool
    Run a query to Download Data