-- Query 1 to find the necessary wallets in the new_account_wallet column

SELECT

f.block_timestamp,

t.amount AS bonk_locked,

IFF(

f.inner_instructions[0]:instructions[0]:parsed:info:newAccount IS NULL

OR f.inner_instructions[0]:instructions[0]:parsed:info:newAccount = 'DjeYgErhMVinMR1jSuVtWy6K1hKkyoSmKgqyb9r8Jsnb',

f.inner_instructions[1]:instructions[0]:parsed:info:newAccount,

f.inner_instructions[0]:instructions[0]:parsed:info:newAccount

) AS new_account_wallet

FROM

solana.core.fact_transactions f

INNER JOIN solana.core.fact_transfers t

ON f.tx_id = t.tx_id

WHERE

f.block_timestamp >= '2024-01-01'

AND t.mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263'

AND t.tx_from = '{{Address}}'

AND t.tx_to = '9AdEE8AAm1XgJrPEs4zkTPozr3o4U5iGbgvPwkNdLDJ3'

ORDER BY block_timestamp DESC

),

rewards_totals AS (

-- Calculate the sum of distinct rewards_claimed for each wallet

SELECT

COALESCE(f.instructions[1]:accounts[2], f.instructions[3]:accounts[2],f.instructions[2]:accounts[2]) AS new_account_wallet,

SUM(DISTINCT t.amount) AS total_rewards_claimed

FROM

solana.core.fact_transactions f

INNER JOIN solana.core.fact_transfers t

ON f.tx_id = t.tx_id

WHERE

t.block_timestamp >= '2024-01-01'

AND t.mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263'

AND t.tx_to = '{{Address}}'

AND t.tx_from IN ('9AdEE8AAm1XgJrPEs4zkTPozr3o4U5iGbgvPwkNdLDJ3', '4hX8YQesSk5JmRNrMXMgXyzbH6L4HG6y7Ujd8v1JH1G2')