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