Eman-RazBridge joe
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 arbitrum as (with tab1 as (select block_timestamp::date as date , tx_hash, origin_from_address as bridger, amount as volume_joe
from arbitrum.core.ez_token_transfers
where to_address='0x0000000000000000000000000000000000000000' and origin_function_signature='0x2cdf0b95'
and contract_address='0x371c7ec6d8039ff7933a2aa28eb827ffe1f52f07'),
tab2 as (select block_timestamp::date as date, tx_hash,
ethereum.public.udf_hex_to_int(substr(topics[1], 64, 8)) as destination_chain_id,
iff(destination_chain_id='102', 'Arbitrum➡BSC', 'Arbitrum➡Avalanche') as "Bridge_Route"
from arbitrum.core.fact_event_logs
where topics[0] = '0xd81fc9b8523134ed613870ed029d6170cbb73aa6a6bc311b9a642689fb9df59a' and tx_status = 'SUCCESS')
select date_trunc('{{Time_Frame}}',tab1.date) as "Date", count(distinct tab1.tx_hash) as "Bridging Count", count(distinct bridger) as "Bridger Count",
sum(volume_joe) as "Bridging Volume (JOE)", "Bridge_Route"
from tab1 left join tab2 on tab1.date=tab2.date and tab1.tx_hash=tab2.tx_hash
where tab1.date>='{{Start_Date}}' and tab1.date<='{{End_Date}}'
group by 1,5
order by 1),
------------------------------------------------------------------------------------------------------------------------------------
bsc as (with tab1 as (select block_timestamp::date as date , tx_hash, origin_from_address as bridger, amount as volume_joe
from bsc.core.ez_token_transfers
where to_address='0x0000000000000000000000000000000000000000' and origin_function_signature='0x2cdf0b95'
and contract_address='0x371c7ec6d8039ff7933a2aa28eb827ffe1f52f07'),
tab2 as (select block_timestamp::date as date, tx_hash,
ethereum.public.udf_hex_to_int(substr(topics[1], 64, 8)) as destination_chain_id,
iff(destination_chain_id='106', 'BSC➡Avalanche', 'BSC➡Arbitrum') as "Bridge_Route"
from bsc.core.fact_event_logs
where topics[0] = '0xd81fc9b8523134ed613870ed029d6170cbb73aa6a6bc311b9a642689fb9df59a' and tx_status = 'SUCCESS')
select date_trunc('{{Time_Frame}}',tab1.date) as "Date", count(distinct tab1.tx_hash) as "Bridging Count", count(distinct bridger) as "Bridger Count",
sum(volume_joe) as "Bridging Volume (JOE)", "Bridge_Route"
from tab1 left join tab2 on tab1.date=tab2.date and tab1.tx_hash=tab2.tx_hash
Run a query to Download Data