select

date_trunc('day', hour) as day,

avg(price) as sol_usd

from ethereum.core.fact_hourly_token_prices

where

token_address = '0xd31a59c85ae9d8edefec411d448f90841571b89c' and

day >= '{{start_date}}' and day <= '{{end_date}}'

group by 1

select

date_trunc('day', block_timestamp) as date,

sum(sales_amount * sol_usd) as sales_volume,

avg(sales_amount * sol_usd) as avg_sales_price,

count(distinct purchaser) as unique_buyer,

count(distinct tx_id) as tx_count,

sum(sales_volume) over (order by date) as cum_sales_volume,

sum(tx_count) over (order by date) as cum_tx_count,

avg(tx_count) over (order by date, date rows between 6 preceding and current row) as ma7_sales_count,

avg(sales_volume) over (order by date, date rows between 6 preceding and current row) as ma7_sales_volume,

avg(unique_buyer) over (order by date, date rows between 6 preceding and current row) as ma7_unique_buyer,

avg(avg_sales_price) over (order by date, date rows between 6 preceding and current row) as ma7_avg_NFT_price

from solana.core.fact_nft_sales s join sol_prise p on block_timestamp::date = day

where

succeeded = true and

date >= '{{start_date}}' and date <= '{{end_date}}'

group by 1