flipside

    Testing again
    datavortexTesting again
    Updated 2024-12-04
    /*
    SELECT DISTINCT
    tx_id,
    ATTRIBUTE_VALUE AS validator
    FROM sei.core.fact_msg_attributes
    WHERE ATTRIBUTE_KEY = 'validator'
    AND msg_type = 'delegate';
    */
    /*
    WITH filtered_data AS (
    -- Step 1: Filter relevant rows early to reduce dataset size
    SELECT *
    FROM sei.core.fact_msg_attributes
    WHERE msg_type = 'delegate'
    AND ATTRIBUTE_KEY IN ('validator', 'amount')
    ),
    tx_validators AS (
    -- Step 2: Get distinct tx_id and validator mapping
    SELECT DISTINCT
    tx_id,
    ATTRIBUTE_VALUE AS validator
    FROM filtered_data
    WHERE ATTRIBUTE_KEY = 'validator'
    ),
    validator_amounts AS (
    -- Step 3: Get amounts, converting from macro SEI to SEI
    SELECT
    tx_id,
    CAST(REPLACE(ATTRIBUTE_VALUE, 'usei', '') AS DOUBLE) / 1e6 AS amount_in_sei
    FROM filtered_data
    WHERE ATTRIBUTE_KEY = 'amount'
    )
    -- Step 4: Join the data and aggregate the amounts by validator
    SELECT
    tv.validator,
    SUM(va.amount_in_sei) AS total_amount_in_sei
    QueryRunArchived: QueryRun has been archived