flipside

    Categorizing transactions based on stacked volume
    Afonso_DiazCategorizing transactions based on stacked volume
    Updated 4 days ago
    with

    pricet as (
    select
    hour::date as date,
    avg(price) as token_price_usd
    from
    ronin.price.ez_prices_hourly
    where
    symbol = 'RON'
    group by 1
    ),


    main as (
    select
    tx_hash,
    block_timestamp,
    '0x' || right(topics[2], 40) as pool_id,
    origin_from_address as user,
    utils.udf_hex_to_int(data)::bigint / 1e18 as amount,
    amount * token_price_usd as amount_usd,
    case origin_function_signature
    when '0x4d99dd16' then 'Unstake'
    when '0x5c19a95c' then 'Stake'
    when '0x097e4a9d' then 'Reward Claimed'
    end as event_name
    from
    ronin.core.fact_event_logs
    left join
    pricet on block_timestamp::date = date
    where
    tx_succeeded
    and origin_to_address = '0x545edb750eb8769c868429be9586f5857a768758'
    and topics[0] in ('0xe5541a6b6103d4fa7e021ed54fad39c66f27a76bd13d374cf6240ae6bd0bb72b', '0x4d10bd049775c77bd7f255195afba5088028ecb3c7c277d393ccff7934f2f92c')
    and origin_function_signature in ('0x4d99dd16', '0x5c19a95c', '0x097e4a9d')
    Last run: 4 days ago
    TYPE
    TRANSACTIONS
    1
    		1,000 $RON - 5,000 $RON8634
    2
    		10 $RON - 50 $RON78626
    3
    		> 100,000 $RON115
    4
    		50 $RON - 100 $RON24689
    5
    		500 $RON - 1,000 $RON8609
    6
    		5,000 $RON - 10,000 $RON1427
    7
    		< 10 $RON78167
    8
    		100 $RON - 500 $RON31639
    9
    		10,000 $RON - 100,000 $RON1513
    9
    265B
    17s