forgashBTC Network Stats Claude v3
Updated 2024-08-27
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, let's create a CTE to identify the first appearance of each address
WITH first_appearance AS (
SELECT
PUBKEY_SCRIPT_ADDRESS,
DATE_TRUNC('week', MIN(BLOCK_TIMESTAMP)) AS first_week
FROM BITCOIN.CORE.FACT_OUTPUTS
WHERE PUBKEY_SCRIPT_ADDRESS IS NOT NULL
GROUP BY PUBKEY_SCRIPT_ADDRESS
),
-- Now, let's create our weekly metrics CTE
weekly_metrics AS (
SELECT
DATE_TRUNC('week', b.BLOCK_TIMESTAMP) AS week,
COUNT(DISTINCT b.BLOCK_NUMBER) AS blocks_per_week,
COUNT(DISTINCT t.TX_ID) AS transactions_per_week,
COUNT(DISTINCT o.PUBKEY_SCRIPT_ADDRESS) AS active_addresses,
COUNT(DISTINCT CASE
WHEN fa.first_week = DATE_TRUNC('week', b.BLOCK_TIMESTAMP) THEN o.PUBKEY_SCRIPT_ADDRESS
END) AS new_addresses
FROM
BITCOIN.CORE.FACT_BLOCKS b
LEFT JOIN BITCOIN.CORE.FACT_TRANSACTIONS t ON b.BLOCK_NUMBER = t.BLOCK_NUMBER
LEFT JOIN BITCOIN.CORE.FACT_OUTPUTS o ON t.TX_ID = o.TX_ID
LEFT JOIN first_appearance fa ON o.PUBKEY_SCRIPT_ADDRESS = fa.PUBKEY_SCRIPT_ADDRESS
GROUP BY
DATE_TRUNC('week', b.BLOCK_TIMESTAMP)
)
-- Main query with 4-week moving averages
SELECT
week,
AVG(blocks_per_week) OVER (ORDER BY week ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS blocks_per_week_4w_avg,
AVG(transactions_per_week) OVER (ORDER BY week ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS transactions_per_week_4w_avg,
AVG(active_addresses) OVER (ORDER BY week ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS active_addresses_4w_avg,
AVG(new_addresses) OVER (ORDER BY week ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS new_addresses_4w_avg
QueryRunArchived: QueryRun has been archived