select

date_trunc('day', block_hour) as date,

asset_id,

asset_name,

avg(price_usd) as price

from algorand.defi.ez_price_pool_balances

where date >= '{{start_date}}' and date <= '{{end_date}}'

group by 1, 2, 3

select

asset_name,

count(distinct tx_group_id) as swaps_count,

count(distinct swapper) as swappers_count,

sum(swap_from_amount * p.price) as swaps_volume,

avg(swap_from_amount * p.price) as avg_swap_size

from algorand.defi.fact_swap s join price p on block_timestamp::date = p.date and swap_from_asset_id = p.asset_id

where

block_timestamp::date >= '{{start_date}}' and block_timestamp::date <= '{{end_date}}' and

swap_program = '{{dex}}' and

swap_from_amount > 0

group by 1

order by 2 desc

limit 100