select TIMESTAMP::date as day, avg (price_usd) as flowprice

from flow.core.fact_prices

where symbol ='FLOW' and source ='coinmarketcap'

group by 1),

topwallets as (

select payer,

case when payer ='0xecfad18ba9582d4f' then 'JoyRide'

when payer ='0x18eb4ee6b3c026d2' then 'Dapper (Mainly Used For NBA TopShot)'

when payer ='0x55ad22f01ef568a1' then 'Blocto'

when payer= '0x1b65c33d7a352c61' then 'Cricket Moments'

when payer= '0x6f649aee955bef6d' then 'RCRDSHP'

when payer= '0x94bb84386cfc3b5d' then 'Everbloom'

when payer= '0x9b00972a3ecb364b' then 'Seussibles'

when payer = '0x93615d25d14fa337' then 'ChainMonsters'

when payer = '0x8234007b36f8113c' then 'Monsoon'

when payer = '0x4bbff461fa8f6192' then 'Fantastec'

when payer = '0x39e42c67cc851cfb' then 'lilico'

else null end as Payer_Name,

from flow.core.fact_transactions t1 join flow.core.fact_events t2 on t1.tx_id = t2.tx_id

where EVENT_TYPE='TokensWithdrawn'

and t1.block_timestamp >= CURRENT_DATE - 30

and t1.TX_SUCCEEDED ilike 'true'

and payer_name is not null),

maintable as (

select payer_name,

substr(nft_collection,20) as "NFT Project Title",

count (distinct tx_id) as Sales_Count,

count (distinct buyer) as Buyers_Count,

sum (case when t1.currency ='A.1654653399040a61.FlowToken' then t1.price * t3.flowprice else t1.price end) as USD_Volume

from flow.core.ez_nft_sales t1 join topwallets t2 on t1.buyer = t2.proposer

join flowpricet t3 on t1.block_timestamp::date = t3.day