select

date_trunc('hour',block_timestamp)as hourly,

sum(price) as total_volume

from flow.core.ez_nft_sales A

inner join flow.core.dim_allday_metadata B on A.nft_collection = B.nft_collection and A.nft_id = B.nft_id

where

hourly >= '2022-9-8'

and hourly <= '2022-9-14'

and TX_SUCCEEDED='TRUE'

group by 1

),

player_sales as (

select

date_trunc('hour',block_timestamp)as hourly,

avg(price) as avg_price,

sum(price) as total_volume_player

from flow.core.ez_nft_sales A inner join flow.core.dim_allday_metadata B on A.nft_collection = B.nft_collection and A.nft_id = B.nft_id

where

hourly >= '2022-9-8'

and hourly <= '2022-9-14'

and TX_SUCCEEDED='TRUE'

and player = '{{player}}'

group by 1

)

select

A.hourly,

A.avg_price,

B.total_volume,

(total_volume_player * 100/ B.total_volume) as percentage_sales

from player_sales A inner join total_sales B on A.hourly = B.hourly