binhachon29. [Easy] Gas Prices and Deposit/Borrow Amounts - deposit withdraw
    Updated 2021-11-07
    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