SELECT DISTINCT signers[0] AS user

FROM solana.core.fact_transactions

WHERE 1=1

-- AND tx_id='3H6vcnwGKyJgkALQy2oTnwk6TmJ3pXXip33vv5GzUa7y9C4NR55xYNBwronw19PAQbEcwqS5hJ4KpRmD6uSqHQrG'

AND log_messages[1]='Program log: Instruction: SellNftForBond'

AND instructions[0]:"programId"='8NvmvWEAw89HnE8Gjo4kHhbHy2PGyeuU9y9QJ1HKiLoR'

AND block_timestamp > CURRENT_DATE - INTERVAL '1 month'

GROUP BY 1

),

temp AS (

SELECT s.*,

CASE

WHEN s.first_program_id = '8NvmvWEAw89HnE8Gjo4kHhbHy2PGyeuU9y9QJ1HKiLoR' THEN 'hades_first'

WHEN '8NvmvWEAw89HnE8Gjo4kHhbHy2PGyeuU9y9QJ1HKiLoR' IN (programs_used[0], programs_used[1], programs_used[2], programs_used[3], programs_used[4]) THEN 'hades initially'

-- WHEN '8NvmvWEAw89HnE8Gjo4kHhbHy2PGyeuU9y9QJ1HKiLoR' IN ARRAY_SLICE(programs_used, CARDINALITY(programs_used)-1, 5) THEN 'hades initially'

ELSE 'other'

END AS typee

FROM users u

INNER JOIN solana.core.ez_signers s ON u.user = s.signer

SELECT typee, COUNT(signer) AS number_of_users, avg(num_days_active) as avg_number_of_active_days,

avg(num_txs) as avg_number_of_txns FROM temp GROUP BY typee;