-- forked from Velodrome - Pool's Swap Performance @ https://flipsidecrypto.xyz/edit/queries/170a6a68-72a9-4791-a1be-5189f0b5f058

with swap_detail as (

SELECT

SYMBOL_IN || ' => ' || SYMBOL_OUT as "Pair",

count(distinct ORIGIN_FROM_ADDRESS) as "Swapper Count",

count(tx_hash) as "swaps Count",

sum(COALESCE(amount_in_usd, AMOUNT_OUT_USD,0)) as "Total Volume ($)"

FROM avalanche.core.ez_dex_swaps

WHERE PLATFORM in ('{{Custom_Dex}}') -- trader-joe-v1, trader-joe-v2, platypus, hashflow, woofi, kyberswap-v1, kyberswap-v2, platypus, pangolin, fraxswap, sushiswap, curve, hashflow, gmx

group by 1

,LAST7 AS ( -- swap volume in the last 7 days

SELECT

SYMBOL_IN || ' => ' || SYMBOL_OUT as "Pair",

count(distinct ORIGIN_FROM_ADDRESS) as "7D Swappers",

count(tx_hash) as "7D Tx",

round(sum(COALESCE(amount_in_usd, AMOUNT_OUT_USD,0)),1) as "7D"

FROM avalanche.core.ez_dex_swaps

WHERE date_trunc('hour', block_timestamp) BETWEEN DATEADD(HOUR, -168, GETDATE()) AND GETDATE()

AND PLATFORM in ('{{Custom_Dex}}')

GROUP BY 1

),

LAST14 AS (-- swap volume in the last 14 days

SELECT

SYMBOL_IN || ' => ' || SYMBOL_OUT as "Pair",

count(distinct ORIGIN_FROM_ADDRESS) as "14D Swappers",

count(tx_hash) as "14D Tx",

round(sum(COALESCE(amount_in_usd, AMOUNT_OUT_USD,0)),1) as "14D"

FROM avalanche.core.ez_dex_swaps

WHERE date_trunc('hour', block_timestamp) BETWEEN DATEADD(HOUR, -336, GETDATE()) AND GETDATE()

AND PLATFORM in ('{{Custom_Dex}}')

GROUP BY 1

),