select

recorded_hour::date as day,

avg(close) as flow_price

from flow.core.fact_hourly_prices

where token ilike 'Flow'

and provider ='coinmarketcap'

group by 1)

select

distinct player,

count (distinct tx_id) as sales_Count,

sum (case when currency in ('A.1654653399040a61.FlowToken','A.ead892083b3e2c6c.FlowUtilityToken') then price * flow_price else price end) as sales_volume

from flow.core.ez_nft_sales t1 join flow.core.dim_topshot_metadata t3 on t1.nft_id = t3.nft_Id

join price t2 on t1.block_timestamp::date = t2.day

where tx_succeeded = 'TRUE'

and Block_timestamp < '2023-01-01'

group by 1

order by 3 DESC

limit 10