,sum(chests)

FROM

(

SELECT

date_trunc('day',BLOCK_TIMESTAMP) as blocktime,

signers[0] as holder,

ROUND(SUM(

CASE WHEN pre_token_balances[0].uiTokenAmount.amount - post_token_balances[0].uiTokenAmount.amount >= 0

THEN pre_token_balances[0].uiTokenAmount.amount - post_token_balances[0].uiTokenAmount.amount

END

)) as chests

solana.core.fact_transactions

BLOCK_TIMESTAMP >= (CURRENT_DATE - interval '14 days')

--BLOCK_TIMESTAMP > CURRENT_DATE - interval '{{timeline}}'

AND array_contains(parse_json('{"pubkey":"ChEsTBFtT4PNEDTEfpvREFUoALMjaUhM5HyCh1jJnQn2","signer":false,"source":"transaction","writable":true}'),account_keys)

-- AND ARRAY_CONTAINS(TO_VARIANT('Program log: Instruction: Burn'),log_messages)

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

-- AND array_size(account_keys) > 4

--AND array_size(instructions) = 1

AND succeeded = True

GROUP BY

blocktime

, holder

HAVING chests > 0

group by blocktime