with op_sales as (

select

seller_address as seller,

buyer_address as buyer,

price_usd as sale_usd,

tx_hash as tx_id,

block_timestamp,

platform_name as platform

from optimism.core.ez_nft_sales a

join optimism.core.dim_labels b on a.nft_address = b.address

and event_type = 'sale'

and block_timestamp >= '2022-01-01'

select

date(block_timestamp) as date,

platform as type,

sum(sale_usd) as sale_volume_usd,

avg(sale_usd) as avg_volume_usd,

count(distinct tx_id) as n_sales,

count(distinct buyer) as n_buyers,

-count(distinct seller) as n_sellers,

sum(n_sales) over (order by date ) as cum_n_txns,

sum(n_buyers) over (order by date ) as cum_n_buyers,

sum(n_sellers) over (order by date ) as cum_n_sellers,

sum(sale_volume_usd) over (order by date ) as cum_sale_volume_usd,

avg(avg_volume_usd) over (order by date ) as avg_volume_usd_1,

avg(n_buyers) over ( order by date) as avg_n_buyers,

n_sales/(count(distinct date_trunc('day', block_timestamp))) as average_tx_day,

n_buyers/(count(distinct date_trunc('day', block_timestamp))) as average_buyer_day,

avg(avg_volume_usd) OVER (ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as MA_7_Days

from op_sales

group by date, type

order by date desc