hessAverage Fee uni
    Updated 2022-10-26
    with tx as ( select *
    from optimism.core.fact_event_logs
    where event_name = 'Swap')
    ,
    label as (
    select ADDRESS as token, PROJECT_NAME
    from optimism.core.dim_labels
    where label_type = 'dex'
    )
    ,
    from_token as ( select date(BLOCK_TIMESTAMP) as date , tx_hash as tx , origin_from_address as swapper, token , PROJECT_NAME as project, symbol as token_in , decimals as decimal_in , EVENT_INPUTS:value as amount_i
    from optimism.core.fact_event_logs a join label b on a.ORIGIN_TO_ADDRESS = b.token
    join optimism.core.dim_contracts c on a.contract_address = c.address
    where tx_hash in ( select tx_hash from tx) and origin_from_address = EVENT_INPUTS:from
    )
    ,
    to_token as ( select date(BLOCK_TIMESTAMP) as date_ , tx_hash , origin_from_address , token , PROJECT_NAME , symbol as token_out , decimals as decimal_out , EVENT_INPUTS:value as amount_ou
    from optimism.core.fact_event_logs a join label b on a.ORIGIN_TO_ADDRESS = b.token
    join optimism.core.dim_contracts c on a.contract_address = c.address
    where tx_hash in ( select tx_hash from tx) and origin_from_address = EVENT_INPUTS:to)
    ,
    final as ( select date , tx_hash, project_name , swapper , token_in , token_out , amount_i/pow(10,decimal_in) as amount_in , amount_ou/pow(10,decimal_out) as amount_out
    from from_token a left outer join to_token b on a.tx = b.tx_hash and date = date_
    )
    ,
    price as ( select date(hour) as p_date ,symbol , avg(price) as prices
    from optimism.core.fact_hourly_token_prices
    group by 1,2 )
    ,
    tb1 as ( select date , tx_hash, project_name , swapper , token_in , token_out ,amount_in , amount_in*b.prices as amount_in_usd, amount_out,
    amount_out*c.prices as amount_out_usd
    from final a left outer join price b on a.token_in = b.symbol and date = b.p_date
    left outer join price c on a.token_out = c.symbol and date = c.p_date)
    ,
    optimism as ( select date,'Optimism' as chain, tx_hash , project_name, swapper, token_in , amount_in_usd, token_out, amount_out_usd
    from tb1
    Run a query to Download Data