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
›
⌄
with tx1 as (SELECT tx_id , block_timestamp::date as daily , supplied_usd , issued_tokens , DEPOSITOR_ADDRESS
from flipside_prod_db.aave.deposits
where SYMBOL = 'AAVE'
and datediff('month', block_timestamp, CURRENT_DATE ) <= {{months}}
and supplied_usd is not null
and issued_tokens is not null
) ,
tx2 as (SELECT tx_id ,block_timestamp::date as daily , WITHDRAWN_USD , withdrawn_tokens , DEPOSITOR_ADDRESS
from flipside_prod_db.aave.withdraws
where SYMBOL = 'AAVE'
and datediff('month', block_timestamp, CURRENT_DATE ) <= {{months}}
and WITHDRAWN_USD is not null
and withdrawn_tokens is not null
) ,
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(supplied_usd) as Deposited_in_USD , -sum(WITHDRAWN_USD) as withdrawed_in_usd , Deposited_in_USD+withdrawed_in_usd as net_deposited_in_usd ,
sum(issued_tokens) as deposited_tokens , -sum(withdrawn_tokens) as withdrawed_tokens , deposited_tokens+withdrawed_tokens as net_deposited_token ,
sum(Deposited_in_USD) over(order by A.daily) as cumulative_deposited_in_usd , sum(withdrawed_in_usd) over(order by A.daily) as cumulative_withrdaed_in_usd ,
cumulative_deposited_in_usd+cumulative_withrdaed_in_usd as Cumulative_net_deposited_in_usd ,
count( DISTINCT A.tx_id) as deposit_tx , count(DISTINCT B.tx_id) as withdraw_tx , count(DISTINCT A.DEPOSITOR_ADDRESS) as deposit , count(DISTINCT B.DEPOSITOR_ADDRESS) as withdraw , avg(price) as price
from tx1 A , tx2 B , tx3 C
where A.daily = B.daily
and A.daily = C.daily
group by 1
Run a query to Download Data