CryptoIcicleOptimism-10.Velodrome Health - Most Voted Pools
Updated 2022-08-14
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
30
›
⌄
-- Velodrome Health
-- Q10. Create 3 dashboards on Velodrome with a description for each that showcase traction and success in a data-driven manner.
-- Most importantly, your insights should help our readers answer the question "How healthy is this project?"
-- Payout 150 USDC
-- Grand Prize 450 USDC
-- Payout Network Ethereum
-- Level Advanced
-- Difficulty Elite
with vote_txns as (
SELECT
regexp_substr_all(SUBSTR(input_data, 11, len(input_data)), '.{64}') AS segmented_data,
concat('0x',ltrim((
segmented_data [4] :: STRING
),'0')) AS pool_address,
*
from optimism.core.fact_transactions
where 1=1
-- and tx_hash = '0x516c3537d534415e5d00845b6b718d93d5f2066e1f9aa795931177b21b5bf5b0'
and origin_function_signature = '0x7ac09bf7' -- vote
and block_timestamp >= CURRENT_DATE - {{n_days}}
and pool_address <> '0x'
)
select
block_timestamp::date as date,
pool_address,
count(distinct tx_hash) as n_votes
from vote_txns
group by 1, 2
Run a query to Download Data