weth_prices as (

select

date(hour) as date,

decimals,

symbol,

token_address,

avg(price) as price

from ethereum.core.fact_hourly_token_prices

where symbol = 'WETH'

group by date, decimals, symbol, token_address

),

ori_bridge_txs as (

select distinct

tt.block_timestamp,

tt.tx_hash,

tt.origin_from_address as bridger,

first_value(tt.contract_address) over (partition by tt.tx_hash order by "_LOG_ID") as from_token,

first_value(tt.raw_amount) over (partition by tt.tx_hash order by "_LOG_ID") as from_amount,

last_value(tt.contract_address) over (partition by tt.tx_hash order by "_LOG_ID") as bridged_token,

last_value(tt.raw_amount) over (partition by tt.tx_hash order by "_LOG_ID") as bridged_amount,

left(replace(replace(from_amount::string, '.'), 'e+', '000000'), 6)::decimal as upper_limit,

upper_limit * 0.95 as lower_limit,

len(lower_limit::int::string) as lower_limit_len -- for cases where the lower limit is one decimal place lower than upper limit, eg upper = 100000, lower = 95000

from optimism.core.fact_token_transfers tt

join optimism.core.fact_transactions t

on tt.tx_hash = t.tx_hash

where origin_to_address in (

'0x9cd619c50562a38edbdc3451ade7b58caa71ab32', -- zap

'0x470f9522ff620ee45df86c58e54e6a645fe3b4a7' -- zap 2

) -- zap bridge

and substr(input_data, 75, 64) in (

'0000000000000000000000000000000000000000000000000000000000000001', -- ethereum

'000000000000000000000000000000000000000000000000000000000000a86a', -- avalanche

'000000000000000000000000000000000000000000000000000000000000000a', -- optimism