CASE WHEN NFT_COLLECTION = 'A.e4cf4bdc1751c65d.PackNFT' OR NFT_COLLECTION = 'A.329feb3ab062d289.NFL_NFT' THEN 'A.e4cf4bdc1751c65d.AllDay' -- NFL All Day Aggregation

WHEN NFT_COLLECTION = 'A.427ceada271aa0b1.SturdyTokens' THEN 'A.427ceada271aa0b1.SturdyItems' -- Sturdy Exchange Aggregation

WHEN NFT_COLLECTION = 'A.62b3063fbe672fc8.ZeedzMarketplace' OR NFT_COLLECTION = 'A.62b3063fbe672fc8.ZeedzDrops' OR NFT_COLLECTION = 'A.62b3063fbe672fc8.ZeedzINO' THEN 'A.62b3063fbe672fc8.ZeedzINO' -- Zeedz Aggregation

WHEN NFT_COLLECTION = 'A.bae9e93397796df2.CheezeNFT' OR NFT_COLLECTION = 'A.5a8fb12692f5a446.CheezeNFT' OR NFT_COLLECTION = 'A.5a8fb12692f5a446.CheezeVouchers' THEN 'A.5a8fb12692f5a446.CheezeVouchers' -- Cheeze Aggregation

WHEN NFT_COLLECTION IN ('A.12450e4bb3b7666e.Genies', 'A.f018642f0ebeca21.Genies', 'A.f018642f0ebeca21.GeniesShardedCollection', 'A.12450e4bb3b7666e.GeniesShardedCollection', 'A.9c2234b992667d22.GeniesShardedCollection') THEN 'A.12450e4bb3b7666e.Genies' -- Genies Aggregation

WHEN NFT_COLLECTION = 'A.f2af175e411dfff8.MetaPanda' THEN 'A.f2af175e411dfff8.MetaPandaAirdropNFT' -- Meta Panda Club Aggregation

WHEN NFT_COLLECTION IN ('A.9969d64233d69723.Blockletes_NFT', 'A.9969d64233d69723.BlockleteMarket_NFT', 'A.9969d64233d69723.BlockleteMarket_NFT_V2', 'A.329feb3ab062d289.BlockleteGames_NFT') THEN 'A.329feb3ab062d289.BlockleteGames_NFT' -- Blocklete Aggregation

WHEN NFT_COLLECTION IN ('A.329feb3ab062d289.AtlantaHawks_NFT', 'A.14c2f30a9e2e923f.AtlantaHawks_NFT', 'A.329feb3ab062d289.AtlantaNft_NFT') THEN 'A.329feb3ab062d289.AtlantaNft_NFT' -- Atlanta Hawks Aggregation

WHEN NFT_COLLECTION IN ('A.8529aaf64c168952.MonoCat', 'A.8529aaf64c168952.MonoPaymentMinter', 'A.8529aaf64c168952.MonoGold', 'A.8529aaf64c168952.MonoCatMysteryBox', 'A.8529aaf64c168952.MonoSilver') THEN 'A.8529aaf64c168952.MonoCatMysteryBox' -- MonoCat Aggregation

ELSE NFT_COLLECTION END AS NFT_COLLECTION

, CASE WHEN CURRENCY IN ('A.ead892083b3e2c6c.DapperUtilityCoin', 'A.4eded0de73020ca5.FazeUtilityCoin', 'A.3c5959b568896393.FUSD', 'A.b19436aae4d94622.FiatToken') THEN 'USD'

WHEN CURRENCY IN ('A.1654653399040a61.FlowToken', 'A.ead892083b3e2c6c.FlowUtilityToken') THEN 'FLOW'

ELSE CURRENCY END AS CURRENCY

, SUM(CASE When BLOCK_TIMESTAMP BETWEEN DATEADD(day, -60, GETDATE()) AND DATEADD(day, -30, GETDATE()) Then PRICE Else 0 End) AS volume_30_prev

, SUM(CASE When BLOCK_TIMESTAMP > DATEADD(day, -30, GETDATE()) Then PRICE Else 0 End ) AS volume_30

, (volume_30/NULLIF(SUM(CASE When BLOCK_TIMESTAMP BETWEEN DATEADD(day, -60, GETDATE()) AND DATEADD(day, -30, GETDATE()) Then PRICE Else 0 End),0)) -1 AS growth_30 -- want to return "-" instead of 0

, sum(CASE When BLOCK_TIMESTAMP > DATEADD(day, -30, GETDATE()) Then 1 Else 0 End ) AS sales_30

, CASE WHEN sales_30 > 0 THEN volume_30 / sales_30 ELSE 0 End AS avg_price_30

, count(DISTINCT (case when BLOCK_TIMESTAMP > DATEADD(day, -30, GETDATE()) > 0 then BUYER ELSE NULL End)) AS unique_buyers_30

, SUM(CASE When BLOCK_TIMESTAMP BETWEEN DATEADD(day, -14, GETDATE()) AND DATEADD(day, -7, GETDATE()) Then PRICE Else 0 End) AS volume_7_prev

, SUM(CASE When BLOCK_TIMESTAMP > DATEADD(day, -7, GETDATE()) Then PRICE Else 0 End ) AS volume_7

, (volume_7/NULLIF(SUM(CASE When BLOCK_TIMESTAMP BETWEEN DATEADD(day, -14, GETDATE()) AND DATEADD(day, -7, GETDATE()) Then PRICE Else 0 End),0)) -1 AS growth_7 -- want to return "-" instead of 0

, sum(CASE When BLOCK_TIMESTAMP > DATEADD(day, -7, GETDATE()) Then 1 Else 0 End ) AS sales_7

, CASE WHEN sales_7 > 0 THEN volume_7 / sales_7 ELSE 0 End AS avg_price_7

, count(DISTINCT (case when BLOCK_TIMESTAMP > DATEADD(day, -7, GETDATE()) > 0 then BUYER ELSE NULL End)) AS unique_buyers_7

, SUM(CASE When BLOCK_TIMESTAMP BETWEEN DATEADD(day, -2, GETDATE()) AND DATEADD(day, -1, GETDATE()) Then PRICE Else 0 End) AS volume_1_prev

, SUM(CASE When BLOCK_TIMESTAMP > DATEADD(day, -1, GETDATE()) Then PRICE Else 0 End ) AS volume_1

, (volume_1/NULLIF(SUM(CASE When BLOCK_TIMESTAMP BETWEEN DATEADD(day, -2, GETDATE()) AND DATEADD(day, -1, GETDATE()) Then PRICE Else 0 End),0)) -1 AS growth_1 -- want to return "-" instead of 0

, sum(CASE When BLOCK_TIMESTAMP > DATEADD(day, -1, GETDATE()) Then 1 Else 0 End ) AS sales_1

, CASE WHEN sales_1 > 0 THEN volume_1 / sales_1 ELSE 0 End AS avg_price_1

, count(DISTINCT (case when BLOCK_TIMESTAMP > DATEADD(day, -1, GETDATE()) > 0 then BUYER ELSE NULL End)) AS unique_buyers_1