WITH active_users AS (

-- Track users performing DEX swaps on Ethereum using contract_address

SELECT DISTINCT contract_address AS user_address, DATE_TRUNC('week', inserted_timestamp) AS week

FROM ethereum.defi.ez_dex_swaps

WHERE inserted_timestamp >= '2024-10-01' AND inserted_timestamp < '2024-11-01' -- Filter for October

GROUP BY user_address, week

UNION ALL

-- Track users interacting with lending protocols (deposits and borrows)

SELECT DISTINCT contract_address AS user_address, DATE_TRUNC('week', inserted_timestamp) AS week

FROM ethereum.defi.ez_lending_borrows

WHERE inserted_timestamp >= '2024-10-01' AND inserted_timestamp < '2024-11-01'

GROUP BY user_address, week

UNION ALL

SELECT DISTINCT contract_address AS user_address, DATE_TRUNC('week', inserted_timestamp) AS week

FROM ethereum.defi.ez_lending_deposits

WHERE inserted_timestamp >= '2024-10-01' AND inserted_timestamp < '2024-11-01'

GROUP BY user_address, week

UNION ALL

-- Track users interacting with liquid staking (deposits and withdrawals)

SELECT DISTINCT contract_address AS user_address, DATE_TRUNC('week', inserted_timestamp) AS week

FROM ethereum.defi.ez_liquid_staking_deposits

WHERE inserted_timestamp >= '2024-10-01' AND inserted_timestamp < '2024-11-01'

GROUP BY user_address, week

UNION ALL

SELECT DISTINCT contract_address AS user_address, DATE_TRUNC('week', inserted_timestamp) AS week