alirsQuixotic NFT Dashboard-part02
Updated 2022-08-03
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 data_price as (
select
hour::date as date,
avg(price) as price
from
ethereum.core.fact_hourly_token_prices
where
token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' --wrapped ETH
and
hour::date >= CURRENT_DATE - 90
group by 1
),
base2 as(select
ns.BLOCK_TIMESTAMP::date as day,
ns.ORIGIN_FROM_ADDRESS,tr.STATUS,
ns.ORIGIN_TO_ADDRESS,ns.PLATFORM_NAME,ns.PRICE_USD,ns.TX_HASH
,tr.ETH_VALUE,tr.FROM_ADDRESS,tr.TX_HASH
from
optimism.core.ez_nft_sales ns join optimism.core.fact_transactions tr on ns.tx_hash=tr.tx_hash
where day>=CURRENT_DATE-90)
,total as (select * from base2 join data_price b on base2.day = b.date)
-- )
select
sum(eth_value*price) as total_volumes_in_usd,
sum(eth_value) as total_volumes_in_ETH,
count(*) as total_Number_of_sales,
count(distinct from_address) as total_Number_of_unique_Buyers
from
total
WHERE day>=CURRENT_DATE-90 and STATUS='SUCCESS'
-- where date>=CURRENT_DATE-90
-- group by date
--limit 5
Run a query to Download Data