SELECT

tx.TX_HASH,

tx.block_timestamp as block_timestamp,

tr.to_Address as claimer,

RAW_AMOUNT/pow(10,18) as amount

FROM

blast.core.fact_transactions tx

INNER join blast.core.fact_token_transfers tr on tx.tx_hash = tr.tx_hash

and tr.contract_address = lower('0xb1a5700fA2358173Fe465e6eA4Ff52E36e88E2ad')

and tx.to_Address = '0xf7be503166828fe8565c520d66645ac6a06bbdd7'

and tx.ORIGIN_FUNCTION_SIGNATURE != '0xca1d209d'

WHERE

tx.status = 'SUCCESS'

,

get_volume_per_user as (

select

claimer,

sum (amount) as total_volume

from all_claims

group by 1

, get_cumulative_count as (

select

* , sum(total_volume) over(order by total_volume desc) as cumulative_count

from get_volume_per_user

, get_pct_of_cumulative_count as(

select

*

, cumulative_count / max(cumulative_count) over() as pct_of_cumu_count

, total_volume / max(cumulative_count) over() as pct_of_cumu_amt

from get_cumulative_count