binhachon29. [Easy] Gas Prices and Deposit/Borrow Amounts - deposit withdraw
Updated 2021-11-07
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 gas_price as (
select date_trunc('day', block_timestamp) as blocktime, avg(gas_price)/1e9 as gas_price from ethereum.transactions
where blocktime > getdate() - interval'180 days'
group by blocktime
),
deposit_fee as(
select date_trunc('day', block_timestamp) as blocktime, avg(tx_fee) as deposit_fee from ethereum.transactions
where blocktime > getdate() - interval'180 days'
and tx_id in (select tx_id from aave.deposits where block_timestamp > getdate() - interval'180 days')
group by blocktime
),
withdraw_fee as(
select date_trunc('day', block_timestamp) as blocktime, avg(tx_fee) as withdraw_fee from ethereum.transactions
where blocktime > getdate() - interval'180 days'
and tx_id in (select tx_id from aave.withdraws where block_timestamp > getdate() - interval'180 days')
group by blocktime
),
borrow_fee as(
select date_trunc('day', block_timestamp) as blocktime, avg(tx_fee) as borrow_fee from ethereum.transactions
where blocktime > getdate() - interval'180 days'
and tx_id in (select tx_id from aave.borrows where block_timestamp > getdate() - interval'180 days')
group by blocktime
),
repay_fee as(
select date_trunc('day', block_timestamp) as blocktime, avg(tx_fee) as repay_fee from ethereum.transactions
where blocktime > getdate() - interval'180 days'
and tx_id in (select tx_id from aave.repayments where block_timestamp > getdate() - interval'180 days')
group by blocktime
),
eth_price as (
select date_trunc('day', hour) as blocktime, avg(price) as price from ethereum.token_prices_hourly
where symbol = 'ETH'
group by blocktime
)
select deposit_fee.blocktime,
deposit_fee, deposit_fee * price as deposit_fee_USD,
Run a query to Download Data