hour::date as date,

avg(price) as price_usd

from flow.price.ez_prices_hourly

where symbol = 'FLOW'

group by 1

),

main as (

tx_id,

block_timestamp,

seller,

buyer,

nft_collection,

case marketplace

when 'A.c1e4f4f4c4257510.Market' then 'TopShot'

when 'Gigantik Primary Market' then 'Gigantik'

when 'A.921ea449dffec68a.FlovatarMarketplace' then 'Flovatar'

when 'A.a49cc0ee46c54bfb.MotoGPNFTStorefront' then 'MotoGP'

when 'A.097bafa4e0b48eef.FindMarketSale' then 'Find Market'

when 'A.62b3063fbe672fc8.ZeedzMarketplace' then 'Zeedz'

when 'A.09e03b1f871b3513.TheFabricantMarketplace' then 'Fabricant'

when 'A.85b075e08d13f697.OlympicPinMarket' then 'Olympic Pin Market'

when 'A.5b82f21c0edf76e3.StarlyCardMarket' then 'Starly Card Market'

when 'A.64f83c60989ce555.ChainmonstersMarketplace' then 'Chainmonsters'

when 'A.4eb8a10cb9f87357.NFTStorefront' then 'Flowverse'

when 'A.4eb8a10cb9f87357.NFTStorefrontV2' then 'Flowverse'

when 'A.2162bbe13ade251e.MatrixMarketOpenOffer' then 'Matrix Market'

when 'A.c1e4f4f4c4257510.TopShotMarketV3' then 'TopShot'

when 'A.30cf5dcf6ea8d379.AeraPack' then 'AeraPack'

when 'A.489fcc527edc21cf.TuneGOMarket' then 'TuneGoMarket'

when 'A.82ed1b9cba5bb1b3.KaratNFTMarket' then 'Karat Market'

when 'A.c8c340cebd11f690.DarkCountryMarket' then 'Dark Country'

when 'A.8b148183c28ff88f.GaiaOrder' then 'Gaia Order'