with prices as(

select

date_trunc('day', recorded_hour) day,

id,

token,

avg(close) price

from flow.core.fact_hourly_prices

where recorded_hour::date >= current_date - 365

group by day, id, token

),

bridge as(

select

date_trunc('day', block_timestamp) day,

token_contract,

sum(amount) total_bridge_amount

from flow.core.ez_bridge_transactions

where block_timestamp::date >= current_date - 365

group by day, token_contract

order by day, token_contract

),

main as(

select

day,

sum(total_bridge_amount_USD) bridged_volume_USD,

count(distinct token_contract) unique_bridged_tokens

from(

select bridge.*,

price,

(total_bridge_amount * price) total_bridge_amount_USD

from bridge

left join prices

on bridge.day = prices.day and bridge.token_contract = prices.id

order by bridge.day

) x