WITH UNI AS (
select
date_trunc('day',hour) as day,
avg(price) as uni_price
from ethereum.token_prices_hourly
where
symbol = 'UNI' AND
hour >= '2021-11-01' AND
token_address = '0x1f9840a85d5af5bf1d1762f925bdaddc4201f984'
group by 1
order by 1
),
v3volume AS (
SELECT
date_trunc('day',block_timestamp) as block_day,
COUNT(tx_id) as transactions
FROM ethereum.dex_swaps
WHERE
platform = 'uniswap-v3' AND
block_day >= '2021-11-01'
GROUP BY block_day
ORDER BY block_day
)
select
CORR (uni_price,transactions)
from UNI a
left join v3volume on block_day = day;
--left join algorand.asset b on b.asset_id = a.swap_to_asset_id