price, price_usd, token_id, tx_id

from ethereum.nft_events

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

and event_type = 'sale'

and block_timestamp < '2021-07-01'),

project as (select

token_metadata:collection_name::string as Project, token_id

from ethereum.nft_metadata

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

group by 1,2),

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

from vol A

join project B on A.token_id = B.token_id

group by 3

order by 1 desc

limit 25)

select *, (Volume_ETH/Tx_count) as Average_ETH_per_piece

from total

order by 2 desc