with a as
(
select
block_hour::date as date,
avg(price_usd) as average_price
from flipside_prod_db.algorand.prices_swap
where date >= '2021-06-01'
and asset_id = 0
group by 1
),
b as(
select
block_timestamp::date as DATE,
count(distinct tx_id) as number_of_transactions
from flipside_prod_db.algorand.transactions
where date >= '2021-06-01'
group by 1
)
select*
from a
left join b
using(date)