-- Capture wallets that interacted with LP actions in Uniswap V3 pools from Jul 2024 to Sep 2024

SELECT

DATE_TRUNC('MONTH', BLOCK_TIMESTAMP) AS MONTH,

LIQUIDITY_PROVIDER AS WALLET_ADDRESS,

POOL_ADDRESS

FROM

ethereum.uniswapv3.ez_lp_actions

WHERE

BLOCK_TIMESTAMP >= '2024-07-01' AND BLOCK_TIMESTAMP < '2024-10-01'

AND POOL_ADDRESS IN (

SELECT POOL_ADDRESS FROM ethereum.uniswapv3.ez_pools

WHERE FACTORY_ADDRESS = '0x1F98431c8aD98523631AE4a59f267346ea31F984'

)

UNION ALL

-- Capture wallets that interacted with swaps in Uniswap V3 pools from Jul 2024 to Sep 2024

SELECT

DATE_TRUNC('MONTH', BLOCK_TIMESTAMP) AS MONTH,

RECIPIENT AS WALLET_ADDRESS,

POOL_ADDRESS

FROM

ethereum.uniswapv3.ez_swaps

WHERE

BLOCK_TIMESTAMP >= '2024-07-01' AND BLOCK_TIMESTAMP < '2024-10-01'

AND POOL_ADDRESS IN (

SELECT POOL_ADDRESS FROM ethereum.uniswapv3.ez_pools

)

),

monthly_cohorts AS (

-- Assign each wallet to its first month of interaction with Uniswap V3 as the cohort month

SELECT

WALLET_ADDRESS,

MIN(MONTH) AS COHORT_MONTH