-- forked from check_sked / Optimism User Data @ https://flipsidecrypto.xyz/check_sked/q/tGOESc22B3HO/optimism-user-data WITH query1 AS ( SELECT DATE_TRUNC('day', ft.block_timestamp) AS dt, COUNT( DISTINCT CASE WHEN dl.label_type = 'defi' THEN ft.from_address END ) AS defi_count, COUNT( DISTINCT CASE WHEN dl.label_type = 'dapp' THEN ft.from_address END ) AS dapp_count, COUNT( DISTINCT CASE WHEN dl.label_type = 'nft' THEN ft.from_address END ) AS nft_count, COUNT( DISTINCT CASE WHEN dl.label_type = 'layer2' THEN ft.from_address END ) AS layer2_count FROM optimism.core.fact_transactions ft JOIN optimism.core.dim_labels dl ON ft.to_address = 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.from_address, COUNT(DISTINCT ft.to_address) AS interactions FROM optimism.core.fact_transactions ft JOIN optimism.core.dim_labels dl ON ft.to_address = 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.from_address ), query3 AS ( SELECT DATE_TRUNC('day', block_timestamp) AS dt, COUNT(DISTINCT from_address) AS trad_dau FROM optimism.core.fact_transactions WHERE block_timestamp >= '2019-01-01' GROUP BY DATE_TRUNC('day', block_timestamp) ), query4 AS ( SELECT DATE_TRUNC('day', block_timestamp) AS dt, COUNT(DISTINCT sender) AS dex_count FROM optimism.defi.ez_dex_swaps WHERE block_timestamp >= '2019-01-01' GROUP BY DATE_TRUNC('day', block_timestamp) ) SELECT query1.dt, query4.dex_count, query1.defi_count, query1.dapp_count, query1.nft_count, query1.layer2_count, COUNT( DISTINCT CASE WHEN query2.interactions = 1 THEN query2.from_address END ) AS interactions_1, COUNT( DISTINCT CASE WHEN query2.interactions >= 2 AND query2.interactions <= 5 THEN query2.from_address END ) AS interactions_2_to_5, COUNT( DISTINCT CASE WHEN query2.interactions > 5 AND query2.interactions <= 7 THEN query2.from_address END ) AS interactions_5_to_7, COUNT( DISTINCT CASE WHEN query2.interactions > 7 AND query2.interactions <= 10 THEN query2.from_address END ) AS interactions_7_to_10, COUNT( DISTINCT CASE WHEN query2.interactions > 10 THEN query2.from_address END ) AS interactions_10_plus, query3.trad_dau, COUNT( DISTINCT CASE WHEN query2.interactions = 1 THEN query2.from_address END ) + COUNT( DISTINCT CASE WHEN query2.interactions >= 2 AND query2.interactions <= 5 THEN query2.from_address END ) + COUNT( DISTINCT CASE WHEN query2.interactions > 5 AND query2.interactions <= 7 THEN query2.from_address END ) + COUNT( DISTINCT CASE WHEN query2.interactions > 7 AND query2.interactions <= 10 THEN query2.from_address END ) + COUNT( DISTINCT CASE WHEN query2.interactions > 10 THEN query2.from_address END ) AS defi_users, query3.trad_dau - ( COUNT( DISTINCT CASE WHEN query2.interactions = 1 THEN query2.from_address END ) + COUNT( DISTINCT CASE WHEN query2.interactions >= 2 AND query2.interactions <= 5 THEN query2.from_address END ) + COUNT( DISTINCT CASE WHEN query2.interactions > 5 AND query2.interactions <= 7 THEN query2.from_address END ) + COUNT( DISTINCT CASE WHEN query2.interactions > 7 AND query2.interactions <= 10 THEN query2.from_address END ) + COUNT( DISTINCT CASE WHEN query2.interactions > 10 THEN query2.from_address END ) ) AS dead_weight, AVG(query4.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.from_address 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.from_address 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.from_address 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.from_address 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.from_address 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 LEFT JOIN query4 ON query1.dt = query4.dt GROUP BY query1.dt, query4.dex_count, query1.defi_count, query1.dapp_count, query1.nft_count, query1.layer2_count, query3.trad_dau ORDER BY query1.dt;

galaxy_zack