WITH t0 AS (

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-11-01'

AND t.block_timestamp::date < CURRENT_DATE::date

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 date_trunc('month', block_timestamp)::date AS date

, 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

GROUP BY 1

), t3 AS (

SELECT date_trunc('month', block_timestamp)::date AS date

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