SELECT DISTINCT tx_id, block_timestamp, signers[0]::string AS signer

FROM solana.core.fact_events e

WHERE block_timestamp >= '2021-01-01'

AND program_id = 'wormDTUJ6AWPNvk59vGQbDvGJmqbDTdgWgAqcLBCgUb'

), t1 AS (

SELECT t.tx_id

, t.block_timestamp

, SUM(CASE WHEN t.tx_from = t0.signer THEN amount ELSE -amount END) AS amt_bridged_off_sol

, SUM(CASE WHEN t.tx_to = t0.signer THEN amount ELSE -amount END) AS amt_bridged_to_sol

FROM solana.core.fact_transfers t

JOIN t0

ON t0.block_timestamp = t.block_timestamp

AND t0.tx_id = t.tx_id

WHERE t.block_timestamp >= '2021-01-01'

AND mint LIKE 'So111%'

AND tx_from <> tx_to

AND (

tx_from = t0.signer

OR tx_to = t0.signer

)

GROUP BY 1, 2

), t2 AS (

SELECT SUM(GREATEST(0, amt_bridged_off_sol)) AS amt_bridged_off_sol

, SUM(GREATEST(0, amt_bridged_to_sol)) AS amt_bridged_to_sol

FROM t1

), t3 AS (

SELECT SUM(raw_amount) * POWER(10, -9) AS amount_bridged_to_eth

, COUNT(DISTINCT to_address) AS n_bridgers_to_eth

FROM ethereum.core.fact_token_transfers

WHERE block_timestamp >= '2021-01-01'

AND contract_address = '0xd31a59c85ae9d8edefec411d448f90841571b89c'

AND from_address = '0x0000000000000000000000000000000000000000'

), t4 AS (

SELECT SUM(raw_amount) * POWER(10, -9) AS amount_bridged_to_poly

, COUNT(DISTINCT to_address) AS n_bridgers_to_poly