select token, date_trunc('hour', timestamp) as hora, avg (price_usd) as token_price

from flow.core.fact_prices

where token like 'Flow'

and hora >= '2022-05-01'

group by 1, 2

order by 2

),

txs as (

select date_trunc('hour', block_timestamp) as hora,

nft_collection,

case currency

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

else 'USD'

end as nomination,

count (distinct tx_id) as hourly_sales,

sum (price) as hourly_volume

from flow.core.fact_nft_sales

where hora >= '2022-05-01'

and tx_succeeded = TRUE

group by 1,2,3

order by 1 desc

),

USD as (

select t.hora,

t.nft_collection,

hourly_sales,

case

when t.nomination = 'Flow' then p.token_price * t.hourly_volume

else t.hourly_volume

end as hourly_volume_USD

from txs t

left join price p

on t.hora = p.hora