select date_trunc('month', hour) as date, avg(price) as sol_usd

from ethereum.core.fact_hourly_token_prices

where date >= '2022-01-01' and token_address = '0xd31a59c85ae9d8edefec411d448f90841571b89c'

group by 1

select p.date,

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

avg(sales_amount) * avg(sol_usd) as avg_sales_price_usd,

count(distinct tx_id) as sales_count,

count(distinct purchaser) as unique_buyer,

count(distinct seller) as unique_seller,

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 (%)",

'Magic Eden' as blockchain

from solana.core.fact_nft_sales s join sol_price p on date_trunc('month', block_timestamp) = p.date

where date >= '2022-01-01' and succeeded = true and marketplace ilike '%magic eden%'

group by 1

union all

select date_trunc('month', block_timestamp) as date,