count(DISTINCT(tx_hash)) as transaction, count(DISTINCT(origin_from_address)) as users,

sum(amount_out) as volume

from ethereum.core.ez_dex_swaps

where block_timestamp::date >= '2023-06-20'

and TOKEN_OUT = lower('0x7Fc66500c84A76Ad7e9c93437bFc5Ac33E2DDaE9')

group by 1,2)

,

avve_price as ( select hour, price

from ethereum.core.fact_hourly_token_prices

where hour::date >= '2023-06-20'

and token_address = lower('0x7Fc66500c84A76Ad7e9c93437bFc5Ac33E2DDaE9'))

,

price as ( select avg(price) as previous_price

from ethereum.core.fact_hourly_token_prices

where hour::date = '2023-06-20'

and token_address = lower('0x7Fc66500c84A76Ad7e9c93437bFc5Ac33E2DDaE9'))

,

final as ( select a.hour, price as aave_price, previous_price,

(aave_price-previous_price)/(aave_price)*100 as change_ratio

from avve_price a , price b )

select a.hour, transaction, users, volume, change_ratio,aave_price

from final a join swap b on a.hour = b.hour