dates AS (

SELECT DISTINCT DATE_TRUNC('day', block_timestamp) as date

FROM ethereum.core.ez_nft_sales

WHERE nft_address = lower('{{nft-contract}}') -- SHARI

AND block_timestamp BETWEEN '2023-02-27' AND current_date

),

early_buyers AS (

SELECT DISTINCT buyer_address, block_timestamp

FROM ethereum.core.ez_nft_sales

WHERE nft_address = lower('{{nft-contract}}') -- SHARI

AND block_timestamp < '2023-03-06'

),

later_holders AS (

SELECT DISTINCT buyer_address, block_timestamp

FROM ethereum.core.ez_nft_sales

WHERE nft_address = lower('{{nft-contract}}') -- SHARI

AND block_timestamp >= '2023-03-06'

AND price > 0

),

retention AS (

SELECT

dates.date AS cohort_date,

DATEDIFF('day', dates.date, early_buyers.block_timestamp) AS retention_day,

COUNT(DISTINCT early_buyers.buyer_address) AS cohort_size,

COUNT(DISTINCT CASE WHEN later_holders.buyer_address IS NOT NULL THEN early_buyers.buyer_address END) AS retained_users

FROM dates

JOIN early_buyers ON DATE_TRUNC('day', early_buyers.block_timestamp) = dates.date

LEFT JOIN later_holders

ON early_buyers.buyer_address = later_holders.buyer_address

AND DATE_TRUNC('day', later_holders.block_timestamp) = DATEADD('day', DATEDIFF('day', dates.date, early_buyers.block_timestamp), dates.date)

-- WHERE early_buyers.nft_address = lower('{{nft-contract}}') -- SHARI

GROUP BY 1, 2