SELECT

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)

date_part('hour', block_timestamp) as "Hour",

case when dayofweek(block_timestamp)=0 then 7 else dayofweek(block_timestamp) end||' - '||dayname(block_timestamp) as "Day Name",

COUNT(DISTINCT tx_hash) AS tx_count

FROM base

GROUP BY 1, 2

ORDER BY 1,2 asc