count(A.tx_id) as tx_count,

row_number() over (partition by signers order by max(A.block_timestamp)) as rank,

CASE

WHEN pre_balances[0]/1e9 < 3 THEN '~100_USD_Wallet' -- less than 100 USD

WHEN pre_balances[0]/1e9 < 26 THEN 'Dolphin_Wallet' -- less than 1k more than 100 USD

WHEN pre_balances[0]/1e9 < 260 THEN 'Whale_Wallet' -- less than 10k more than 1k

WHEN pre_balances[0]/1e9 > 260 THEN 'Big_Whale_Wallet'

WHEN pre_balances[0]/1e9 > 2600 THEN 'Over 100k USD wallet'

END AS Wallet_Type,

CASE

WHEN post_balances[0]/1e9-pre_balances[0]/1e9 > 0 THEN 'Withdrawal_Wallet'

WHEN post_balances[0]/1e9-pre_balances[0]/1e9 < 0 THEN 'Deposit_Wallet'

WHEN post_balances[0]/1e9-pre_balances[0]/1e9 < 0 THEN 'Neutral_Wallet'

END AS Wallet_transaction

from solana.core.fact_transactions A

left join solana.core.fact_events B

on A.tx_id=B.tx_id

where program_id ilike 'So1endDq2YkqhipRh3WViPa8hdiSpxWy6z3Z6tMCpAo'

and A.block_timestamp > '2022-07-01'

and A.succeeded = 'True'

group by 1,4,5

QUALIFY rank = 1)

Wallet_type,

tx_count,

signers,

From Wallets

group by 1,2,3,4