-- total

sum(price_usd) as sales_volume_usd,

avg(price_usd) as avg_sales_price_usd,

count(distinct tx_hash) as sales_count,

count(distinct buyer_address) as unique_buyer,

count(distinct seller_address) as unique_seller,

-- percentage change

lag(sales_volume_usd,1) over(order by date) as previous_sales_volume_usd,

((sales_volume_usd - previous_sales_volume_usd) / previous_sales_volume_usd) * 100 as "volume change (%)",

lag(avg_sales_price_usd,1) over(order by date) as previous_avg_sales_price_usd,

((avg_sales_price_usd - previous_avg_sales_price_usd) / previous_avg_sales_price_usd) * 100 as "avg price change (%)",

lag(sales_count,1) over(order by date) as previous_sales_count,

((sales_count - previous_sales_count) / previous_sales_count) * 100 as "sale change (%)",

lag(unique_buyer,1) over(order by date) as previous_unique_buyer,

((unique_buyer - previous_unique_buyer) / previous_unique_buyer) * 100 as "buyer change (%)",

lag(unique_seller,1) over(order by date) as previous_unique_seller,

((unique_seller - previous_unique_seller) / previous_unique_seller) * 100 as "seller change (%)",

-- colour

case when "volume change (%)" >= 0 then 'Positive' else 'Negative' end as colour_volume,

case when "avg price change (%)" >= 0 then 'Positive' else 'Negative' end as colour_avg_price,

case when "sale change (%)" >= 0 then 'Positive' else 'Negative' end as colour_sale,

case when "buyer change (%)" >= 0 then 'Positive' else 'Negative' end as colour_buyer,

case when "seller change (%)" >= 0 then 'Positive' else 'Negative' end as colour_seller

from optimism.core.ez_nft_sales

where event_type = 'sale'

group by 1