mehrancrypto-dxoepqQ1- 1
Updated 2022-08-12
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 tx1 AS
( select tx_hash , origin_from_address
from ethereum.core.fact_event_logs
where event_name = 'Staked'
and contract_address = lower('0x4da27a545c0c5b758a6ba100e3a049001de870f5')
and datediff('month', block_timestamp, CURRENT_DATE ) <= {{months}}
) ,
staking as (
select A1.tx_hash , block_timestamp::date as daily, 'Stake' as type, from_address as staker, amount , amount_usd
from ethereum.core.ez_token_transfers A1 , tx1 B1
where A1.tx_hash = B1.tx_hash and A1.origin_from_address = from_address
and symbol = 'AAVE'
)
, tx2 AS
( select tx_hash , origin_from_address
from ethereum.core.fact_event_logs
where contract_address = lower('0x4da27a545c0c5b758a6ba100e3a049001de870f5')
and event_name = 'Redeem'
and datediff('month', block_timestamp, CURRENT_DATE ) <= {{months}} ) ,
unstaking as (
select A2.tx_hash , block_timestamp::date as daily, 'UnStake' as type, A2.origin_from_address as unstaker, amount , amount_usd
from ethereum.core.ez_token_transfers A2 , tx2 B2
where A2.tx_hash = B2.tx_hash and A2.origin_from_address = to_address
and symbol = 'AAVE'
) ,
tx3 as (
SELECT HOUR::date as daily , avg(price) as price
from ethereum.core.fact_hourly_token_prices
where SYMBOL = 'AAVE'
group by 1
)
SELECT A.daily , sum(A.amount) as staked , sum(A.amount_usd) as staked_usd , -sum(B.amount) as unstaked , -sum(B.amount_usd) as unstaked_usd ,
Run a query to Download Data