SELECT date(block_hour) as date, avg(price_usd) as price

from algorand.prices_swap

where date(block_hour) >= '2022-01-01'

and asset_id = 0

GROUP by date

select date(sw.block_timestamp) as date, sw.swap_program, pr.price,

sum(case when SWAP_FROM_ASSET_ID = 0 then SWAP_FROM_AMOUNT else swap_to_amount END) as algo_vol,

avg(case when SWAP_FROM_ASSET_ID = 0 then SWAP_FROM_AMOUNT else swap_to_amount END) as avg_algo_vol

from flipside_prod_db.algorand.swaps sw

join prices pr on pr.date = date(sw.block_timestamp)

where date(block_timestamp) BETWEEN '2022-01-01' and CURRENT_DATE - 1

and sw.swap_from_amount > 0

and (SWAP_FROM_ASSET_ID = 0 or SWAP_TO_ASSET_ID = 0)

group by date(sw.block_timestamp), sw.swap_program, price

order by date