KeyrockSEI - NFTs by PLATFORM
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 SEI - NFTs @ https://flipsidecrypto.xyz/edit/queries/947384eb-8226-4edf-9477-d992468a46e7
WITH
sei_CTE 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 sei_price
FROM
raw_data,
LATERAL FLATTEN (input => resp:data:prices)
),
nft_data as (
SELECT
date_trunc('day', block_timestamp) AS day,
COUNT(DISTINCT(TX_ID)) as count,
platform_name as marketplace,
SUM(amount) AS total_amount
FROM sei.nft.ez_nft_sales
WHERE EVENT_TYPE = 'sale'
GROUP BY 1,3
)
SELECT
nft.day,
nft.count,
nft.marketplace,
nft.total_amount * pr.sei_price as "NFT_$amount",
pr.sei_price,
SUM(nft.total_amount * pr.sei_price) OVER (ORDER BY nft.day) AS cumulative_nft_amount
FROM
nft_data nft
QueryRunArchived: QueryRun has been archived