SELECT {{Timespan}} AS INPUT_Timespan,

CASE

WHEN INPUT_Timespan = 'Last 30 days' THEN CURRENT_DATE - 30

WHEN INPUT_Timespan = 'Last 60 days' THEN CURRENT_DATE - 60

WHEN INPUT_Timespan = 'Last 90 days' THEN CURRENT_DATE - 90

WHEN INPUT_Timespan = 'Last 180 days' THEN CURRENT_DATE - 180

WHEN INPUT_Timespan = 'Last year' THEN CURRENT_DATE - 365

ELSE '2014-01-01'

END AS Timespan

),

Flow_NFT_Sales_With_Stablecoins AS (

SELECT TX_ID AS TX, BLOCK_TIMESTAMP, MARKETPLACE, NFT_ID, NFT_COLLECTION, CONCAT(NFT_COLLECTION, '#', NFT_ID) "Collection#NFT_ID",

BUYER, 1 AS "Number of sold NFTs" ,PRICE AS "Sales Price", 'Flow' AS Chain --SELLER

FROM flow.core.ez_nft_sales

WHERE CURRENCY NOT IN ('A.1654653399040a61.FlowToken', 'A.d01e482eb680ec9f.REVV') AND BLOCK_TIMESTAMP >= (SELECT Timespan FROM GET_INPUT_Timespan)

AND PRICE > 0 AND PRICE IS NOT NULL AND TX_SUCCEEDED = 'TRUE'

),

REVV_Flow_PRICE AS (

SELECT TIMESTAMP::DATE AS DAYS, TOKEN_CONTRACT, AVG(PRICE_USD) AS "Token Price"

FROM flow.core.fact_prices

WHERE TOKEN_CONTRACT IN ('A.1654653399040a61.FlowToken', 'A.d01e482eb680ec9f.REVV') AND DAYS >= (SELECT Timespan FROM GET_INPUT_Timespan)

GROUP BY DAYS, TOKEN_CONTRACT

),

Flow_NFT_Sales_With_Other_Tokens AS (

SELECT TX_ID AS TX, BLOCK_TIMESTAMP, MARKETPLACE, NFT_ID, NFT_COLLECTION, CONCAT(NFT_COLLECTION, '#', NFT_ID) "Collection#NFT_ID", BUYER,

1 AS "Number of sold NFTs" ,PRICE * "Token Price" AS "Sales Price" , 'Flow' AS Chain --SELLER

FROM flow.core.ez_nft_sales JOIN REVV_Flow_PRICE ON (BLOCK_TIMESTAMP::DATE = DAYS AND CURRENCY = TOKEN_CONTRACT)

WHERE CURRENCY IN ('A.1654653399040a61.FlowToken', 'A.d01e482eb680ec9f.REVV') AND BLOCK_TIMESTAMP >= (SELECT Timespan FROM GET_INPUT_Timespan)

AND PRICE > 0 AND PRICE IS NOT NULL AND TX_SUCCEEDED = 'TRUE'