WITH Receive AS (

SELECT

PUBKEY_SCRIPT_ADDRESS as address,

sum(value) AS value

FROM bitcoin.core.fact_outputs

where PUBKEY_SCRIPT_ADDRESS is not null

and block_timestamp < current_date - 365

GROUP BY 1

), Send AS (

SELECT

PUBKEY_SCRIPT_ADDRESS as address, SUM(-value) AS value

FROM bitcoin.core.fact_inputs

where PUBKEY_SCRIPT_ADDRESS is not null

and block_timestamp < current_date - 365

GROUP BY 1

select address, sum(value) as amount

from (

select * from Receive

union all

select * from Send

WHERE NOT address in (

SELECT DISTINCT address

from bitcoin.core.dim_labels

where label_type like 'cex'

GROUP by 1

ORDER BY 2 DESC