bridge_addresses AS (

SELECT

address,

address_name,

project_name

FROM

crosschain.core.address_labels

WHERE

label_subtype = 'bridge'

AND blockchain = 'avalanche'

),

token_transfers AS (

SELECT

ft.*,

p.DECIMALS,

p.HOUR,

p.PRICE,

ba.address_name,

ba.project_name

FROM

avalanche.core.fact_token_transfers ft

JOIN avalanche.core.fact_hourly_token_prices p ON ft.CONTRACT_ADDRESS = p.TOKEN_ADDRESS

JOIN bridge_addresses ba ON ft.TO_ADDRESS = ba.address

WHERE

ft.TO_ADDRESS IN (

SELECT

address

FROM

bridge_addresses

)

AND ft.BLOCK_TIMESTAMP >= p.HOUR

AND ft.BLOCK_TIMESTAMP < p.HOUR + INTERVAL '1 HOUR'

AND p.IS_IMPUTED = false

),