-- Get the first minting transaction for each user

SELECT

tx.sender AS user,

MIN(tx.block_timestamp) AS first_mint_date

FROM aptos.core.fact_events fe

JOIN aptos.core.fact_transactions tx

ON fe.tx_hash = tx.tx_hash

WHERE fe.event_resource = 'MintEvent'

AND fe.event_module = 'stapt_token'

AND fe.event_data:"amount" IS NOT NULL

AND fe.event_address = '0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a'

AND fe.success = TRUE

GROUP BY 1

),

all_mints AS (

-- Get all minting transactions and their details

SELECT

tx.sender AS user,

DATE_TRUNC('week', fe.block_timestamp) AS week,

fe.block_timestamp,

CAST(fe.event_data:"amount" AS DOUBLE) / 1e8 AS mint_amount

FROM aptos.core.fact_events fe

JOIN aptos.core.fact_transactions tx

ON fe.tx_hash = tx.tx_hash

WHERE fe.event_resource = 'MintEvent'

AND fe.event_address = '0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a'

AND fe.event_module = 'stapt_token'

AND fe.event_data:"amount" IS NOT NULL

),

user_mint_analysis AS (

-- Identify first-time and returning users for each minting transaction

SELECT

am.week,

am.user,

am.block_timestamp,