marquOP Bridgers - txs users pies
    Updated 2022-11-07
    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