SELECT

buyer_address,

COUNT(*) as amt

FROM ethereum.core.ez_nft_sales

where block_timestamp::date >= '2022-08-01'

GROUP BY 1

), tab2 as (

SELECT

date(block_timestamp) as dates,

name as collection,

tokenid,

max(DATEDIFF(day, block_timestamp, CURRENT_DATE)) as date_diff,

count(*),

max(DATEDIFF(day, block_timestamp, CURRENT_DATE)) / count(*) as ratio

FROM flipside_prod_db.ethereum_core.ez_nft_sales

LEFT outer join flipside_prod_db.ethereum_core.dim_contracts

on address = nft_address

and block_timestamp::date >= '2022-08-01'

GROUP by 1,2,3

dates,

collection,

avg(ratio) as average_daily_turnover,

count(*) as sales

from tab2

where not ratio = 1

and not collection is NULL

group by 1,2

LIMIT 10