SELECT

date_trunc('day', block_timestamp) AS date,

AVG(AMOUNT_OUT_USD) AS avg_usd,

distinct(TX_HASH) as transactions

FROM

ethereum.defi.ez_dex_swaps

WHERE

SYMBOL_OUT LIKE '%BONK%'

AND BLOCK_TIMESTAMP >= '2024-05-01'

GROUP BY

date

),

prices AS (

SELECT

date_trunc('day', HOUR) AS date,

SUM(PRICE) / COUNT(IS_IMPUTED) AS price

FROM

ethereum.price.ez_hourly_token_prices

WHERE

SYMBOL = 'BONK'

AND date >= '2024-05-01'

GROUP BY

date

),

percentage_changes AS (

SELECT

date,

(avg_usd - LAG(avg_usd) OVER (ORDER BY date)) / LAG(avg_usd) OVER (ORDER BY date) AS avg_usd_change,

(price - LAG(price) OVER (ORDER BY date)) / LAG(price) OVER (ORDER BY date) AS price_change

FROM

swapped_usd_avg

JOIN

prices USING (date)