SELECT

mint,

MIN(block_timestamp) AS launch_time

FROM solana.defi.fact_token_mint_actions

WHERE succeeded = true

AND block_timestamp >= DATEADD('day', -30, CURRENT_TIMESTAMP())

GROUP BY mint

),

suspicious_events AS (

-- Looking for large balance changes that might indicate a rug pull

SELECT

mint,

MIN(block_timestamp) AS suspicious_time

FROM solana.core.fact_token_balances

WHERE succeeded = true

AND (balance - pre_balance) / NULLIF(pre_balance, 0) < -0.9 -- 90% or more reduction

AND block_timestamp >= DATEADD('day', -30, CURRENT_TIMESTAMP())

GROUP BY mint

),

rug_time as (

tl.mint as token_address,

tl.launch_time,

se.suspicious_time as potential_rug_time,

DATEDIFF('minute', tl.launch_time, se.suspicious_time) AS time_to_suspicious_event_minutes,

CASE

WHEN DATEDIFF('minute', tl.launch_time, se.suspicious_time) < 60 THEN 'within 1 hour'

WHEN DATEDIFF('minute', tl.launch_time, se.suspicious_time) < 1440 THEN 'within 1 day'

ELSE 'more than 1 day'

END AS time_category

FROM token_launch tl

JOIN suspicious_events se ON tl.mint = se.mint

WHERE se.suspicious_time > tl.launch_time -- ensure suspicious activity happened after launch