block_timestamp,

origin_from_address,

tx_hash,

pool_name,

CASE

WHEN amount_in_usd IS NOT NULL AND amount_out_usd IS NOT NULL THEN (amount_in_usd + amount_out_usd) / 2

ELSE COALESCE(amount_in_usd, amount_out_usd)

END AS amount

from

avalanche.defi.ez_dex_swaps

where

platform = 'pangolin' and

amount_in_usd IS NOT NULL OR amount_out_usd IS NOT NULL

),

daily as (

date_trunc('day', block_timestamp) as date,

count(DISTINCT origin_from_address) as user_count,

count(DISTINCT tx_hash) as tx_count,

count(distinct pool_name) as pool_count,

sum(amount) as total_volume,

avg(amount) as avg_tx_volume

from base

where

block_timestamp::date >= '2025-01-01'

group by 1

select

avg(tx_count) as avg_tx_count,

avg(user_count) as avg_user_count,

avg(total_volume) as avg_total_volume

from daily