hesamTypes of plays - Volume (From 2022-06-01) Query
Updated 2022-10-13
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 prices as (
select
date_trunc('week', timestamp) as date,
avg(price_usd) as flow_price
from
flow.core.fact_prices
where
symbol = 'FLOW'
and source ='coinmarketcap'
group by
1
),
datas as (
select
date_trunc('week', block_timestamp) as date0,
play_type,
case
when NFTs.currency ='A.1654653399040a61.FlowToken' then NFTs.price * prices.flow_price
else NFTs.price
end as end_price
from
flow.core.EZ_NFT_SALES NFTs
join prices on date_trunc('week', block_timestamp) = prices.date
join flow.core.dim_topshot_metadata d on NFTs.nft_id = d.nft_id
where
block_timestamp::date >= '2022-06-01' and
NFTs.nft_collection ilike '%TopShot'
and tx_succeeded = 'TRUE'
)
select
date0 as Date,
play_type,
sum(end_price) as "Volume ($USD)"
FROM
datas
group by