with prices as (

select

recorded_hour::date as day,

avg(close) as price

from solana.core.ez_token_prices_hourly

where symbol ilike 'sol'

group by 1

),

base as (

select

block_timestamp,

tx_id,

seller,

purchaser,

sales_amount,

marketplace,

case when marketplace in ('magic eden v2','Magic Ededn') then 'Magic Eden'

else marketplace end as markets

from solana.core.fact_nft_sales

where block_timestamp >= '2023-01-01'

and succeeded = TRUE

select

block_timestamp::date as date,

count(distinct tx_id) as "Number Sold",

count(distinct seller) as "Number Seller",

count(distinct purchaser) as "Number Buyer",

sum(sales_amount) * price as "Amount USD",

markets

from base join prices

on block_timestamp::date = day

group by 1,6,price