-- forked from Avalanche bridges deposits @ https://flipsidecrypto.xyz/edit/queries/5278c937-1715-4fc1-a52d-c25f8de03aa9

WITH 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,

p.SYMBOL,

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

),

token_transfers_with_usd AS (

SELECT

*,

RAW_AMOUNT / POW(10, DECIMALS) * PRICE AS amount_usd

FROM token_transfers