SELECT

pool_name

FROM avalanche.defi.ez_dex_swaps

WHERE

platform ilike '%trader-joe%' and amount_out_usd is not null

GROUP BY

pool_name

ORDER BY

SUM(amount_out_usd) DESC

LIMIT 10

),

joe AS (

SELECT

DATE_TRUNC('day', block_timestamp) AS time,

pool_name,

sender,

tx_hash,

amount_out_usd AS volume

FROM avalanche.defi.ez_dex_swaps

WHERE DATE_TRUNC('day', block_timestamp) < DATE_TRUNC('day', CURRENT_DATE) and platform ilike '%trader-joe%'

and time>=current_date-INTERVAL '1 week' and amount_out_usd is not null

--AND pool_name IN (SELECT pool_name FROM top_projects)

),

alls AS (

SELECT

time,

pool_name,

COUNT(DISTINCT tx_hash) AS sales,

COUNT(DISTINCT sender) AS active_users,

SUM(volume) AS volume,

AVG(volume) AS avg_price

FROM joe

GROUP BY 1,2

),

alls2 AS (