select

date_trunc('month', block_timestamp) as month,

token_contract,

sum(amount) as monthly_token_amount_inbound

from flow.core.fact_bridge_transactions

where direction = 'inbound'

group by month, token_contract

),

outbound as (

select

date_trunc('month', block_timestamp) as month,

token_contract,

sum(amount) as monthly_token_amount_outbound

from flow.core.fact_bridge_transactions

where direction = 'outbound'

group by month, token_contract

),

net_flow as (

select

inbound.month,

coalesce (inbound.token_contract, outbound.token_contract) as full_token_name ,

split (coalesce (inbound.token_contract, outbound.token_contract) ,'.')[2] as token_contract_name,

nvl (monthly_token_amount_inbound, 0) as outbound_token_amount,

nvl (monthly_token_amount_outbound, 0) as inbound_token_amount

from inbound

left join outbound on (inbound.month = outbound.month and inbound.token_contract = outbound.token_contract)

),

prices as (

select

date_trunc('month', timestamp) as monthly,

token_contract,

avg(price_usd) as monthly_price