with claim_txs as (

select

block_timestamp

, tx_id

, signers[0] as fee_payer

, instruction :accounts[4]::string as claimant

, utils.udf_hex_to_int(to_char(reverse(to_binary(substr(utils.udf_base58_to_hex(instruction :data), 19, 16))))) / pow(10, 6) as amount

from solana.core.fact_events

where succeeded

and fact_events.program_id = 'disGCfSiJKFigEphfou4PGHn1rukMfbs9cg9GpTM6oe'

and substr(utils.udf_base58_to_hex(fact_events.instruction :data), 3, 16) = '4eb1627bd215bb53' -- New Claim

and block_timestamp >= '2024-12-10 14:05:13.000'

trunc(BLOCK_TIMESTAMP, 'hour') as date,

count(*) as n_claims,

count(DISTINCT claimant) as n_claimants,

sum(amount) as claimed_amt

FROM claim_txs

GROUP by 1

ORDER by 1 DESC