princefarzamWhich pools are wallets voting for with their locked tokens most often? For more information on Velodrome
    Updated 2022-07-17
    WITH Vote_txns AS (SELECT
    regexp_substr_all( SUBSTR(INPUT_DATA, 11, len(INPUT_DATA)), '.{64}' ) AS segmented_data,
    ethereum.public.udf_hex_to_int( segmented_data[0] :: STRING ) AS tokenID,
    ethereum.public.udf_hex_to_int( segmented_data[6] :: STRING ) AS Weight,
    CONCAT('0x', SUBSTR(INPUT_DATA :: STRING, 291, 40)) AS Pool_Contract,
    *
    FROM optimism.core.fact_transactions
    WHERE ORIGIN_FUNCTION_SIGNATURE='0x7ac09bf7'
    AND STATUS='SUCCESS')

    SELECT
    Pool_Contract,
    CASE
    WHEN Pool_Contract='0x47029bc8f5cbe3b464004e87ef9c9419a48018cd' THEN 'VolatileV1 AMM - OP/USDC'
    WHEN Pool_Contract='0xe8537b6ff1039cb9ed0b71713f697ddbadbb717d' THEN 'VolatileV1 AMM - VELO/USDC'
    WHEN Pool_Contract='0x79c912fef520be002c2b6e57ec4324e260f38e50' THEN 'VolatileV1 AMM - WETH/USDC'
    WHEN Pool_Contract='0xffd74ef185989bff8752c818a53a47fc45388f08' THEN 'VolatileV1 AMM - VELO/OP'
    WHEN Pool_Contract='0xd16232ad60188b68076a235c65d692090caba155' THEN 'StableV1 AMM - USDC/sUSD'
    WHEN Pool_Contract='0xfd7fddfc0a729ecf45fb6b12fa3b71a575e1966f' THEN 'StableV1 AMM - WETH/sETH'
    WHEN Pool_Contract='0x4f7ebc19844259386dbddb7b2eb759eefc6f8353' THEN 'StableV1 AMM - USDC/DAI'
    WHEN Pool_Contract='0x207addb05c548f262219f6bfc6e11c02d0f7fdbe' THEN 'StableV1 AMM - USDC/LUSD'
    WHEN Pool_Contract='0x9355292f66552ea5717b274d27eefc8254011d83' THEN 'VolatileV1 AMM - THALES/USDC'
    WHEN Pool_Contract='0xcdd41009e74bd1ae4f7b2eecf892e4bc718b9302' THEN 'VolatileV1 AMM - WETH/OP'
    END AS Pools,
    COUNT (DISTINCT TX_HASH) AS Number_of_votes
    FROM Vote_txns
    GROUP BY Pool_Contract
    ORDER BY Number_of_votes DESC
    LIMIT 10