kaibladeAverage Gas Used per Vote (Past 90 Days) copy
    Updated 2023-03-22
    -- forked from 09229667-f62e-496d-a0bf-450f83eaa6b5

    -- forked from 1aa6d4a0-5ab7-44f1-87aa-9fa18ba46e4f

    -- forked from c581e13e-d80b-4fa6-9256-3224aa436f70

    WITH delegating_tx AS
    (SELECT DISTINCT(decoded_log:delegatee) AS delegate
    FROM ethereum.core.ez_decoded_event_logs
    WHERE contract_address IN ( LOWER('0x4da27a545c0c5B758a6BA100e3a049001de870f5'), LOWER('0x7Fc66500c84A76Ad7e9c93437bFc5Ac33E2DDaE9'))
    AND event_name = 'DelegateChanged'
    AND block_timestamp::date >= CURRENT_DATE() - INTERVAL '180 days'
    AND decoded_log:delegationType =1
    AND tx_status = 'SUCCESS'),

    delegates_votes AS
    (SELECT tx_hash, block_timestamp, contract_address, contract_name,
    decoded_log, event_name,origin_from_address, origin_to_address
    FROM ethereum.core.ez_decoded_event_logs
    WHERE contract_address = '0xec568fffba86c094cf06b22134b23074dfe2252c'
    AND event_name = 'VoteEmitted'
    AND origin_from_address IN (SELECT delegate FROM delegating_tx)
    AND block_timestamp::date >= CURRENT_DATE() - INTERVAL '180 days'
    AND tx_status = 'SUCCESS'),

    voteswithgas AS
    (SELECT votes.*, gas.gas_used
    FROM delegates_votes votes
    JOIN ethereum.core.fact_transactions gas
    ON votes.tx_hash = gas.tx_hash
    ),

    Address_ens AS
    (SELECT block_timestamp, tx_hash, decoded_log:owner AS owner, decoded_log:name AS address_name
    FROM ethereum.core.ez_decoded_event_logs
    WHERE contract_address = LOWER('0x283Af0B28c62C092C9727F1Ee09c02CA627EB7F5')