marquOP Bridgers - txs users pies
Updated 2022-11-07
999
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
hop_txs as (
select
event_logs.block_timestamp,
event_logs.tx_hash,
ifnull(txs_token.origin_from_address, txs_eth.origin_from_address) as user_address,
'Hop' as bridge
from (
select block_timestamp, tx_hash, event_inputs:chainId::int as chain_id, contract_address
from ethereum.core.fact_event_logs logs
where contract_address in (select address from ethereum.core.dim_labels
where address_name like 'hop exchange:%bridge' or address_name like 'hop protocol:%bridge')
and event_name = 'TransferSentToL2'
and event_inputs:chainId::int = 10
and tx_status = 'SUCCESS'
and block_timestamp > current_date() - interval '{{months}} months'
) event_logs
left join ethereum.core.ez_token_transfers txs_token
on event_logs.tx_hash = txs_token.tx_hash
and event_logs.contract_address = txs_token.to_address
left join ethereum.core.ez_eth_transfers txs_eth
on event_logs.tx_hash = txs_eth.tx_hash
and event_logs.contract_address = txs_eth.eth_to_address
and txs_eth.eth_to_address = '0xb8901acb165ed027e32754e0ffe830802919727f'
where not exists (
select 1 from ethereum.core.dim_labels labels
where (address_name like 'hop exchange:%bonder'
or address_name like 'hop protocol:%bonder')
and (labels.address = txs_token.from_address
or labels.address = txs_eth.eth_from_address)
)
Run a query to Download Data