Derek2628BTC Transfers
Updated 2023-11-10
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
›
⌄
-- first the input entity for each transaction
-- the amount doesn't matter, we really just need to know the inputs
-- we're grouping by every var at the end so that we don't have input dupes
-- we don't care about the input address! just the entity (address group)
WITH inputs AS (
SELECT
tx_id,
COALESCE(project_name, CAST(address_group AS varchar)) AS from_entity
FROM
BITCOIN.CORE.FACT_INPUTS fi
LEFT JOIN BITCOIN.CORE.DIM_ENTITY_CLUSTERS ec ON fi.pubkey_script_address = ec.address
WHERE
block_timestamp >= '2023-10-24'
AND block_timestamp < '2023-10-25'
group by
tx_id,
from_entity
) -- next get the outputs and merge in the inputs
-- sum up the output value by entity as the transfer amount
-- if there is no project name, or entity, use the pubkey
-- (presumably this is a new address - it could be the change address or just a new address,
-- we'll find out in time)
SELECT
fo.tx_id,
block_timestamp,
from_entity,
COALESCE(
project_name,
CAST(address_group AS varchar),
PUBKEY_SCRIPT_ADDRESS
) AS to_entity,
sum(value) AS transfer_amount
FROM
BITCOIN.CORE.FACT_OUTPUTS fo
LEFT JOIN BITCOIN.CORE.DIM_ENTITY_CLUSTERS ec ON fo.pubkey_script_address = ec.address
LEFT JOIN inputs ON inputs.tx_id = fo.tx_id
Run a query to Download Data