date_day as block_day

from crosschain.core.dim_dates

where date_day > current_date - interval'6 months'

and date_day <= current_date

order by date_day

, weth_market_stats as (

select

date_trunc('day', block_hour) as block_day

, reserve_name

, avg(reserve_price) as weth_price

, avg(borrow_rate_variable) as weth_borrow_rate

, avg(total_variable_debt_token) as weth_var_debt

, avg(total_variable_debt_usd) as weth_var_debt_usd

, avg(total_stable_debt_token) as weth_stab_debt

, avg(total_stable_debt_usd) as weth_stab_debt_usd

, weth_var_debt - first_value(weth_var_debt) over (order by block_day rows between 1 preceding and current row) as weth_var_debt_change

, weth_var_debt_change * weth_price as weth_var_debt_change_usd

, weth_stab_debt - first_value(weth_stab_debt) over (order by block_day rows between 1 preceding and current row) as weth_stab_debt_change

, weth_stab_debt_change * weth_price as weth_stab_debt_change_usd

, weth_var_debt_change + weth_stab_debt_change as weth_debt_change

, weth_debt_change * weth_price as weth_debt_change_usd

, (weth_var_debt_usd * weth_borrow_rate / 365 ) as weth_interest_charged_usd

from ethereum.aave.ez_market_stats

where 1=1

and atoken_address = '0x030ba81f1c18d280636f32af80b9aad02cf0854e'

and block_day in (select block_day from dates)

and lending_pool_add = '0x7d2768de32b0b80b7a3454c06bdac94a69ddc7a9'

and aave_version = 'Aave V2'

group by block_day, reserve_name

order by block_day