0xtableauSafety Module Rewards
Updated 2022-11-28
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
31
32
33
34
35
36
›
⌄
with staked_aave as (
select date_trunc('day',block_timestamp) as "day",
contract_name,
TX_TO_LABEL_SUBTYPE,
sum(event_inputs:amount::float/1e18) as "amount"
from ethereum.events_emitted
where block_timestamp > getdate() - interval '3 months'
and upper(contract_address) = upper('0x4da27a545c0c5B758a6BA100e3a049001de870f5')
and event_name = 'RewardsAccrued'
and TX_SUCCEEDED = 'TRUE'
group by 1,2,3
order by 1 desc, 2 desc),
prices as (
select date_trunc('day',hour) as "day",
symbol,
round(avg(price),4) as Avg_Daily_Price
from ethereum.token_prices_hourly
where hour > getdate() - interval '3 months'
and symbol = 'AAVE'
group by 1, 2
order by 1 desc
)
select a.*
, b.Avg_Daily_Price
,'AAVE' as type
, round(a."amount" * b.Avg_Daily_Price,4) as amount_usd
from staked_aave a
left join prices b
on a."day" = b."day"
order by 1 desc
Run a query to Download Data