pleasantcommentMakerDao: Skill-building Bounty 3 - USD Tables
Updated 2022-08-23
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
›
⌄
--EZ_deposits - block_timestamp (date trunc to day), depositor, token_deposited, amount_deposited
--WETH prices - hour (date trunc to day), token_address, avg(price)
--join deposits and prices to calculate weth deposit * day price = usd_value_of_deposit
--ez_token_transfers - block_timestamp (date trunc to date), contract_address, amount
--DAI prices - hour (date trunc to day), token_address, avg(price)
--join transfers and prices to calculate dai amount * day price = usd_value_of_withdraw
with weth_price_table as (
select date_trunc('day', hour) as weth_day, avg(price) as weth_price
from ethereum.core.fact_hourly_token_prices
where token_address = lower('0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2')
group by 1
),
dai_price_table as (
select date_trunc('day', hour) as dai_day, avg(price) as dai_price
from ethereum.core.fact_hourly_token_prices
where token_address = lower('0x6B175474E89094C44Da98b954EedeAC495271d0F')
group by 1
),
weth_depsoit as (
select date_trunc('day', block_timestamp) as deposit_day, vault, depositor, amount_deposited, wpt.weth_price, (wpt.weth_price * amount_deposited) as weth_deposit_usd
from ethereum.maker.ez_deposits
left join weth_price_table wpt ON
date_trunc('day', block_timestamp) = wpt.weth_day
where token_deposited = lower('0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2')
),
dai_withdraw as (
select date_trunc('day', block_timestamp) as withdraw_day, to_address, amount, (dp.avg(price) * amount)
from ethereum.core.ez_token_transfers
left join dai_price dp on
withdraw_day = dp.dai_day
where contract_address = lower('0xe1ac9eb7cddabfd9e5ca49c23bd521afcdf8be49')
)
Run a query to Download Data