WITH top_projects AS (

SELECT

pool_name

FROM

avalanche.defi.ez_dex_swaps

WHERE

platform ilike '%joe%' and block_timestamp>=current_date-INTERVAL '1 WEEK' and AMOUNT_OUT_USD is not null

GROUP BY

pool_name

ORDER BY

SUM(AMOUNT_OUT_USD) DESC

LIMIT 10

),

joe as (

pool_name,

ORIGIN_FROM_ADDRESS,

tx_hash,

AMOUNT_OUT_USD AS volume

FROM

avalanche.defi.ez_dex_swaps

WHERE

platform ilike '%joe%'

AND pool_name IN (SELECT pool_name FROM top_projects)

AND block_timestamp > current_date - interval '1 week'

),

alls AS (

SELECT

pool_name,

COUNT(DISTINCT tx_hash) AS sales,

COUNT(DISTINCT ORIGIN_FROM_ADDRESS) AS active_users,

SUM(volume) AS volume,

AVG(volume) AS avg_price

FROM joe

GROUP BY 1