WITH inputs AS (

SELECT

tx_id,

COALESCE(project_name, CAST(address_group AS varchar)) AS from_entity

FROM

BITCOIN.CORE.FACT_INPUTS fi

LEFT JOIN BITCOIN.CORE.DIM_ENTITY_CLUSTERS ec ON fi.pubkey_script_address = ec.address

WHERE

block_timestamp >= '2023-10-24'

AND block_timestamp < '2023-10-25'

group by

tx_id,

from_entity

),

transfers AS (

fo.tx_id,

block_timestamp,

from_entity,

COALESCE(

project_name,

CAST(address_group AS varchar),

PUBKEY_SCRIPT_ADDRESS

) AS to_entity,

sum(value) AS transfer_amount

BITCOIN.CORE.FACT_OUTPUTS fo

LEFT JOIN BITCOIN.CORE.DIM_ENTITY_CLUSTERS ec ON fo.pubkey_script_address = ec.address

LEFT JOIN inputs ON inputs.tx_id = fo.tx_id

-- don't forget to subset time or this query will be loooooong

block_timestamp >= '2023-10-24'

AND block_timestamp < '2023-10-25'

AND from_entity != to_entity

GROUP BY