select s.*,

p.price_usd,

total_sales_amount*p.price_usd as total_sales_amount_usd,

case array_size(split(nft_asset_name, ' '))

when 4 then lower(concat_ws('-',split_part(nft_asset_name, ' ', 1),split_part(nft_asset_name, ' ', 2), split_part(nft_asset_name, ' ', 3)))

when 3 then lower(concat_ws('-',split_part(nft_asset_name, ' ', 1),split_part(nft_asset_name, ' ', 2)))

when 2 then lower(split_part(nft_asset_name, ' ', 1))

when 1 then nft_asset_name

end as alternate_name,

upper(nvl(collection_name, alternate_name))as collection

from algorand.nft.ez_nft_sales s

left join algorand.defi.ez_price_pool_balances p

on date_trunc('hour', s.block_timestamp) = p.block_hour

where s.block_timestamp > CURRENT_DATE - 1 -{{Last_number_of_days}}

),

top_10_sales as (

select nft_asset_name,

nft_marketplace,

total_sales_amount as sale_price,

total_sales_amount_usd as sale_price_usd,

purchaser,

row_number () over (partition by nft_marketplace order by sale_price desc) as rank,

nft_url

from nft_sales_timeframe

where nft_marketplace IN ('rand gallery', 'algoxnft', 'shufl', 'ab2 gallery', 'octorand')

qualify rank <= 10

order by nft_marketplace, rank

select * from top_10_sales where nft_marketplace IN ('rand gallery', 'algoxnft', 'shufl')