-- forked from f7781d74-c1a2-4800-88f5-b24a550a989c -- SOLANA WITH solana_users AS ( SELECT signers[0] AS account ,DATE_TRUNC('month', block_timestamp) AS block_month FROM solana.core.fact_transactions WHERE succeeded -- NEED TO INCLUDE THIS FILTER SINCE SOLANA DATA ONLY GOES -- BACK THIS FAR AND WANT TO ENSURE ALL COMPARISONS ARE APPLES-TO-APPLES. -- CAN REMOVE WHEN DATA IS BACK-FILLED. AND block_timestamp::DATE >= '2021-12-01' GROUP BY 1, 2 ) ,solana_accounts_remaining AS ( SELECT DATEDIFF('month', cohort_month, block_month) AS months_since_join ,COUNT(DISTINCT account) AS num_accounts_remaining FROM ( SELECT account ,block_month ,MIN(block_month) OVER (PARTITION BY account) AS cohort_month FROM solana_users ) WHERE cohort_month >= DATE_TRUNC('month', CURRENT_DATE - 365) AND cohort_month <= DATE_TRUNC('month', CURRENT_DATE - 56) AND DATEDIFF('month', cohort_month, block_month) <= 28 GROUP BY 1 ) ,solana_retention AS ( SELECT months_since_join ,'solana' AS chain ,num_accounts_remaining/FIRST_VALUE(num_accounts_remaining) OVER (ORDER BY months_since_join) AS cumulative_retention FROM solana_accounts_remaining ) -- ETHEREUM ,ethereum_users AS ( SELECT from_address AS account ,DATE_TRUNC('month', block_timestamp) AS block_month FROM ethereum.core.fact_transactions WHERE status = 'SUCCESS' -- NEED TO INCLUDE THIS FILTER SINCE SOLANA DATA ONLY GOES -- BACK THIS FAR AND WANT TO ENSURE ALL COMPARISONS ARE APPLES-TO-APPLES. -- CAN REMOVE WHEN DATA IS BACK-FILLED. AND block_timestamp::DATE >= '2021-12-01' GROUP BY 1, 2 ) ,ethereum_accounts_remaining AS ( SELECT DATEDIFF('month', cohort_month, block_month) AS months_since_join ,COUNT(DISTINCT account) AS num_accounts_remaining FROM ( SELECT account ,block_month ,MIN(block_month) OVER (PARTITION BY account) AS cohort_month FROM ethereum_users ) WHERE cohort_month >= DATE_TRUNC('month', CURRENT_DATE - 365) AND cohort_month <= DATE_TRUNC('month', CURRENT_DATE - 56) AND DATEDIFF('month', cohort_month, block_month) <= 28 GROUP BY 1 ) ,ethereum_retention AS ( SELECT months_since_join ,'ethereum' AS chain ,num_accounts_remaining/FIRST_VALUE(num_accounts_remaining) OVER (ORDER BY months_since_join) AS cumulative_retention FROM ethereum_accounts_remaining ) -- POLYGON ,polygon_transactions AS ( SELECT DATE_TRUNC('month', block_timestamp) AS block_timestamp ,from_address ,success FROM flipside_prod_db.polygon.transactions GROUP BY 1, 2, 3 UNION ALL SELECT DATE_TRUNC('month', block_timestamp) AS block_timestamp ,from_address ,CASE WHEN status = 'SUCCESS' THEN TRUE ELSE FALSE END AS success FROM polygon.core.fact_transactions GROUP BY 1, 2, 3 ) ,polygon_users AS ( SELECT from_address AS account ,DATE_TRUNC('month', block_timestamp) AS block_month FROM polygon_transactions WHERE success -- NEED TO INCLUDE THIS FILTER SINCE SOLANA DATA ONLY GOES -- BACK THIS FAR AND WANT TO ENSURE ALL COMPARISONS ARE APPLES-TO-APPLES. -- CAN REMOVE WHEN DATA IS BACK-FILLED. AND block_timestamp::DATE >= '2021-12-01' GROUP BY 1, 2 ) ,polygon_accounts_remaining AS ( SELECT DATEDIFF('month', cohort_month, block_month) AS months_since_join ,COUNT(DISTINCT account) AS num_accounts_remaining FROM ( SELECT account ,block_month ,MIN(block_month) OVER (PARTITION BY account) AS cohort_month FROM polygon_users ) WHERE cohort_month >= DATE_TRUNC('month', CURRENT_DATE - 365) AND cohort_month <= DATE_TRUNC('month', CURRENT_DATE - 56) AND DATEDIFF('month', cohort_month, block_month) <= 28 GROUP BY 1 ) ,polygon_retention AS ( SELECT months_since_join ,'polygon' AS chain ,num_accounts_remaining/FIRST_VALUE(num_accounts_remaining) OVER (ORDER BY months_since_join) AS cumulative_retention FROM polygon_accounts_remaining ) -- Arbitrum ,arbitrum_users AS ( SELECT from_address AS account ,DATE_TRUNC('month', block_timestamp) AS block_month FROM arbitrum.core.fact_transactions WHERE status = 'SUCCESS' -- NEED TO INCLUDE THIS FILTER SINCE SOLANA DATA ONLY GOES -- BACK THIS FAR AND WANT TO ENSURE ALL COMPARISONS ARE APPLES-TO-APPLES. -- CAN REMOVE WHEN DATA IS BACK-FILLED. AND block_timestamp::DATE >= '2021-12-01' GROUP BY 1, 2 ) ,arbitrum_accounts_remaining AS ( SELECT DATEDIFF('month', cohort_month, block_month) AS months_since_join ,COUNT(DISTINCT account) AS num_accounts_remaining FROM ( SELECT account ,block_month ,MIN(block_month) OVER (PARTITION BY account) AS cohort_month FROM arbitrum_users ) WHERE cohort_month >= DATE_TRUNC('month', CURRENT_DATE - 365) AND cohort_month <= DATE_TRUNC('month', CURRENT_DATE - 56) AND DATEDIFF('month', cohort_month, block_month) <= 28 GROUP BY 1 ) ,arbitrum_retention AS ( SELECT months_since_join ,'arbitrum' AS chain ,num_accounts_remaining/FIRST_VALUE(num_accounts_remaining) OVER (ORDER BY months_since_join) AS cumulative_retention FROM arbitrum_accounts_remaining ) -- Avalanche ,avalanche_users AS ( SELECT from_address AS account ,DATE_TRUNC('month', block_timestamp) AS block_month FROM avalanche.core.fact_transactions WHERE status = 'SUCCESS' -- NEED TO INCLUDE THIS FILTER SINCE SOLANA DATA ONLY GOES -- BACK THIS FAR AND WANT TO ENSURE ALL COMPARISONS ARE APPLES-TO-APPLES. -- CAN REMOVE WHEN DATA IS BACK-FILLED. AND block_timestamp::DATE >= '2021-12-01' GROUP BY 1, 2 ) ,avalanche_accounts_remaining AS ( SELECT DATEDIFF('month', cohort_month, block_month) AS months_since_join ,COUNT(DISTINCT account) AS num_accounts_remaining FROM ( SELECT account ,block_month ,MIN(block_month) OVER (PARTITION BY account) AS cohort_month FROM avalanche_users ) WHERE cohort_month >= DATE_TRUNC('month', CURRENT_DATE - 365) AND cohort_month <= DATE_TRUNC('month', CURRENT_DATE - 56) AND DATEDIFF('month', cohort_month, block_month) <= 28 GROUP BY 1 ) ,avalanche_retention AS ( SELECT months_since_join ,'avalanche' AS chain ,num_accounts_remaining/FIRST_VALUE(num_accounts_remaining) OVER (ORDER BY months_since_join) AS cumulative_retention FROM avalanche_accounts_remaining ) -- Optimism ,optimism_users AS ( SELECT from_address AS account ,DATE_TRUNC('month', block_timestamp) AS block_month FROM optimism.core.fact_transactions WHERE status = 'SUCCESS' -- NEED TO INCLUDE THIS FILTER SINCE SOLANA DATA ONLY GOES -- BACK THIS FAR AND WANT TO ENSURE ALL COMPARISONS ARE APPLES-TO-APPLES. -- CAN REMOVE WHEN DATA IS BACK-FILLED. AND block_timestamp::DATE >= '2021-12-01' GROUP BY 1, 2 ) ,optimism_accounts_remaining AS ( SELECT DATEDIFF('month', cohort_month, block_month) AS months_since_join ,COUNT(DISTINCT account) AS num_accounts_remaining FROM ( SELECT account ,block_month ,MIN(block_month) OVER (PARTITION BY account) AS cohort_month FROM optimism_users ) WHERE cohort_month >= DATE_TRUNC('month', CURRENT_DATE - 365) AND cohort_month <= DATE_TRUNC('month', CURRENT_DATE - 56) AND DATEDIFF('month', cohort_month, block_month) <= 28 GROUP BY 1 ) ,optimism_retention AS ( SELECT months_since_join ,'optimism' AS chain ,num_accounts_remaining/FIRST_VALUE(num_accounts_remaining) OVER (ORDER BY months_since_join) AS cumulative_retention FROM optimism_accounts_remaining ) -- BNB ,bsc_users AS ( SELECT from_address AS account ,DATE_TRUNC('month', block_timestamp) AS block_month FROM bsc.core.fact_transactions WHERE status = 'SUCCESS' -- NEED TO INCLUDE THIS FILTER SINCE SOLANA DATA ONLY GOES -- BACK THIS FAR AND WANT TO ENSURE ALL COMPARISONS ARE APPLES-TO-APPLES. -- CAN REMOVE WHEN DATA IS BACK-FILLED. AND block_timestamp::DATE >= '2021-12-01' GROUP BY 1, 2 ) ,bsc_accounts_remaining AS ( SELECT DATEDIFF('month', cohort_month, block_month) AS months_since_join ,COUNT(DISTINCT account) AS num_accounts_remaining FROM ( SELECT account ,block_month ,MIN(block_month) OVER (PARTITION BY account) AS cohort_month FROM bsc_users ) WHERE cohort_month >= DATE_TRUNC('month', CURRENT_DATE - 365) AND cohort_month <= DATE_TRUNC('month', CURRENT_DATE - 56) AND DATEDIFF('month', cohort_month, block_month) <= 28 GROUP BY 1 ) ,bsc_retention AS ( SELECT months_since_join ,'bsc' AS chain ,num_accounts_remaining/FIRST_VALUE(num_accounts_remaining) OVER (ORDER BY months_since_join) AS cumulative_retention FROM bsc_accounts_remaining ) -- NEAR ,near_users AS ( SELECT tx_signer AS account ,DATE_TRUNC('month', block_timestamp) AS block_month FROM near.core.fact_transactions WHERE block_timestamp::DATE >= '2021-12-01' -- NEED TO INCLUDE THIS FILTER SINCE SOLANA DATA ONLY GOES -- BACK THIS FAR AND WANT TO ENSURE ALL COMPARISONS ARE APPLES-TO-APPLES. -- CAN REMOVE WHEN DATA IS BACK-FILLED. GROUP BY 1, 2 ) ,near_accounts_remaining AS ( SELECT DATEDIFF('month', cohort_month, block_month) AS months_since_join ,COUNT(DISTINCT account) AS num_accounts_remaining FROM ( SELECT account ,block_month ,MIN(block_month) OVER (PARTITION BY account) AS cohort_month FROM near_users ) WHERE cohort_month >= DATE_TRUNC('month', CURRENT_DATE - 365) AND cohort_month <= DATE_TRUNC('month', CURRENT_DATE - 56) AND DATEDIFF('month', cohort_month, block_month) <= 28 GROUP BY 1 ) ,near_retention AS ( SELECT months_since_join ,'near' AS chain ,num_accounts_remaining/FIRST_VALUE(num_accounts_remaining) OVER (ORDER BY months_since_join) AS cumulative_retention FROM near_accounts_remaining ) SELECT * FROM solana_retention UNION ALL SELECT * FROM ethereum_retention UNION ALL SELECT * FROM polygon_retention UNION ALL SELECT * FROM arbitrum_retention UNION ALL SELECT * FROM optimism_retention UNION ALL SELECT * FROM avalanche_retention UNION ALL SELECT * FROM bsc_retention UNION ALL SELECT * FROM near_retention

Query	Creator	Created
Monthly active accounts by chain near bsc arbitrum ethereum optimism	ally	Mar 15, 2023
Monthly Active Retention Per Chain near bsc arbitrum ethereum optimism	ally	Mar 14, 2023
Cross-Chain Retention monthly near bsc arbitrum ethereum optimism	ally	Mar 15, 2023
Weekly active retention across chains near bsc arbitrum ethereum optimism	ally	Mar 14, 2023
Days Active CDF by Chain copy near bsc arbitrum ethereum optimism	ally	Mar 14, 2023

ally