nft as (

SELECT

date_trunc('day', block_timestamp) as date,

nft_collection,

tx_id,

nft_id,

buyer

FROM flow.nft.ez_nft_sales

WHERE nft_collection IN ('A.0b2a3299cc857e29.TopShot', 'A.e4cf4bdc1751c65d.AllDay'

,'A.329feb3ab062d289.UFC_NFT', 'A.8b148183c28ff88f.Gaia'

,'A.87ca73a41bb50ad5.Golazos')

AND (date BETWEEN '2023-08-14' AND '2023-09-12'

OR date BETWEEN '2023-07-14' AND '2023-08-12')

),

periods as (

SELECT

CASE WHEN date BETWEEN '2023-08-14' AND '2023-09-12' THEN 'GALXE campaign'

WHEN date BETWEEN '2023-07-14' AND '2023-08-12' THEN 'common'

END AS periods,

CASE WHEN nft_collection = 'A.0b2a3299cc857e29.TopShot' THEN 'NBA TOP SHOT'

WHEN nft_collection = 'A.e4cf4bdc1751c65d.AllDay' THEN 'NFL ALL DAY'

WHEN nft_collection = 'A.329feb3ab062d289.UFC_NFT' THEN 'UFC Strike'

WHEN nft_collection = 'A.8b148183c28ff88f.Gaia' THEN 'Ballerz'

WHEN nft_collection = 'A.87ca73a41bb50ad5.Golazos' THEN 'LaLiga Golazos'

end as title,

count(DISTINCT tx_id) as num_sales

FROM nft

GROUP BY 1, 2

),

pivot as (

SELECT *

FROM periods

PIVOT(SUM(num_sales) FOR periods IN ('GALXE campaign', 'common'))