0xHaM-dCEX netflow
Updated 2024-06-23
999
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 Sandesh / CEX netflow @ https://flipsidecrypto.xyz/Sandesh/q/UOXSoeHRCPXT/cex-netflow
-- forked from CEX base @ https://flipsidecrypto.xyz/edit/queries/2a5114a1-1f9b-4c49-a62a-9ec5eb8871d9
with outt AS
(
with outflow AS
(
select
ft.block_timestamp,
ft.block_number,
ft.PUBKEY_SCRIPT_ADDRESS,
ft.value,
dl.label_type,
dl.label_subtype,
dl.address_name,
dl.label
from bitcoin.core.fact_inputs ft
inner join bitcoin.core.dim_labels dl
on ft.PUBKEY_SCRIPT_ADDRESS = dl.address
where 1=1
-- and ft.tx_id=('f8f3656083429056ccdba05815b424dc7da796d8c77bc4bc4503eb9cf3f55b7c')
-- and ft.block_timestamp >= '2024-01-01'
-- and ft.pubkey_script_address='bc1qm34lsc65zpw79lxes69zkqmk6ee3ewf0j77s3h'
and dl.label_type='cex'
and label_subtype='hot_wallet'
)
select
trunc(block_timestamp, '{{Time_Interval}}') as date,
label,
pubkey_script_address,
sum(value) as daily_outflow,
sum(daily_outflow) over ( partition by pubkey_script_address order by date asc) as total_outflow
from outflow
group by date,label,pubkey_script_address
order by 1 asc
QueryRunArchived: QueryRun has been archived