elvis22/7 NFT Top Shots: What makes a Top Shots moment valuable? (Part I) : TS Volume
Updated 2022-11-08Copy Reference Fork
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
hourly_prices_t1 AS (
SELECT date_trunc('hour',timestamp) as block_hour, token_contract, avg(price_usd) as price_usd
FROM flow.core.fact_prices
WHERE token_contract = 'A.1654653399040a61.FlowToken'
GROUP BY 1,2
),
hourly_prices AS (
SELECT *
FROM (SELECT DISTINCT block_hour FROM hourly_prices_t1) T CROSS JOIN (
SELECT key AS token_contract, value[0] AS price_usd
FROM table(flatten(input => parse_json('{
"A.b19436aae4d94622.FiatToken":[1],
"A.3c5959b568896393.FUSD":[1],
"A.ead892083b3e2c6c.DapperUtilityCoin":[1]
}')
, outer => true))
) UNION (SELECT * FROM hourly_prices_t1)
),
names_marketplace as (
SELECT key AS tech_name, value[0] AS marketplace
FROM table(flatten(input => parse_json('{
"A.c1e4f4f4c4257510.TopShotMarketV3":["TopShotMarketV3"],
"A.4eb8a10cb9f87357.NFTStorefront":["NFTStorefront"],
"A.c1e4f4f4c4257510.Market":["TopShotMarketV2"]
}')
, outer => true))
),
TopShot_Volume AS (
SELECT date_trunc('day', block_timestamp) as date, N.marketplace, currency, sum(price*price_usd) as daily_USD_volume
FROM flow.core.fact_nft_sales S LEFT JOIN hourly_prices P ON (date_trunc('hour',S.block_timestamp) = P.block_hour AND S.currency = P.token_contract) LEFT JOIN names_marketplace N ON S.marketplace = N.tech_name
WHERE NFT_collection IN (SELECT NFT_collection FROM flow.core.dim_topshot_metadata)
AND tx_succeeded = TRUE
GROUP BY 1,2,3
)
Run a query to Download Data