with swap_volume as(
SELECT block_timestamp::date as date,
count(DISTINCT tx_group_id) as tx_count
FROM flipside_prod_db.algorand.swaps
WHERE date >= '2022-01-01'
GROUP by 1
ORDER by 1 ASC
), swap_price as (
SELECT BLOCK_HOUR::date as date,
AVG(price_usd) as algo_price_usd
FROM flipside_prod_db.algorand.prices_swap
WHERE date >= '2022-01-01'
and asset_id = '0'
GROUP by 1
ORDER by 1 ASC
)
SELECT v.date as date,
AVG(v.tx_count) as tx_count,
AVG(p.algo_price_usd) as algo_price
FROM swap_volume v join swap_price p on v.date = p.date
GROUP BY 1
ORDER BY 1 ASC