SELECT

a.block_timestamp,

a.purchaser,

SUBSTR(b.address_name, POSITION('#' IN b.address_name) + 1) AS nft_id,

a.mint,

a.sales_amount,

b.label

FROM solana.nft.fact_nft_sales a

JOIN solana.core.dim_labels b

ON a.mint = b.address

WHERE a.block_timestamp > '2023-02-01'

AND b.label in ('mad lads')

-- AND b.label in ('mad lads')

AND a.succeeded

),

first_purchase_date AS (

SELECT

purchaser,

label,

MIN(DATE_TRUNC('DAY', block_timestamp)) AS first_purchase_date

FROM mad_buying

GROUP BY purchaser, label

),

purchaser_status AS (

SELECT

DATE_TRUNC('DAY', mb.block_timestamp) AS purchase_date,

mb.label,

mb.purchaser,

CASE

WHEN fp.first_purchase_date = DATE_TRUNC('DAY', mb.block_timestamp) THEN 'new'

ELSE 'old'

END AS purchaser_type

FROM mad_buying mb

JOIN first_purchase_date fp

ON mb.purchaser = fp.purchaser