damidezMagic Eden - $ME Claim agg
    Updated 19 hours ago
    WITH meprice AS (
    SELECT
    trunc(hour, 'day') AS day,
    AVG(price) AS price
    FROM
    solana.price.ez_prices_hourly
    WHERE
    token_address = 'MEFNBXixkEbait3xn9bkm8WsJzXtVsaJEn4c8Sam21u'
    GROUP BY
    day
    ),
    claim_txs AS (
    SELECT
    block_timestamp,
    tx_id,
    signers[0] AS fee_payer,
    instruction :accounts[1] AS claimer,
    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,
    'veT' AS claim_program
    FROM solana.core.fact_events
    WHERE succeeded
    AND fact_events.program_id = 'veTbq5fF2HWYpgmkwjGKTYLVpY6miWYYmakML7R7LRf'
    AND substr(utils.udf_base58_to_hex(fact_events.instruction :data), 3, 16) = '0438e530aa0ffd05' -- Claim From Distribution
    AND block_timestamp >= '2024-12-10 14:05:13.000'

    UNION ALL

    SELECT
    block_timestamp,
    tx_id,
    signers[0] AS fee_payer,
    instruction :accounts[4] AS claimer,
    utils.udf_hex_to_int(
    to_char(reverse(to_binary(substr(utils.udf_base58_to_hex(instruction :data), 19, 16))))
    Last run: about 19 hours ago
    TOTAL_CLAIMING
    TOTAL_CLAIMER
    USD_VALUE
    AMOUNTS
    AVG_CLAIM
    MIN_CLAIM
    MAX_CLAIM
    INITIAL_CLAIM
    UNCLAIMED_AMOUNT
    UNCLAIMED_PERCENTAGE
    1
    216186183583541148959.481085773482503.1637547361.469166667803563.5588888891250000001642265213.1381216
    1
    116B
    125s