select

date_trunc('day', hour) as date,

avg(price) as sol_usd

from ethereum.core.fact_hourly_token_prices

where

token_address = '0xd31a59c85ae9d8edefec411d448f90841571b89c' and

date >= current_date - 90

group by 1

),

base as (

select *

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

where

succeeded = true and

block_timestamp::date >= current_date - 90

),

info as (

select

date_trunc('day', block_timestamp) as day,

-- periodly

sum(sales_amount) * avg(p.sol_usd) as sales_volume_usd,

avg(sales_amount) * avg(p.sol_usd) as avg_nft_price_usd,

count(distinct tx_id) as sales_count,

count(distinct purchaser) as unique_buyer,

count(distinct seller) as unique_seller,

-- MA(7)

avg(sales_volume_usd) over (order by day rows between 6 preceding and 0 following) as ma_7_sales_volume,

avg(avg_nft_price_usd) over (order by day rows between 6 preceding and 0 following) as ma_7_avg_nft_price,

avg(sales_count) over (order by day rows between 6 preceding and 0 following) as ma_7_sales_count,

avg(unique_buyer) over (order by day rows between 6 preceding and 0 following) as ma_7_unique_buyer,

avg(unique_seller) over (order by day rows between 6 preceding and 0 following) as ma_7_unique_seller,

-- Cumulative

sum(sales_volume_usd) over (order by day) as cumulative_sales_volume_usd,

sum(sales_count) over (order by day) as cumulative_sales_count,