select

count(CONTRACT_NAME) as numer_of_transactions,

CONTRACT_NAME as name_of_collection

FROM flow.core.fact_nft_sales a

full join flow.core.dim_contract_labels b

on a.NFT_COLLECTION=b.EVENT_CONTRACT

WHERE

block_timestamp >= '2022-05-9'

group by CONTRACT_NAME

order by numer_of_transactions desc

limit 10),

t2 as

(select

sum(PRICE) as volume,

CONTRACT_NAME as name_of_collection

FROM flow.core.fact_nft_sales a

full join flow.core.dim_contract_labels b

on a.NFT_COLLECTION=b.EVENT_CONTRACT

WHERE

block_timestamp >= '2022-05-9'

group by CONTRACT_NAME

order by volume desc),

t3 as( select * from t2

where volume<10000000

limit 10

)

select *

from t1 a

inner join t3 b

on a.NAME_OF_COLLECTION=b.NAME_OF_COLLECTION