SELECT

origin_from_address

, tx_hash

, amount_in_usd

, amount_out_usd

, MIN(date_trunc('day', block_timestamp)) AS first_date

, platform

FROM

base.defi.ez_dex_swaps

WHERE 1=1

AND block_timestamp >= (current_date - INTERVAL '7 DAYS')

GROUP BY

1, 2, 3, 4, 6

),

filtered_addresses AS (

SELECT

origin_from_address

FROM

base.defi.ez_dex_swaps

WHERE 1=1

AND block_timestamp < (current_date - INTERVAL '7 DAYS')

GROUP BY

origin_from_address

),

new_users AS (

SELECT

COUNT(DISTINCT r.origin_from_address) AS new_users

, r.first_date AS date

, r.platform

, COUNT(r.tx_hash) AS new_users_tx

, SUM(ABS(r.amount_in_usd) + ABS(r.amount_out_usd)) / 2 AS new_users_transaction_volume

FROM

recent_transactions r

LEFT JOIN filtered_addresses f

ON r.origin_from_address = f.origin_from_address