sqrr_researchTest Ord 7
Updated 2023-12-19
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
›
⌄
WITH total
AS (
SELECT date_trunc('day', BLOCK_TIMESTAMP) AS "DATE"
,sum(FEE) AS "Total Fees"
,count(TX_HASH) AS "Total Transactions"
FROM bitcoin.core.fact_transactions
WHERE 1 = 1
AND BLOCK_NUMBER > 767429
GROUP BY 1
)
,ordinals
AS (
SELECT date_trunc('day', BLOCK_TIMESTAMP) AS "DATE"
,sum(FEE) AS "Ordinals Fees"
,count(TX_HASH) AS "Ordinals Transactions"
FROM bitcoin.core.fact_transactions
WHERE 1 = 1
AND BLOCK_NUMBER > 767429
AND HEX::STRING LIKE '%0063036f726401%'
GROUP BY 1
)
,prices
AS (
SELECT date_trunc('day', HOUR) AS "DATE"
,avg(PRICE) AS "Price"
FROM bitcoin.price.ez_hourly_token_prices
WHERE PROVIDER = 'coingecko'
GROUP BY 1
)
SELECT t."DATE"
,"Ordinals Fees"
,"Ordinals Fees" * "Price" AS "Ordinals Fees (USD)"
,"Total Fees"
,"Total Fees" * "Price" AS "Total Fees (USD)"
,"Ordinals Transactions"
,"Total Transactions"
Run a query to Download Data