flipside

    Grouping txns
    Afonso_DiazGrouping txns
    Updated 2025-03-03
    with

    main as (
    select
    tx_id,
    block_timestamp,
    signers[0] as user,
    --credited to @pine
    iff(tx_id in (select distinct tx_id from solana.core.fact_events b where a.tx_id = b.tx_id and program_id = 'voTpe3tHQ7AjQHMapgSue2HJFAh2cGsdokqN3XqmVSj' and block_timestamp >= '2025-01-15'), 'Claim', 'Claim & Stake') as claim_type,
    inner_instruction['instructions'][1]['parsed']['info']['amount'] / 1e6 as amount
    from
    solana.core.fact_events a
    where
    program_id = 'DiS3nNjFVMieMgmiQFm6wgJL7nevk4NrhXKLbtEH1Z2R'
    and block_timestamp >= '2025-01-22'
    )

    select
    case
    when amount < 10 then 'a. Less than 10 $JUP'
    when amount <= 50 then 'b. 10 $JUP - 50 $JUP'
    when amount <= 100 then 'c. 50 $JUP - 100 $JUP'
    when amount <= 500 then 'd. 100 $JUP - 500 $JUP'
    when amount <= 1000 then 'e. 500 $JUP - 1000 $JUP'
    when amount <= 5000 then 'f. 1000 $JUP - 5000 $JUP'
    when amount <= 10000 then 'g. 5000 $JUP - 10,000 $JUP'
    when amount <= 100000 then 'h. 10,000 $JUP - 100,000 $JUP'
    else 'i. More than 100,000 $JUP'
    end as type,
    count(distinct tx_id) as claims
    from
    main
    group by 1
    order by 1

    Last run: about 2 months ago
    TYPE
    CLAIMS
    1
    		a. Less than 10 $JUP40
    2
    		b. 10 $JUP - 50 $JUP350390
    3
    		c. 50 $JUP - 100 $JUP88014
    4
    		d. 100 $JUP - 500 $JUP235443
    5
    		e. 500 $JUP - 1000 $JUP27116
    6
    		f. 1000 $JUP - 5000 $JUP53770
    7
    		g. 5000 $JUP - 10,000 $JUP8035
    8
    		h. 10,000 $JUP - 100,000 $JUP3780
    9
    		i. More than 100,000 $JUP96999
    9
    307B
    12s