date_trunc('hour', block_timestamp) as block_day, sum(price) as price, sum(price_usd) as price_usd, token_id

from ethereum.nft_events

where contract_address in ('0x059edd72cd353df5106d2b9cc5ab83a52287ac3a', '0xa7d8d9ef8d8ce8992df33d8b8cf4aebabd5bd270')

and event_type = 'mint' and block_day < '2021-08-28'

group by 1,4),

project as (select

token_metadata:collection_name::string as Project, token_id

from ethereum.nft_metadata

where contract_address in ('0x059edd72cd353df5106d2b9cc5ab83a52287ac3a', '0xa7d8d9ef8d8ce8992df33d8b8cf4aebabd5bd270')

and project = 'Geometry Runners by Rich Lord'

group by 1,2

),

total as (select A.block_day, sum(A.price) as Volume_ETH, sum(A.price_usd) as Volume_USD, B.project, count(DISTINCT B.token_id) as Token_count

from vol A

join project B on A.token_id = B.token_id

group by 1,4

order by 1 asc

select *, (Volume_ETH/Token_count) as Average_ETH_per_piece

from total