WITH bridge_addresses AS (

SELECT address, address_name, project_name

FROM crosschain.core.address_labels

WHERE label_subtype = 'bridge'

AND blockchain = 'ethereum'

),

token_transfers AS (

SELECT

ft.*,

p.DECIMALS,

p.HOUR,

p.PRICE,

p.SYMBOL,

ba.address_name,

ba.project_name

FROM ethereum.core.fact_token_transfers ft

JOIN ethereum.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

AND ft.CONTRACT_ADDRESS NOT IN (

-- SPAM TOKENS

'0x1b3c515f58857e141a966b33182f2f3feecc10e9',

'0x96543ef8d2c75c26387c1a319ae69c0bee6f3fe7',

'0x2c4f1df9c7de0c59778936c9b145ff56813f3295',

'0xe9b076b476d8865cdf79d1cf7df420ee397a7f75'

)

AND ft.BLOCK_TIMESTAMP::date >= '2021-01-01'

),