WITH ranked as ( SELECT *, rank()over(partition by buyer ORDER BY block_timestamp) as rank FROM flow.core.ez_nft_sales ), last_txns as ( SELECT buyer, max(block_timestamp) as last_txn FROM flow.core.ez_nft_sales GROUP BY buyer ), eth_ranked as ( SELECT *, rank()over(partition by buyer_address ORDER BY block_timestamp) as rank FROM ethereum.core.ez_nft_sales ), eth_last_txns as ( SELECT buyer_address, max(block_timestamp) as last_txn FROM ethereum.core.ez_nft_sales GROUP BY buyer_address ), sol_ranked as ( SELECT block_timestamp, purchaser as from_address, rank()over(partition by from_address ORDER BY block_timestamp) as rank FROM solana.core.fact_nft_sales), sol_last_txns as ( SELECT purchaser as from_address, max(block_timestamp) as last_txn FROM solana.core.fact_nft_sales GROUP BY from_address ), algo_ranked as ( SELECT *, rank()over(partition by purchaser ORDER BY block_timestamp) as rank FROM algorand.nft.ez_nft_sales ), algo_last_txns as ( SELECT purchaser, max(block_timestamp) as last_txn FROM algorand.nft.ez_nft_sales GROUP BY purchaser ), opt_ranked as ( SELECT *, rank()over(partition by buyer_address ORDER BY block_timestamp) as rank FROM optimism.core.ez_nft_sales ), opt_last_txns as ( SELECT buyer_address, max(block_timestamp) as last_txn FROM optimism.core.ez_nft_sales GROUP BY buyer_address ) SELECT chain, avg(one_month_ret) as "6 month avg" FROM ( SELECT 'flow' as chain, x.date as month, 100*n_sustained/(n_sustained+n_leaver+x.n_drop) as one_month_ret FROM (SELECT date_trunc('month',s.last_txn) as date, count(DISTINCT s.buyer) as n_drop FROM last_txns s WHERE last_txn < CURRENT_DATE-30 GROUP BY date)x LEFT JOIN ( SELECT date_trunc('month',a.block_timestamp) as date, count(DISTINCT CASE WHEN datediff(month,a.block_timestamp, b.block_timestamp)>1 THEN a.buyer END) as n_leaver, count(DISTINCT CASE WHEN datediff(month,a.block_timestamp,b.block_timestamp)<=1 THEN a.buyer END) as n_sustained FROM ranked a, ranked b WHERE a.rank = b.rank - 1 AND a.buyer = b.buyer GROUP BY date )y ON x.date = y.date HAVING month >= '2022-06-01' UNION SELECT 'ethereum' as chain, x.date as month, 100*n_sustained/(n_sustained+n_leaver+x.n_drop) as one_month_ret FROM (SELECT date_trunc('month',s.last_txn) as date, count(DISTINCT s.buyer_address) as n_drop FROM eth_last_txns s WHERE last_txn < CURRENT_DATE-30 GROUP BY date)x LEFT JOIN ( SELECT date_trunc('month',a.block_timestamp) as date, count(DISTINCT CASE WHEN datediff(month,a.block_timestamp,b.block_timestamp)>1 THEN a.buyer_address END) as n_leaver, count(DISTINCT CASE WHEN datediff(month,a.block_timestamp,b.block_timestamp)<=1 THEN a.buyer_address END) as n_sustained FROM eth_ranked a, eth_ranked b WHERE a.rank = b.rank - 1 AND a.buyer_address = b.buyer_address GROUP BY date )y ON x.date = y.date HAVING month >= '2022-06-01' UNION SELECT 'solana' as chain, x.date as month, 100*n_sustained/(n_sustained+n_leaver+x.n_drop) as one_month_ret FROM (SELECT date_trunc('month',s.last_txn) as date, count(DISTINCT s.from_address) as n_drop FROM sol_last_txns s WHERE last_txn < CURRENT_DATE-30 --AND date >= '2021-12-01' GROUP BY date)x LEFT JOIN ( SELECT date_trunc('month',a.block_timestamp) as date, count(DISTINCT CASE WHEN datediff(month,a.block_timestamp,b.block_timestamp)>1 THEN a.from_address END) as n_leaver, count(DISTINCT CASE WHEN datediff(month,a.block_timestamp,b.block_timestamp)<=1 THEN a.from_address END) as n_sustained FROM sol_ranked a, sol_ranked b WHERE a.rank = b.rank - 1 AND a.from_address = b.from_address GROUP BY date )y ON x.date = y.date HAVING month >= '2022-06-01' UNION SELECT 'algorand' as chain, x.date as month, 100*n_sustained/(n_sustained+n_leaver+x.n_drop) as one_month_ret FROM (SELECT date_trunc('month',s.last_txn) as date, count(DISTINCT s.purchaser) as n_drop FROM algo_last_txns s WHERE last_txn < CURRENT_DATE-30 GROUP BY date)x LEFT JOIN ( SELECT date_trunc('month',a.block_timestamp) as date, count(DISTINCT CASE WHEN datediff(month,a.block_timestamp, b.block_timestamp)>1 THEN a.purchaser END) as n_leaver, count(DISTINCT CASE WHEN datediff(month,a.block_timestamp,b.block_timestamp)<=1 THEN a.purchaser END) as n_sustained FROM algo_ranked a, algo_ranked b WHERE a.rank = b.rank - 1 AND a.purchaser = b.purchaser GROUP BY date )y ON x.date = y.date HAVING month >= '2022-06-01' UNION SELECT 'optimism' as chain, x.date as month, 100*n_sustained/(n_sustained+n_leaver+x.n_drop) as one_month_ret FROM (SELECT date_trunc('month',s.last_txn) as date, count(DISTINCT s.buyer_address) as n_drop FROM opt_last_txns s WHERE last_txn < CURRENT_DATE-30 GROUP BY date)x LEFT JOIN ( SELECT date_trunc('month',a.block_timestamp) as date, count(DISTINCT CASE WHEN datediff(month,a.block_timestamp, b.block_timestamp)>1 THEN a.buyer_address END) as n_leaver, count(DISTINCT CASE WHEN datediff(month,a.block_timestamp,b.block_timestamp)<=1 THEN a.buyer_address END) as n_sustained FROM opt_ranked a, opt_ranked b WHERE a.rank = b.rank - 1 AND a.buyer_address = b.buyer_address GROUP BY date )y ON x.date = y.date HAVING month >= '2022-06-01' ) GROUP BY chain

Flow Retention