fabre-baseacross-BridgeTransaction
Updated 2022-06-22
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
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