Mrftiunstakers analysis copy
    Updated 2025-04-09
    -- forked from unstakers analysis @ https://flipsidecrypto.xyz/studio/queries/8d4e9020-9961-40f4-bcde-b1f3f0a33d73


    with usertbl as
    (WITH database AS (
    SELECT
    a.BLOCK_TIMESTAMP,
    a.TX_HASH,
    a.ORIGIN_FROM_ADDRESS as "Unstaker",
    CAST(ethereum.public.udf_hex_to_int(RIGHT(a.data, 64)) AS decimal) / 1e18 AS "$shMON Burned",
    b.tx_fee as "Fee ($MON)"
    FROM
    monad.testnet.fact_event_logs a
    join
    monad.testnet.fact_transactions b on a.tx_hash = b.tx_hash

    where a.CONTRACT_ADDRESS = lower ('0x3a98250f98dd388c211206983453837c8365bdc1')
    and a.ORIGIN_TO_ADDRESS = lower ('0x3a98250f98dd388c211206983453837c8365bdc1')
    and a.ORIGIN_FUNCTION_SIGNATURE = '0xba087652' --redeem
    and a.TX_SUCCEEDED = 'TRUE'
    and topic_3 is null
    )

    select
    "Unstaker",
    sum ("$shMON Burned") as "Total $shMON Burned",
    count (distinct TX_HASH) as "Unstake tx"
    from database
    group by 1
    )

    SELECT
    case
    when "Total $shMON Burned" < 0.1 then 'Tier1: Less Than 0.1 $shMON Burned'
    when "Total $shMON Burned" >= 0.1 and "Total $shMON Burned" < 0.5 then 'Tier2: 0.1-0.5 $shMON Burned'
    when "Total $shMON Burned" >= 0.5 and "Total $shMON Burned" < 1 then 'Tier3: 0.5-1 $shMON Burned'