SELECT

MIN(BLOCK_TIMESTAMP) as first_date,

FROM osmosis.core.fact_transactions

GROUP by 2

),

swap as (SELECT

first_date,

trader,

PROJECT_NAME,

to_currency,

to_amount / to_decimal as amount

FROM osmosis.core.fact_swaps

LEFT OUTER JOIN first_t ON trader = tx_from AND first_date = BLOCK_TIMESTAMP

LEFT outer JOIN osmosis.core.dim_tokens ON to_currency = address

WHERE NOT tx_from is NULL AND first_date::date >= '2022-01-01' AND tx_status = 'SUCCEEDED'

),

transfer_out as (select

block_timestamp,

sender,

d.project_name,

currency,

t.amount / t.decimal as amount,

REGEXP_SUBSTR (receiver,'[^1]+',1) as chains

from osmosis.core.fact_transfers t

INNER JOIN swap ON trader = sender AND block_timestamp > first_date

LEFT outer JOIN osmosis.core.dim_tokens d ON currency = address

where tx_status = 'SUCCEEDED' AND transfer_type = 'IBC_TRANSFER_OUT' and sender ILIKE 'osmo%'

having chains NOT LIKE '0%' --filter out non-chain transfer

)

select