select

hour::date as date,

avg(price) as token_price_usd

from flow.price.ez_prices_hourly

where symbol = 'FLOW'

group by 1

),

txns as (

select

tx_id,

block_timestamp::date as date,

seller,

buyer,

case currency

when 'A.ead892083b3e2c6c.DapperUtilityCoin' then 'USDC'

when 'A.1654653399040a61.FlowToken' then 'FLOW'

when 'A.3c5959b568896393.FUSD' then 'FUSD'

when 'A.ead892083b3e2c6c.FlowUtilityToken' then 'FLOW'

when 'A.d01e482eb680ec9f.REVV' then 'REVV'

end as symbol,

iff(symbol = 'FLOW', token_price_usd, 1) * price as price_usd

from flow.nft.ez_nft_sales

left join pricet on block_timestamp::date = pricet.date

where tx_succeeded

),

buyer_activity as (

select

buyer,

count(tx_id) as txn_count

from txns

group by buyer

),