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'

),

alls AS (

SELECT

time,

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

),

alls2 AS (

SELECT

time,

sales,

SUM(sales) OVER (ORDER BY time) AS total_sales,

volume,

SUM(volume) OVER (ORDER BY time) AS total_volume,

avg_price

FROM alls

ORDER BY time DESC

SELECT * FROM alls2 order by time desc