KeyrockSEI TX COUNT + Price 180D
Updated 2024-05-30
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
›
⌄
-- forked from OSMO TX COUNT + Price 180D copy @ https://flipsidecrypto.xyz/edit/queries/4cb5b140-5033-4143-8510-35a953daaf62
-- forked from sei TX COUNT + Price 180D @ https://flipsidecrypto.xyz/edit/queries/8bbf34c8-863a-4a86-93ba-51167aff4c06
-- forked from sei TX COUNT + Price 180D @ https://flipsidecrypto.xyz/edit/queries/a6b8ab6d-7af1-4a58-bcea-f43b274070c2
WITH
CTE_1 AS (
WITH raw_data AS (
SELECT livequery.live.udf_api('https://api.coingecko.com/api/v3/coins/sei-network/market_chart?vs_currency=usd&days=365') 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)
)
,
min_block_timestamp AS (
SELECT MIN(block_timestamp) AS min_timestamp
FROM sei. core.fact_transactions
)
SELECT
CTE_1.day_price,
CTE_1.price,
COUNT(DISTINCT FACT_TRANSACTIONS_ID) AS daily_transactions
FROM
sei.core.fact_transactions
CROSS JOIN
min_block_timestamp
JOIN
CTE_1
QueryRunArchived: QueryRun has been archived