sixahi6066Cluster Holdings Test
Updated 2024-06-04
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
-- 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