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 - 30

select nft_marketplace as source,

collection as target

--purchaser as target

from nft_sales_timeframe

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