with bonding_phase as ( select distinct signers[0] as wallet from solana.core.fact_events events inner join solana.core.fact_transactions txs using(block_timestamp, tx_id, succeeded) inner join lateral flatten (input => log_messages) f where succeeded and block_timestamp > '2023-02-02' and block_timestamp < '2023-02-17' and program_id = '8NvmvWEAw89HnE8Gjo4kHhbHy2PGyeuU9y9QJ1HKiLoR' and f.value = 'Program log: Instruction: SellNftForBond' ), last_transfers as ( select distinct iff(bp.wallet is not null, 'Bonder', 'Not Bonder') as is_bonder, wallet, last_value(tx_id) over (partition by wallet order by block_timestamp, index) as tx_id from ( select tx_from as wallet, tx_id, block_timestamp, index from solana.core.fact_transfers transfers where mint = 'BWXrrYFhT7bMHmNBFoQFWdsSgA3yXoAnMhDK6Fn1eSEn' -- $HADES and transfers.block_timestamp < '2023-02-02 19:12:14.000' -- p2 first and transfers.block_timestamp > '2023-01-23' union all select tx_to as wallet, tx_id, block_timestamp, index from solana.core.fact_transfers transfers where mint = 'BWXrrYFhT7bMHmNBFoQFWdsSgA3yXoAnMhDK6Fn1eSEn' -- $HADES and transfers.block_timestamp < '2023-02-13 20:12:31.000' -- p2 first and transfers.block_timestamp > '2023-01-23' ) transfers left join bonding_phase bp using(wallet) where wallet not in ('9XhPR1egVMncx2dq7LZhAyYRUwfhoUKfAjNi58ReUx28', 'vestSg7WZWkTsusSsHWMPAT1aM4XMuvtx1H2QJk1HjM', 'FuXcjyY3EkoC8YxiM1XrivHfYKmtFeeUdKppjybdp22C', 'd4sH8ckL3iowTSin8gQaXtY9nQvdKCx2jimiV9qdndd', 'Fzx2MEY6fuwjoL1g4Mh5D6dcxp24HEcdZ1QsrVxE5sfs', 'AojtngqK2RoX5WXhnEfBmduZnGZfZYi8jMy69wzXKYPM', 'J9TtRc2AUnxBh3uA6uxqL1BBbm6qXEPfEFF9o2teeMzm') ), balances as ( select is_bonder, wallet, f.value :uiTokenAmount :amount ::int / pow(10,9) as amount from solana.core.fact_transactions txs inner join lateral flatten (input => post_token_balances) f inner join last_transfers lt on txs.tx_id = lt.tx_id where succeeded and block_timestamp > '2023-01-23' and block_timestamp < '2023-02-17' and f.value :mint = 'BWXrrYFhT7bMHmNBFoQFWdsSgA3yXoAnMhDK6Fn1eSEn' and f.value :owner = lt.wallet ), aggregated as ( select is_bonder, case when amount < 10 then floor(amount) when amount < pow(10,2) then floor(amount/10) * 10 when amount < pow(10,3) then floor(amount/pow(10,2)) * pow(10,2) when amount < pow(10,4) then floor(amount/pow(10,3)) * pow(10,3) when amount < pow(10,5) then floor(amount/pow(10,4)) * pow(10,4) when amount < pow(10,6) then floor(amount/pow(10,5)) * pow(10,5) when amount < pow(10,7) then floor(amount/pow(10,6)) * pow(10,6) else amount end as amount, count(distinct wallet) as wallets from balances group by 1,2 ) select * from aggregated order by is_bonder

Hadeswap | NFT Bonding and $HADES Emissions