SELECT date(hour) as day, avg(price) AS avg_price

FROM flipside_prod_db.ethereum_core.fact_hourly_token_prices

WHERE token_address = lower('0xD31a59c85aE9D8edEFeC411D448f90841571b89c')

AND date(hour) >= '2022-01-01'

GROUP BY 1

)

select

date(a.block_timestamp) as date,

count(distinct tx_id) as no_of_txns,

count(distinct purchaser) as no_of_users,

--project_name,

round(sum(sales_amount),2) as tot_volume,

round(sum(sales_amount * avg_price),2) as tot_volume_usd

from solana.core.fact_nft_sales a join price b on date(a.block_timestamp) = b.day

inner join solana.core.dim_nft_metadata c on a.MINT = c.MINT

where program_id in ('M2mx93ekt1fmXSVkTrUL9xVFHkmME8HTUi5Cyc5aF7K','MEisE1HzehtrDpAAT8PnLHjpSSkRYakotTuJRPjTpo8')

and marketplace like 'magic eden%'

and a.block_timestamp >= '2022-01-01'

group by 1--,2