polarityPremium and volume
    Updated 2025-02-13
    WITH raw_data AS (
    SELECT
    block_timestamp,
    tx_hash,
    origin_from_address,
    topics,
    data
    FROM berachain.testnet.fact_event_logs
    WHERE origin_to_address = LOWER('0x8B1A76B48a284047f3BF1Ee15723BBb54C49c63D')
    AND block_timestamp >= '2024-08-27'
    AND tx_succeeded = 'TRUE'
    )
    SELECT
    DATE(block_timestamp) AS date,
    SUM(utils.udf_hex_to_int(substr(data, 131, 64)) / pow(10, 18)) AS total_size_usd,
    SUM(utils.udf_hex_to_int(substr(data, 195, 64)) / pow(10, 18)) AS total_premium,
    SUM(SUM(utils.udf_hex_to_int(substr(data, 131, 64)) / pow(10, 18))) OVER (ORDER BY DATE(block_timestamp)) AS cumulative_size_usd,
    SUM(SUM(utils.udf_hex_to_int(substr(data, 195, 64)) / pow(10, 18))) OVER (ORDER BY DATE(block_timestamp)) AS cumulative_premium,
    FROM raw_data
    WHERE POSITION('0xe6ba045508353d28ffe727d7e1d54c14bb77665dd60636928b88674a9c7b4260' IN topics) > 0
    GROUP BY DATE(block_timestamp)
    ORDER BY date DESC

    QueryRunArchived: QueryRun has been archived