sixahi6066Cluster Holdings Test
    Updated 2024-06-04
    -- forked from zakkisyed / US Govt BTC Holdings - Daily Net Flow By Seizure @ https://flipsidecrypto.xyz/zakkisyed/q/N87miTUcXvPz/us-govt-btc-holdings---daily-net-flow-by-seizure

    -- forked from Bitcoin ETFs - Daily Net Flow @ https://flipsidecrypto.xyz/edit/queries/a0f6ba71-d036-4989-931c-7d2aecdff190

    -- forked from Bitcoin ETFs - Net Holdings @ https://flipsidecrypto.xyz/edit/queries/7224ec56-3baf-46d3-9c93-79fc5ce248ad

    -- Select ETF_name, net_holdings, 1D_change, 7D_Change
    -- from final_table




    WITH RH_Address_List AS (
    SELECT 'Robinhood' as cluster_name, '1P5ZEDWTKTFGxQjZphgWPQUpe554WKDfHQ' as address, 'RH Cold #1' as label_name
    UNION ALL
    SELECT 'Robinhood' as cluster_name, '1LQoWist8KkaUXSPKZHNvEyfrEkPHzSsCd' as address, 'RH Cold #2' as label_name
    UNION ALL
    SELECT 'Robinhood' as cluster_name, 'bc1ql49ydapnjafl5t2cp9zqpjwe6pdgmxy98859v2' as address, 'RH Cold #3' as label_name
    UNION ALL
    SELECT 'Robinhood' as cluster_name, 'bc1qhk0ghcywv0mlmcmz408sdaxudxuk9tvng9xx8g' as address, 'RH Internal #2?' as label_name
    ),
    -- Aggregating input values with daily truncation
    inputs as (
    SELECT PUBKEY_SCRIPT_ADDRESS as address, DATE(block_timestamp) as day, sum(-value) as daily_input_balance
    FROM bitcoin.core.fact_inputs
    -- WHERE block_timestamp >= '2024-01-01'
    GROUP BY PUBKEY_SCRIPT_ADDRESS, DATE(block_timestamp)
    ),

    -- Aggregating output values with daily truncation
    outputs as (
    SELECT PUBKEY_SCRIPT_ADDRESS as address, DATE(block_timestamp) as day, sum(value) as daily_output_balance
    FROM bitcoin.core.fact_outputs
    -- WHERE block_timestamp >= '2024-01-01'
    GROUP BY PUBKEY_SCRIPT_ADDRESS, DATE(block_timestamp)
    ),
    QueryRunArchived: QueryRun has been archived