flipside

    2023-10-31 04:32 PM
    jackguy2023-10-31 04:32 PM
    Updated 2023-10-31
    -- forked from Over 10k BTC @ https://flipsidecrypto.xyz/edit/queries/1f99386b-14ec-4739-8e33-4b91c300bab1

    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
    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
    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
    Run a query to Download Data