unique_addresses AS (

SELECT

MIN(TRUNC(tx.block_timestamp, 'week')) AS new_week,

tx.from_address AS address

FROM sei.core_evm.fact_transactions AS tx

INNER JOIN sei.core_evm.fact_event_logs AS el

ON tx.tx_hash = el.tx_hash

WHERE el.topics[0]::STRING = '0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822'

AND tx.status = 'SUCCESS'

GROUP BY tx.from_address

),

weekly_activity AS (

SELECT

tx.tx_hash AS transaction_id,

TRUNC(tx.block_timestamp, 'week') AS date,

tx.from_address AS wallet,

tx.tx_fee AS transaction_fee

FROM sei.core_evm.fact_transactions AS tx

INNER JOIN sei.core_evm.fact_event_logs AS el

ON tx.tx_hash = el.tx_hash

WHERE el.topics[0]::STRING = '0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822'

AND tx.status = 'SUCCESS'

),

aggregated_metrics AS (

SELECT

act.date,

COUNT(DISTINCT act.transaction_id) AS swap_count,

COUNT(DISTINCT act.wallet) AS distinct_swappers,

AVG(act.transaction_fee) AS avg_fee,

SUM(act.transaction_fee) AS total_fees

FROM weekly_activity AS act

GROUP BY act.date

),

final_results AS (

SELECT