KeyrockSolana TX COUNT + PRICE 180D
Updated 2024-03-11
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
›
⌄
⌄
/*SELECT
DATEADD(DAY, FLOOR((block_id - 1) / 194264) + 1, '2020-03-16 00:00:00.000') AS transaction_date,
COUNT(DISTINCT FACT_TRANSACTIONS_ID) AS daily_transactions
FROM
solana.core.fact_transactions
WHERE
block_id <= 194264 * 180 -- Limit the blocks to 217k*180 days (assuming 217k blocks per day)
GROUP BY
transaction_date
ORDER BY
transaction_date;*/
WITH
transaction_summary AS (
SELECT
DATEADD(DAY, FLOOR((block_id - 1) / 194264) + 1, '2020-03-16 00:00:00.000') AS transaction_date,
COUNT(DISTINCT FACT_TRANSACTIONS_ID) AS daily_transactions
FROM
solana.core.fact_transactions
WHERE
block_id <= 194264 * 180 -- Limit the blocks to 217k*180 days (assuming 217k blocks per day)
GROUP BY
transaction_date
),
CTE_1 AS (
WITH raw_data AS (
SELECT livequery.live.udf_api('https://api.coingecko.com/api/v3/coins/solana/market_chart?vs_currency=usd&days=max') AS resp
)
SELECT
'prices' AS key,
TO_TIMESTAMP(value[0]::string) AS day_price,
value[1] AS price
FROM
raw_data,
LATERAL FLATTEN (input => resp:data:prices)
)
QueryRunArchived: QueryRun has been archived