--Get first LIDO deposit transaction for each wallet WITH first_deposit AS ( SELECT staker AS wallet_address, 100*YEAR(block_timestamp)+MONTH(block_timestamp) AS cohort_id, block_timestamp AS first_deposit_timestamp, token_amount AS first_deposit_amount, token_amount_usd AS first_deposit_amount_usd, MONTHS_BETWEEN(CURRENT_DATE(), block_timestamp) AS months_since_first_deposit FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY staker ORDER BY block_timestamp) AS tx_order FROM ethereum.defi.ez_liquid_staking_deposits WHERE platform = 'lido' ) WHERE tx_order = 1 AND token_amount >= 0.01 ), --Append balance after first deposit and all balance changes since --Add which month (number since deposit date) each balance change occurred in steth_balances AS ( SELECT d.wallet_address, b.block_timestamp, --where balance after first deposit is null, replace balance with 0 --happens when user deposits and transfers all stETH out from wallet in 1 single transaction b.current_bal AS balance, CASE WHEN b.block_timestamp = d.first_deposit_timestamp THEN 'M0' WHEN b.block_timestamp <= DATEADD(month, 1, d.first_deposit_timestamp) THEN 'M1' WHEN b.block_timestamp <= DATEADD(month, 2, d.first_deposit_timestamp) THEN 'M2' WHEN b.block_timestamp <= DATEADD(month, 3, d.first_deposit_timestamp) THEN 'M3' WHEN b.block_timestamp <= DATEADD(month, 4, d.first_deposit_timestamp) THEN 'M4' WHEN b.block_timestamp <= DATEADD(month, 5, d.first_deposit_timestamp) THEN 'M5' WHEN b.block_timestamp <= DATEADD(month, 6, d.first_deposit_timestamp) THEN 'M6' WHEN b.block_timestamp <= DATEADD(month, 7, d.first_deposit_timestamp) THEN 'M7' WHEN b.block_timestamp <= DATEADD(month, 8, d.first_deposit_timestamp) THEN 'M8' WHEN b.block_timestamp <= DATEADD(month, 9, d.first_deposit_timestamp) THEN 'M9' WHEN b.block_timestamp <= DATEADD(month, 10, d.first_deposit_timestamp) THEN 'M10' WHEN b.block_timestamp <= DATEADD(month, 11, d.first_deposit_timestamp) THEN 'M11' WHEN b.block_timestamp <= DATEADD(month, 12, d.first_deposit_timestamp) THEN 'M12' WHEN b.block_timestamp <= DATEADD(month, 13, d.first_deposit_timestamp) THEN 'M13' WHEN b.block_timestamp <= DATEADD(month, 14, d.first_deposit_timestamp) THEN 'M14' WHEN b.block_timestamp <= DATEADD(month, 15, d.first_deposit_timestamp) THEN 'M15' WHEN b.block_timestamp <= DATEADD(month, 16, d.first_deposit_timestamp) THEN 'M16' WHEN b.block_timestamp <= DATEADD(month, 17, d.first_deposit_timestamp) THEN 'M17' WHEN b.block_timestamp <= DATEADD(month, 18, d.first_deposit_timestamp) THEN 'M18' WHEN b.block_timestamp <= DATEADD(month, 19, d.first_deposit_timestamp) THEN 'M19' WHEN b.block_timestamp <= DATEADD(month, 20, d.first_deposit_timestamp) THEN 'M20' WHEN b.block_timestamp <= DATEADD(month, 21, d.first_deposit_timestamp) THEN 'M21' WHEN b.block_timestamp <= DATEADD(month, 22, d.first_deposit_timestamp) THEN 'M22' WHEN b.block_timestamp <= DATEADD(month, 23, d.first_deposit_timestamp) THEN 'M23' WHEN b.block_timestamp <= DATEADD(month, 24, d.first_deposit_timestamp) THEN 'M24' ELSE 'M25+' END AS month FROM first_deposit d LEFT JOIN ethereum.core.ez_balance_deltas b ON b.contract_address = '0xae7ab96520de3a18e5e111b5eaab095312d7fe84' --stETH AND b.user_address = d.wallet_address AND b.block_timestamp >= d.first_deposit_timestamp ), --Select only latest balance in each month since last deposit and append all other info about first deposit latest_monthly_balances AS ( SELECT b.wallet_address, d.cohort_id, d.first_deposit_timestamp, d.months_since_first_deposit, d.first_deposit_amount, d.first_deposit_amount_usd, b.month, b.balance FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY wallet_address, month ORDER BY block_timestamp DESC) AS latest_balance_rank FROM steth_balances ) b LEFT JOIN first_deposit d ON b.wallet_address = d.wallet_address WHERE b.latest_balance_rank = 1 ), --Pivot table to get balance for each month (1 to 24) after first deposit pivot AS ( SELECT * FROM latest_monthly_balances PIVOT(SUM(balance) FOR month IN ( 'M0', 'M1', 'M2', 'M3', 'M4', 'M5', 'M6', 'M7', 'M8', 'M9', 'M10', 'M11', 'M12', 'M13', 'M14', 'M15', 'M16', 'M17', 'M18', 'M19', 'M20', 'M21', 'M22', 'M23', 'M24', 'M25+') ) AS p( wallet_address, cohort_id, first_deposit_timestamp, months_since_first_deposit, first_deposit_amount, first_Deposit_amount_usd, M0, M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12, M13, M14, M15, M16, M17, M18, M19, M20, M21, M22, M23, M24, M25p ) ORDER BY wallet_address ), --Fill in balance for each of full months since deposit, by taking previous months' balance if no change since. wallet_table AS ( SELECT wallet_address, cohort_id, first_deposit_timestamp, months_since_first_deposit, first_deposit_amount, first_deposit_amount_usd, M0, --fill in the blanks due to no change in balance in some months CASE WHEN months_since_first_deposit >= 1 THEN COALESCE(M1, M0) ELSE NULL END AS M1, CASE WHEN months_since_first_deposit >= 2 THEN COALESCE(M2, M1, M0) ELSE NULL END AS M2, CASE WHEN months_since_first_deposit >= 3 THEN COALESCE(M3, M2, M1, M0) ELSE NULL END AS M3, CASE WHEN months_since_first_deposit >= 4 THEN COALESCE(M4, M3, M2, M1, M0) ELSE NULL END AS M4, CASE WHEN months_since_first_deposit >= 5 THEN COALESCE(M5, M4, M3, M2, M1, M0) ELSE NULL END AS M5, CASE WHEN months_since_first_deposit >= 6 THEN COALESCE(M6, M5, M4, M3, M2, M1, M0) ELSE NULL END AS M6, CASE WHEN months_since_first_deposit >= 7 THEN COALESCE(M7, M6, M5, M4, M3, M2, M1, M0) ELSE NULL END AS M7, CASE WHEN months_since_first_deposit >= 8 THEN COALESCE(M8, M7, M6, M5, M4, M3, M2, M1, M0) ELSE NULL END AS M8, CASE WHEN months_since_first_deposit >= 9 THEN COALESCE(M9, M8, M7, M6, M5, M4, M3, M2, M1, M0) ELSE NULL END AS M9, CASE WHEN months_since_first_deposit >= 10 THEN COALESCE(M10, M9, M8, M7, M6, M5, M4, M3, M2, M1, M0) ELSE NULL END AS M10, CASE WHEN months_since_first_deposit >= 11 THEN COALESCE(M11, M10, M9, M8, M7, M6, M5, M4, M3, M2, M1, M0) ELSE NULL END AS M11, CASE WHEN months_since_first_deposit >= 12 THEN COALESCE(M12, M11, M10, M9, M8, M7, M6, M5, M4, M3, M2, M1, M0) ELSE NULL END AS M12, CASE WHEN months_since_first_deposit >= 13 THEN COALESCE(M13, M12, M11, M10, M9, M8, M7, M6, M5, M4, M3, M2, M1, M0) ELSE NULL END AS M13, CASE WHEN months_since_first_deposit >= 14 THEN COALESCE(M14, M13, M12, M11, M10, M9, M8, M7, M6, M5, M4, M3, M2, M1, M0) ELSE NULL END AS M14, CASE WHEN months_since_first_deposit >= 15 THEN COALESCE(M15, M14, M13, M12, M11, M10, M9, M8, M7, M6, M5, M4, M3, M2, M1, M0) ELSE NULL END AS M15, CASE WHEN months_since_first_deposit >= 16 THEN COALESCE(M16, M15, M14, M13, M12, M11, M10, M9, M8, M7, M6, M5, M4, M3, M2, M1, M0) ELSE NULL END AS M16, CASE WHEN months_since_first_deposit >= 17 THEN COALESCE(M17, M16, M15, M14, M13, M12, M11, M10, M9, M8, M7, M6, M5, M4, M3, M2, M1, M0) ELSE NULL END AS M17, CASE WHEN months_since_first_deposit >= 18 THEN COALESCE(M18, M17, M16, M15, M14, M13, M12, M11, M10, M9, M8, M7, M6, M5, M4, M3, M2, M1, M0) ELSE NULL END AS M18, CASE WHEN months_since_first_deposit >= 19 THEN COALESCE(M19, M18, M17, M16, M15, M14, M13, M12, M11, M10, M9, M8, M7, M6, M5, M4, M3, M2, M1, M0) ELSE NULL END AS M19, CASE WHEN months_since_first_deposit >= 20 THEN COALESCE(M20, M19, M18, M17, M16, M15, M14, M13, M12, M11, M10, M9, M8, M7, M6, M5, M4, M3, M2, M1, M0) ELSE NULL END AS M20, CASE WHEN months_since_first_deposit >= 21 THEN COALESCE(M21, M20, M19, M18, M17, M16, M15, M14, M13, M12, M11, M10, M9, M8, M7, M6, M5, M4, M3, M2, M1, M0) ELSE NULL END AS M21, CASE WHEN months_since_first_deposit >= 22 THEN COALESCE(M22, M21, M20, M19, M18, M17, M16, M15, M14, M13, M12, M11, M10, M9, M8, M7, M6, M5, M4, M3, M2, M1, M0) ELSE NULL END AS M22, CASE WHEN months_since_first_deposit >= 23 THEN COALESCE(M23, M22, M21, M20, M19, M18, M17, M16, M15, M14, M13, M12, M11, M10, M9, M8, M7, M6, M5, M4, M3, M2, M1, M0) ELSE NULL END AS M23, CASE WHEN months_since_first_deposit >= 24 THEN COALESCE(M24, M23, M22, M21, M20, M19, M18, M17, M16, M15, M14, M13, M12, M11, M10, M9, M8, M7, M6, M5, M4, M3, M2, M1, M0) ELSE NULL END AS M24, CASE WHEN months_since_first_deposit >= 25 THEN COALESCE(M25p, M24, M23, M22, M21, M20, M19, M18, M17, M16, M15, M14, M13, M12, M11, M10, M9, M8, M7, M6, M5, M4, M3, M2, M1, M0) ELSE NULL END AS M25p FROM pivot ), cohort_table AS ( SELECT cohort_id, COUNT(*) AS depositor_count, SUM(first_deposit_amount) AS total_deposited, SUM(first_deposit_amount_usd) AS total_deposited_usd, SUM(first_deposit_amount) / COUNT(*) AS avg_deposit_per_wallet, SUM(first_deposit_amount_usd) / COUNT(*) AS avg_deposit_per_wallet_usd, SUM(M0) AS total_balance_at_deposit, SUM(M1) AS total_balance_at_month_1, SUM(M2) AS total_balance_at_month_2 FROM wallet_table GROUP BY 1 ORDER BY 1 DESC ) --ROW COUNT CHECKS: --select count(*) from first_deposit --143337 depositing wallets --select count(distinct wallet_address) from steth_balances --143337 --select count(distinct wallet_address) from latest_monthly_balances --143337 --select count(*) from pivot --143337 --select count(*) from wallet_table --143337 --select sum(depositor_count) from cohort_table -- SELECT * FROM cohort_table /* NEED TABLE LIKE: COHORTID | MONTH | Balance COHORT1 |0 | COHORT 1 balance at Month 0 COHORT1 |1 | COHORT 1 balance at Month+1 COHORT2 |0 | COHORT 2 balance at Month 0 COHORT2 |1 | COHORT 2 balance at Month+1 To get this: - table with cohort_id and Months 0 to X depending on number of full months since cohort - take user table and do left join with cohort_id_months table on cohort id and month number - where no balance, do coalesce or take last value available

Query	Creator	Created
depositors unieth stakewise stakehound stafi stader	kryptaki	Aug 8, 2023
transactions uniswap kyberswap hashflow maverick dodo	kryptaki	Aug 10, 2023
1.new_users unieth stakewise stakehound stafi stader	kryptaki	Aug 10, 2023
lido unieth stakewise stakehound stafi stader	kryptaki	Aug 3, 2023
depositor_retention unieth stakewise stakehound stafi stader	kryptaki	Aug 9, 2023

kryptaki