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'

select

count(DISTINCT claimant) as users,

sum(amount) as claimed_amt,

case

when amount <= 10 then 'a. <= 10'

when amount <= 100 then 'b. 10 ~ 100'

when amount <= 500 then 'c. 100 ~ 500'

when amount <= 1000 then 'd. 500 ~ 1K'

when amount <= 10000 then 'e. 1K ~ 10K'

when amount > 10000 then 'i. > 10K' end as

user_breakdown

from claim_txs

group by 3

order by 2 DESC