-- forked from aptos User Data @ https://flipsidecrypto.xyz/edit/queries/3997d9fd-caf8-4717-ae42-330613987ff7 WITH query1 AS ( SELECT DATE_TRUNC('day', ft.block_timestamp) AS dt, COUNT( DISTINCT CASE WHEN dl.label_type = 'dex' THEN ft.sender END ) AS dex_count, COUNT( DISTINCT CASE WHEN dl.label_type = 'defi' THEN ft.sender END ) AS defi_count, COUNT( DISTINCT CASE WHEN dl.label_type = 'dapp' THEN ft.sender END ) AS dapp_count, COUNT( DISTINCT CASE WHEN dl.label_type = 'nft' THEN ft.sender END ) AS nft_count, COUNT( DISTINCT CASE WHEN dl.label_type = 'layer2' THEN ft.sender END ) AS layer2_count FROM aptos.core.fact_transactions ft JOIN aptos.core.dim_labels dl ON ft.sender = dl.address WHERE ft.block_timestamp >= '2019-01-01' AND dl.label_type IN ('dex', 'defi', 'dapp', 'nft', 'layer2') GROUP BY DATE_TRUNC('day', ft.block_timestamp) ), query2 AS ( SELECT DATE_TRUNC('day', ft.block_timestamp) AS dt, ft.sender, COUNT(DISTINCT ft.sender) AS interactions FROM aptos.core.fact_transactions ft JOIN aptos.core.dim_labels dl ON ft.sender = dl.address WHERE dl.label_type IN ('dex', 'defi', 'dapp', 'nft', 'layer2') AND ft.block_timestamp >= '2019-01-01' GROUP BY DATE_TRUNC('day', ft.block_timestamp), ft.sender ), query3 AS ( SELECT DATE_TRUNC('day', block_timestamp) AS dt, COUNT(DISTINCT sender) AS trad_dau FROM aptos.core.fact_transactions WHERE block_timestamp >= '2019-01-01' GROUP BY DATE_TRUNC('day', block_timestamp) ) SELECT query1.dt, query1.dex_count, query1.defi_count, query1.dapp_count, query1.nft_count, query1.layer2_count, COUNT( DISTINCT CASE WHEN query2.interactions = 1 THEN query2.sender END ) AS interactions_1, COUNT( DISTINCT CASE WHEN query2.interactions >= 2 AND query2.interactions <= 5 THEN query2.sender END ) AS interactions_2_to_5, COUNT( DISTINCT CASE WHEN query2.interactions > 5 AND query2.interactions <= 7 THEN query2.sender END ) AS interactions_5_to_7, COUNT( DISTINCT CASE WHEN query2.interactions > 7 AND query2.interactions <= 10 THEN query2.sender END ) AS interactions_7_to_10, COUNT( DISTINCT CASE WHEN query2.interactions > 10 THEN query2.sender END ) AS interactions_10_plus, query3.trad_dau, COUNT( DISTINCT CASE WHEN query2.interactions = 1 THEN query2.sender END ) + COUNT( DISTINCT CASE WHEN query2.interactions >= 2 AND query2.interactions <= 5 THEN query2.sender END ) + COUNT( DISTINCT CASE WHEN query2.interactions > 5 AND query2.interactions <= 7 THEN query2.sender END ) + COUNT( DISTINCT CASE WHEN query2.interactions > 7 AND query2.interactions <= 10 THEN query2.sender END ) + COUNT( DISTINCT CASE WHEN query2.interactions > 10 THEN query2.sender END ) AS defi_users, query3.trad_dau - ( COUNT( DISTINCT CASE WHEN query2.interactions = 1 THEN query2.sender END ) + COUNT( DISTINCT CASE WHEN query2.interactions >= 2 AND query2.interactions <= 5 THEN query2.sender END ) + COUNT( DISTINCT CASE WHEN query2.interactions > 5 AND query2.interactions <= 7 THEN query2.sender END ) + COUNT( DISTINCT CASE WHEN query2.interactions > 7 AND query2.interactions <= 10 THEN query2.sender END ) + COUNT( DISTINCT CASE WHEN query2.interactions > 10 THEN query2.sender END ) ) AS dead_weight, AVG(query1.dex_count) OVER ( ORDER BY query1.dt ROWS BETWEEN 29 PRECEDING AND CURRENT ROW ) AS sma_30d_dex_count, AVG(query1.defi_count) OVER ( ORDER BY query1.dt ROWS BETWEEN 29 PRECEDING AND CURRENT ROW ) AS sma_30d_defi_count, AVG(query1.dapp_count) OVER ( ORDER BY query1.dt ROWS BETWEEN 29 PRECEDING AND CURRENT ROW ) AS sma_30d_dapp_count, AVG(query1.nft_count) OVER ( ORDER BY query1.dt ROWS BETWEEN 29 PRECEDING AND CURRENT ROW ) AS sma_30d_nft_count, AVG(query1.layer2_count) OVER ( ORDER BY query1.dt ROWS BETWEEN 29 PRECEDING AND CURRENT ROW ) AS sma_30d_layer2_count, AVG( COUNT( DISTINCT CASE WHEN query2.interactions = 1 THEN query2.sender END ) ) OVER ( ORDER BY query1.dt ROWS BETWEEN 29 PRECEDING AND CURRENT ROW ) AS sma_30d_interactions_1, AVG( COUNT( DISTINCT CASE WHEN query2.interactions >= 2 AND query2.interactions <= 5 THEN query2.sender END ) ) OVER ( ORDER BY query1.dt ROWS BETWEEN 29 PRECEDING AND CURRENT ROW ) AS sma_30d_interactions_2_to_5, AVG( COUNT( DISTINCT CASE WHEN query2.interactions > 5 AND query2.interactions <= 7 THEN query2.sender END ) ) OVER ( ORDER BY query1.dt ROWS BETWEEN 29 PRECEDING AND CURRENT ROW ) AS sma_30d_interactions_5_to_7, AVG( COUNT( DISTINCT CASE WHEN query2.interactions > 7 AND query2.interactions <= 10 THEN query2.sender END ) ) OVER ( ORDER BY query1.dt ROWS BETWEEN 29 PRECEDING AND CURRENT ROW ) AS sma_30d_interactions_7_to_10, AVG( COUNT( DISTINCT CASE WHEN query2.interactions > 10 THEN query2.sender END ) ) OVER ( ORDER BY query1.dt ROWS BETWEEN 29 PRECEDING AND CURRENT ROW ) AS sma_30d_interactions_10_plus, AVG(query3.trad_dau) OVER ( ORDER BY query1.dt ROWS BETWEEN 29 PRECEDING AND CURRENT ROW ) AS sma_30d_trad_dau FROM query1 LEFT JOIN query2 ON query1.dt = query2.dt LEFT JOIN query3 ON query1.dt = query3.dt GROUP BY query1.dt, query1.dex_count, query1.defi_count, query1.dapp_count, query1.nft_count, query1.layer2_count, query3.trad_dau ORDER BY query1.dt;

check_sked