scottincryptoAave Governance Gas Rebate Model
Updated 2023-05-14
999
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
31
32
33
34
35
36
›
⌄
WITH votes AS
(
SELECT
BLOCK_TIMESTAMP,
PROPOSAL_ID,
SUPPORT,
VOTER,
TX_HASH,
VOTING_POWER::float / 1e18 as voting_power
FROM ethereum.aave.ez_votes
where block_timestamp > DATEADD(day, -{{history_window}}, current_date)
)
, vote_tx as (
select
tx_hash
, count(proposal_id) as proposal_count
from votes
group by tx_hash
)
, votes_with_gas as (
select
v.tx_hash
, v.proposal_count
, t.gas_used
, t.gas_used / v.proposal_count as vote_gas_cost
from vote_tx v left JOIN (select * from ethereum.core.fact_transactions where block_timestamp > DATEADD(day, -{{history_window}}, current_date)) t ON v.TX_HASH = t.TX_HASH
-- order by vote_gas_cost desc
)
, gas_costs as (
select
median(vote_gas_cost) as med_gas_cost
, avg(vote_gas_cost) as avg_gas_cost
, min(vote_gas_cost) as min_gas_cost
Run a query to Download Data