select date_trunc('day', timestamp)::Date as days,

avg(price_usd) as flow_price

from flow.core.fact_prices

group by 1),

final as (select

s.*,

case

when currency = 'A.1654653399040a61.FlowToken' then price*flow_price else price end as sale_usd

from flow.core.fact_nft_sales s,flow_prices p

where tx_succeeded = 'TRUE'

and s.BLOCK_TIMESTAMP::date = p.days

select

date_trunc(day,BLOCK_TIMESTAMP)::date as days,

avg(sale_usd) as avg_sale_volume_usd,

sum(sale_usd) as sale_volume_usd,

sum(sale_volume_usd)over(order by days ) as sale_volume_usd_growth,

count(distinct BUYER) as unique_BUYER,

sum(unique_BUYER)over(order by days ) as unique_BUYER_growth,

count(distinct SELLER) as unique_SELLER,

sum(unique_SELLER)over(order by days ) as unique_SELLER_growth,

count(distinct TX_ID) as sales,

sum(sales)over(order by days ) as sales_growth,

count(distinct NFT_ID) as unique_nft_sales,

sum(unique_nft_sales)over(order by days ) as unique_nft_sales_growth

,sum(tokenflow[0]:TokensWithdrawn:amount) as sale_amount,

sum(sale_amount)over(order by days ) as sale_amount_growth,

avg(sale_usd) as avg_volume_usd,

avg(avg_volume_usd)over(order by days rows between 7 preceding and current row) as sale_volume_7