Moe* maker
Updated 2023-03-12Copy Reference Fork
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
vault_creation as (select
date_trunc({{scale}},BLOCK_TIMESTAMP)::date as date,
case when tx_hash in (select tx_hash from ethereum.maker.ez_vault_creation) then 'Vault creation'
else 'Re-deposit' end as type,
TOKEN_DEPOSITED,
TX_HASH,
DEPOSITOR,
AMOUNT_DEPOSITED
from
ethereum.maker.ez_deposits
where TOKEN_DEPOSITED = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'
),
price as (
select
date_trunc({{scale}},HOUR)::date as datee,avg(PRICE) as price
from ethereum.core.fact_hourly_token_prices
where TOKEN_ADDRESS = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'
group by 1
)
--,joined as (
select
date,
type,
count(distinct TX_HASH) as total_tx,
count(distinct DEPOSITOR) as total_depositors,
sum(AMOUNT_DEPOSITED) as native_amount_dep,price,
price*native_amount_dep as dep_value_usd
from vault_creation v join price p on date = datee
and date >= CURRENT_DATE - {{days_back}}
group by 1,2,price
--)
Run a query to Download Data