select

tx_hash,

block_timestamp

from ethereum.defi.ez_bridge_activity

where block_timestamp::date >= dateadd('day', -{{past_days}}, current_date)

,

fee_eth as (

select

count(DISTINCT tx_hash) as n_bridge,

sum(tx_fee) as total_fee_eth,

avg(tx_fee) as avg_fee_eth,

median(tx_fee) as median_fee_eth

from ethereum.core.fact_transactions a

join bridge b using(tx_hash, block_timestamp)

where a.block_timestamp::date >= dateadd('day', -{{past_days}}, current_date)

,

eth_price as (

SELECT 'Ethereum' as network,

TO_TIMESTAMP(value[0]::string) as date,

value[1] as last_price

FROM (

SELECT livequery.live.udf_api(

'https://api.coingecko.com/api/v3/coins/ethereum/market_chart?vs_currency=usd&days=30') as response

),LATERAL FLATTEN (input => response:data:prices)

qualify row_number () over (order by date DESC) = 1

select

network

,date

,last_price

,n_bridge