sarathop_bridge_analysis4
    Updated 2022-10-23
    with hop as ( select tx_hash
    from ethereum.core.fact_event_logs
    where origin_to_address = lower('0xb8901acb165ed027e32754e0ffe830802919727f')
    and event_inputs:chainId ='10'
    and event_name = 'TransferSentToL2'),

    optimism1_tr as (
    select date(block_timestamp) as day ,
    count(DISTINCT(tx_hash)) as total_bridge ,
    sum(amount) as amount ,
    sum(amount_usd) as volume
    from ethereum.core.ez_eth_transfers
    where origin_to_address = lower('0x52ec2f3d7c5977a8e558c8d9c6000b615098e8fc')
    and block_timestamp::date >= current_date - 90
    group by 1
    order by 1),
    optimism2_tr as ( select date(block_timestamp) as day ,
    count(DISTINCT(tx_hash)) as total_bridge ,
    sum(amount) as amount ,
    sum(amount_usd) as volume
    from ethereum.core.ez_eth_transfers
    where origin_to_address = lower('0x99c9fc46f92e8a1c0dec1b1747d010903e884be1')
    and block_timestamp::date >= current_date - 90
    group by 1
    order by 1),
    total_optimism_tr as (
    select day , total_bridge , amount , volume
    from optimism1_tr
    UNION
    select day ,total_bridge , amount , volume
    from optimism2_tr),
    final_optimism_tr as (
    Run a query to Download Data