prices AS (

SELECT

eth.hour::date AS day,

AVG(eth.price) AS price_eth,

AVG(btc.price) AS price_btc,

AVG(avax.price) AS price_avax,

AVG(link.price) AS price_link

FROM

ethereum.core.fact_hourly_token_prices eth

LEFT JOIN ethereum.core.fact_hourly_token_prices btc ON eth.hour::date = btc.hour::date

LEFT JOIN ethereum.core.fact_hourly_token_prices avax ON eth.hour::date = avax.hour::date

LEFT JOIN ethereum.core.fact_hourly_token_prices link ON eth.hour::date = link.hour::date

WHERE

eth.symbol = 'WETH'

AND btc.symbol = 'WBTC'

AND avax.symbol = 'WAVAX'

AND link.symbol = 'LINK'

GROUP BY

day

ORDER BY

day

),

transactions AS (

SELECT

block_timestamp,

tx_hash,

CASE

WHEN event_name = 'Mint' THEN 'Supply'

WHEN event_name = 'Borrow' THEN 'Borrow'

WHEN event_name = 'RepayBorrow' THEN 'Repay'

WHEN event_name = 'Redeem' THEN 'Redeem'

END AS event,

origin_from_address AS user,

CASE

WHEN contract_address = '0x5c0401e81bc07ca70fad469b451682c0d747ef1c' THEN 'AVAX'