from flow.core.ez_nft_sales

where nft_collection='A.e4cf4bdc1751c65d.AllDay' and tx_succeeded='TRUE'

order by 1),

tab2 as (select nft_id, moment_tier, player, team

from flow.core.dim_allday_metadata)

select date, buyer, seller, currency, marketplace, tab1.nft_id as nft_id, price, tx_id, moment_tier, player, team

from tab1 left join tab2 on tab1.nft_id=tab2.nft_id

order by 1),

table2 as (select date_trunc('day',timestamp) as date, avg(price_usd) as avg_price, token_contract

from flow.core.fact_prices

group by 1,3

order by 1)

select table1.date as date, buyer, seller, currency, marketplace, nft_id, price, price*avg_price as price_usd, case

when price_usd is null then price

else price_usd

end as nft_price_usd,

tx_id, moment_tier, player, team

from table1 left join table2 on table1.date=table2.date and table1.currency=table2.token_contract

order by 1)

select player, sum(nft_price_usd) as volume_usd, count(distinct tx_id) as "Sales Count"

from table3

where date>='2022-11-01' and moment_tier is not null

group by 1

order by 3 desc

limit 20