txs as (

select

date_trunc('day',BLOCK_TIMESTAMP) as date,

instructions

from

solana.core.fact_transactions

where

succeeded = true

-- array_contains(account_keys, 'vBoNdEvzMrSai7is21XgVYik65mqtaKXuSdMBJ1xkW4'::variant)

AND BLOCK_TIMESTAMP > date_trunc('month', dateadd('month', -2, CURRENT_DATE))

AND BLOCK_TIMESTAMP < date_trunc('month', dateadd('month', -1, CURRENT_DATE))

and CAST(LOG_MESSAGES AS STRING) LIKE '%ClaimSettlement%'

)

date,

sum(utils.udf_hex_to_int(concat(

substring(utils.udf_base58_to_hex(ixs.value:data), length(utils.udf_base58_to_hex(ixs.value:data)) - 2 * (8 + 8) + (2 * 7) + 1, 2),

substring(utils.udf_base58_to_hex(ixs.value:data), length(utils.udf_base58_to_hex(ixs.value:data)) - 2 * (8 + 8) + (2 * 6) + 1, 2),

substring(utils.udf_base58_to_hex(ixs.value:data), length(utils.udf_base58_to_hex(ixs.value:data)) - 2 * (8 + 8) + (2 * 5) + 1, 2),

substring(utils.udf_base58_to_hex(ixs.value:data), length(utils.udf_base58_to_hex(ixs.value:data)) - 2 * (8 + 8) + (2 * 4) + 1, 2),

substring(utils.udf_base58_to_hex(ixs.value:data), length(utils.udf_base58_to_hex(ixs.value:data)) - 2 * (8 + 8) + (2 * 3) + 1, 2),

substring(utils.udf_base58_to_hex(ixs.value:data), length(utils.udf_base58_to_hex(ixs.value:data)) - 2 * (8 + 8) + (2 * 2) + 1, 2),

substring(utils.udf_base58_to_hex(ixs.value:data), length(utils.udf_base58_to_hex(ixs.value:data)) - 2 * (8 + 8) + (2 * 1) + 1, 2),

substring(utils.udf_base58_to_hex(ixs.value:data), length(utils.udf_base58_to_hex(ixs.value:data)) - 2 * (8 + 8) + 1, 2)

)) / 1e9)

txs,

LATERAL FLATTEN(input => instructions) ixs,

WHERE 1=1

and ixs.value:programId = 'vBoNdEvzMrSai7is21XgVYik65mqtaKXuSdMBJ1xkW4'

and '89SrbjbuNyqSqAALKBsKBqMSh463eLvzS4iVWCeArBgB' = utils.udf_hex_to_base58(concat('0x', substring(utils.udf_base58_to_hex(ixs.value:data), length(utils.udf_base58_to_hex(ixs.value:data)) - 2 * (8 + 8 + 32) + 1, 2 * 32)))

group by date

order by date