WeeklySummary AS (

SELECT

DATE_TRUNC('day', block_timestamp) AS week_start,

SUM(CASE

WHEN amount_out_usd IS NOT NULL THEN amount_out_usd

ELSE amount_in_usd

END) AS weekly_volume,

COUNT(DISTINCT trader) AS weekly_unique_traders,

COUNT(DISTINCT tx_hash) AS weekly_transactions

FROM

near.defi.ez_dex_swaps

WHERE

block_timestamp >= '2025-01-01'

AND platform = 'v2.ref-finance.near'

AND ('REF' IN (symbol_in, symbol_out))

GROUP BY

week_start

),

WeeklyNewTraders AS (

SELECT

DATE_TRUNC('day', first_trade_date) AS week_start,

COUNT(DISTINCT trader) AS weekly_new_traders

FROM (

SELECT

trader,

MIN(block_timestamp) AS first_trade_date

FROM

near.defi.ez_dex_swaps

WHERE

platform = 'v2.ref-finance.near'

AND ('REF' IN (symbol_in, symbol_out))

GROUP BY

trader

) AS FirstTrades