elsina✅ Certain collection: Daily - single number
Updated 2023-04-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
›
⌄
with price as (
select
date_trunc('day', hour) as date,
avg(price) as usd_price
from ethereum.core.fact_hourly_token_prices
where
token_address = lower('0xD31a59c85aE9D8edEFeC411D448f90841571b89c')
group by 1
),
info as (
SELECT
date_trunc('day', block_timestamp) as "day",
sum(sales_amount * usd_price) as "sales volume",
avg(sales_amount * usd_price) as "avg nft price",
count(distinct purchaser) as "unique buyers",
count(distinct seller) as "unique sellers",
count(distinct tx_id) as "sales count"
FROM solana.core.fact_nft_sales s join price n on block_timestamp::date = date join solana.core.dim_nft_metadata m on s.mint = m.mint
where
"day" >= current_date - 90 and
project_name = '{{collection}}' and
succeeded = TRUE
group by 1
),
avg_info as (
select
avg("sales volume") as "daily sales volume (in usd)",
avg("avg nft price") as "daily nft price (in usd)",
avg("sales count") as "daily sales count",
avg("unique buyers") as "daily unique buyers",
avg("unique sellers") as "daily unique sellers"
from info
)
select *
from avg_info
Run a query to Download Data