Which pools are wallets voting for with their locked tokens most often? For more information on Velodrome
princefarzamWhich pools are wallets voting for with their locked tokens most often? For more information on Velodrome
Updated 2022-07-17
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
›
⌄
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