core | rackhael | Flipside

Updated 2023-01-26

-- with user_cohorts as (

-- SELECT origin_to_address as address

-- , min(date_trunc('month', block_timestamp)) as cohortMonth

-- FROM ethereum.core.ez_dex_swaps

-- WHERE contract_address = unhex('7d2768dE32b0b80b7a3454c06BdAc94A69DDc7A9') -- Aave V2 Lending Pool --change this to another dex prob uniswapv2

-- AND chain_name = 'eth_mainnet'

-- AND signed_at > '2020-12-01' -- Date of Aave V2 Launch

-- GROUP BY address

-- ),

-- following_months as (

-- SELECT tx_sender as address

-- , date_diff('month', uc.cohortMonth, date_trunc('month', signed_at)) as month_number

-- FROM blockchains.all_chains

-- LEFT JOIN user_cohorts uc ON address = uc.address

-- WHERE tx_recipient = unhex('7d2768dE32b0b80b7a3454c06BdAc94A69DDc7A9') -- Aave V2 Lending Pool

-- AND chain_name = 'eth_mainnet'

-- AND signed_at > '2020-12-01' -- Date of Aave V2 Launch

-- GROUP BY address, month_number

-- ),

-- cohort_size as (

-- SELECT uc.cohortMonth as cohortMonth,

-- count(*) as num_users

-- FROM user_cohorts uc

-- GROUP BY cohortMonth

-- ORDER BY cohortMonth

-- ),

-- retention_table as (

-- SELECT c.cohortMonth as cohortMonth

-- , o.month_number as month_number

-- , count(*) as num_users

-- FROM following_months o

-- LEFT JOIN user_cohorts c ON o.address = c.address

-- GROUP BY cohortMonth, month_number

-- )

-- SELECT r.cohortMonth

-- , s.num_users as new_users

Run a query to Download Data

flipside