( WITH gs AS ( SELECT DISTINCT DATE_TRUNC('day', block_timestamp) AS day FROM ethereum.core.fact_transactions WHERE block_timestamp < DATE_TRUNC('day',CURRENT_DATE()) AND block_timestamp >= DATE_TRUNC('day', CURRENT_DATE() - interval '210 DAYS') ORDER BY 1 ), addrs AS ( SELECT day, addr FROM ( SELECT DATE_TRUNC('day', block_timestamp) AS day, from_address AS addr FROM arbitrum.core.fact_transactions WHERE -- status = 'SUCCESS' block_timestamp < DATE_TRUNC('day', CURRENT_DATE()) AND block_timestamp >= DATE_TRUNC('day', CURRENT_DATE() - INTERVAL '210 DAYS') GROUP BY 1,2 ) GROUP BY day, addr ), daus AS ( SELECT day, COUNT(*) AS dau FROM ( SELECT day ,addr FROM addrs GROUP BY 1,2 ) a GROUP BY 1 ), waus AS ( SELECT day, COUNT(*) AS wau FROM ( SELECT gs.day AS day, w.addr FROM addrs w INNER JOIN gs ON w.day >= gs.day - (interval '6 days') AND w.day <= gs.day GROUP BY 1, 2 ) a GROUP BY 1 ), maus AS ( SELECT day, COUNT(*) AS mau FROM ( SELECT gs.day AS day, w.addr FROM addrs w INNER JOIN gs ON w.day >= gs.day - (interval '29 days') AND w.day <= gs.day GROUP BY 1, 2 ) a GROUP BY 1 ) SELECT *, -- DENSE_RANK() OVER (PARTITION BY DATE_TRUNC('week',day) ORDER BY day DESC) AS week_rank, -- DENSE_RANK() OVER (PARTITION BY DATE_TRUNC('month',day) ORDER BY day DESC) AS month_rank, AVG(dau) OVER (ORDER BY day ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS dau_7dma, AVG(dau) OVER (ORDER BY day ASC ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS dau_30dma, 'Arbitrum' AS chain FROM ( SELECT d.day, d.dau, w.wau, m.mau FROM daus d INNER JOIN waus w ON w.day = d.day INNER JOIN maus m ON m.day = d.day ) a WHERE day IN ('2023-06-10', '2023-05-11', '2023-04-11', '2023-03-12', '2023-02-10', '2023-01-11') ORDER BY day DESC ) UNION ALL (WITH gs AS ( SELECT DISTINCT DATE_TRUNC('day', block_timestamp) AS day FROM ethereum.core.fact_transactions WHERE block_timestamp < DATE_TRUNC('day',CURRENT_DATE()) AND block_timestamp >= DATE_TRUNC('day', CURRENT_DATE() - interval '210 DAYS') ORDER BY 1 ), addrs AS ( SELECT day, addr FROM ( SELECT DATE_TRUNC('day', block_timestamp) AS day, from_address AS addr FROM optimism.core.fact_transactions WHERE -- status = 'SUCCESS' block_timestamp < DATE_TRUNC('day', CURRENT_DATE()) AND block_timestamp >= DATE_TRUNC('day', CURRENT_DATE() - INTERVAL '210 DAYS') GROUP BY 1,2 ) GROUP BY day, addr ), daus AS ( SELECT day, COUNT(*) AS dau FROM ( SELECT day ,addr FROM addrs GROUP BY 1,2 ) a GROUP BY 1 ), waus AS ( SELECT day, COUNT(*) AS wau FROM ( SELECT gs.day AS day, w.addr FROM addrs w INNER JOIN gs ON w.day >= gs.day - (interval '6 days') AND w.day <= gs.day GROUP BY 1, 2 ) a GROUP BY 1 ), maus AS ( SELECT day, COUNT(*) AS mau FROM ( SELECT gs.day AS day, w.addr FROM addrs w INNER JOIN gs ON w.day >= gs.day - (interval '29 days') AND w.day <= gs.day GROUP BY 1, 2 ) a GROUP BY 1 ) SELECT *, -- DENSE_RANK() OVER (PARTITION BY DATE_TRUNC('week',day) ORDER BY day DESC) AS week_rank, -- DENSE_RANK() OVER (PARTITION BY DATE_TRUNC('month',day) ORDER BY day DESC) AS month_rank, AVG(dau) OVER (ORDER BY day ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS dau_7dma, AVG(dau) OVER (ORDER BY day ASC ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS dau_30dma, 'Optimism' AS chain FROM ( SELECT d.day, d.dau, w.wau, m.mau FROM daus d INNER JOIN waus w ON w.day = d.day INNER JOIN maus m ON m.day = d.day ) a WHERE day IN ('2023-06-10', '2023-05-11', '2023-04-11', '2023-03-12', '2023-02-10', '2023-01-11') ORDER BY day DESC ) UNION ALL ( WITH gs AS ( SELECT DISTINCT DATE_TRUNC('day', block_timestamp) AS day FROM ethereum.core.fact_transactions WHERE block_timestamp < DATE_TRUNC('day',CURRENT_DATE()) AND block_timestamp >= DATE_TRUNC('day', CURRENT_DATE() - interval '210 DAYS') ORDER BY 1 ), addrs AS ( SELECT day, addr FROM ( SELECT DATE_TRUNC('day', block_timestamp) AS day, from_address AS addr FROM bsc.core.fact_transactions WHERE -- status = 'SUCCESS' block_timestamp < DATE_TRUNC('day', CURRENT_DATE()) AND block_timestamp >= DATE_TRUNC('day', CURRENT_DATE() - INTERVAL '210 DAYS') GROUP BY 1,2 ) GROUP BY day, addr ), daus AS ( SELECT day, COUNT(*) AS dau FROM ( SELECT day ,addr FROM addrs GROUP BY 1,2 ) a GROUP BY 1 ), waus AS ( SELECT day, COUNT(*) AS wau FROM ( SELECT gs.day AS day, w.addr FROM addrs w INNER JOIN gs ON w.day >= gs.day - (interval '6 days') AND w.day <= gs.day GROUP BY 1, 2 ) a GROUP BY 1 ), maus AS ( SELECT day, COUNT(*) AS mau FROM ( SELECT gs.day AS day, w.addr FROM addrs w INNER JOIN gs ON w.day >= gs.day - (interval '29 days') AND w.day <= gs.day GROUP BY 1, 2 ) a GROUP BY 1 ) SELECT *, -- DENSE_RANK() OVER (PARTITION BY DATE_TRUNC('week',day) ORDER BY day DESC) AS week_rank, -- DENSE_RANK() OVER (PARTITION BY DATE_TRUNC('month',day) ORDER BY day DESC) AS month_rank, AVG(dau) OVER (ORDER BY day ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS dau_7dma, AVG(dau) OVER (ORDER BY day ASC ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS dau_30dma, 'Binance' AS chain FROM ( SELECT d.day, d.dau, w.wau, m.mau FROM daus d INNER JOIN waus w ON w.day = d.day INNER JOIN maus m ON m.day = d.day ) a WHERE day IN ('2023-06-10', '2023-05-11', '2023-04-11', '2023-03-12', '2023-02-10', '2023-01-11') ORDER BY day DESC ) UNION ALL ( WITH gs AS ( SELECT DISTINCT DATE_TRUNC('day', block_timestamp) AS day FROM ethereum.core.fact_transactions WHERE block_timestamp < DATE_TRUNC('day',CURRENT_DATE()) AND block_timestamp >= DATE_TRUNC('day', CURRENT_DATE() - interval '210 DAYS') ORDER BY 1 ), addrs AS ( SELECT day, addr FROM ( SELECT DATE_TRUNC('day', block_timestamp) AS day, from_address AS addr FROM polygon.core.fact_transactions WHERE -- status = 'SUCCESS' block_timestamp < DATE_TRUNC('day', CURRENT_DATE()) AND block_timestamp >= DATE_TRUNC('day', CURRENT_DATE() - INTERVAL '210 DAYS') GROUP BY 1,2 ) GROUP BY day, addr ), daus AS ( SELECT day, COUNT(*) AS dau FROM ( SELECT day ,addr FROM addrs GROUP BY 1,2 ) a GROUP BY 1 ), waus AS ( SELECT day, COUNT(*) AS wau FROM ( SELECT gs.day AS day, w.addr FROM addrs w INNER JOIN gs ON w.day >= gs.day - (interval '6 days') AND w.day <= gs.day GROUP BY 1, 2 ) a GROUP BY 1 ), maus AS ( SELECT day, COUNT(*) AS mau FROM ( SELECT gs.day AS day, w.addr FROM addrs w INNER JOIN gs ON w.day >= gs.day - (interval '29 days') AND w.day <= gs.day GROUP BY 1, 2 ) a GROUP BY 1 ) SELECT *, -- DENSE_RANK() OVER (PARTITION BY DATE_TRUNC('week',day) ORDER BY day DESC) AS week_rank, -- DENSE_RANK() OVER (PARTITION BY DATE_TRUNC('month',day) ORDER BY day DESC) AS month_rank, AVG(dau) OVER (ORDER BY day ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS dau_7dma, AVG(dau) OVER (ORDER BY day ASC ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS dau_30dma, 'Polygon' AS chain FROM ( SELECT d.day, d.dau, w.wau, m.mau FROM daus d INNER JOIN waus w ON w.day = d.day INNER JOIN maus m ON m.day = d.day ) a WHERE day IN ('2023-06-10', '2023-05-11', '2023-04-11', '2023-03-12', '2023-02-10', '2023-01-11') ORDER BY day DESC ) UNION ALL ( WITH gs AS ( SELECT DISTINCT DATE_TRUNC('day', block_timestamp) AS day FROM ethereum.core.fact_transactions WHERE block_timestamp < DATE_TRUNC('day',CURRENT_DATE()) AND block_timestamp >= DATE_TRUNC('day', CURRENT_DATE() - interval '210 DAYS') ORDER BY 1 ), addrs AS ( SELECT day, addr FROM ( SELECT DATE_TRUNC('day', block_timestamp) AS day, from_address AS addr FROM ethereum.core.fact_transactions WHERE -- status = 'SUCCESS' block_timestamp < DATE_TRUNC('day', CURRENT_DATE()) AND block_timestamp >= DATE_TRUNC('day', CURRENT_DATE() - INTERVAL '210 DAYS') GROUP BY 1,2 ) GROUP BY day, addr ), daus AS ( SELECT day, COUNT(*) AS dau FROM ( SELECT day ,addr FROM addrs GROUP BY 1,2 ) a GROUP BY 1 ), waus AS ( SELECT day, COUNT(*) AS wau FROM ( SELECT gs.day AS day, w.addr FROM addrs w INNER JOIN gs ON w.day >= gs.day - (interval '6 days') AND w.day <= gs.day GROUP BY 1, 2 ) a GROUP BY 1 ), maus AS ( SELECT day, COUNT(*) AS mau FROM ( SELECT gs.day AS day, w.addr FROM addrs w INNER JOIN gs ON w.day >= gs.day - (interval '29 days') AND w.day <= gs.day GROUP BY 1, 2 ) a GROUP BY 1 ) SELECT *, -- DENSE_RANK() OVER (PARTITION BY DATE_TRUNC('week',day) ORDER BY day DESC) AS week_rank, -- DENSE_RANK() OVER (PARTITION BY DATE_TRUNC('month',day) ORDER BY day DESC) AS month_rank, AVG(dau) OVER (ORDER BY day ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS dau_7dma, AVG(dau) OVER (ORDER BY day ASC ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS dau_30dma, 'Ethereum' AS chain FROM ( SELECT d.day, d.dau, w.wau, m.mau FROM daus d INNER JOIN waus w ON w.day = d.day INNER JOIN maus m ON m.day = d.day ) a WHERE day IN ('2023-06-10', '2023-05-11', '2023-04-11', '2023-03-12', '2023-02-10', '2023-01-11') ORDER BY day DESC )

Query	Creator	Created
Untitled Query bsc evm binance	petertherock	Sep 13, 2022
Rolling 30D Active Wallets - All Chains bsc arbitrum ethereum optimism polygon	petertherock	May 15, 2023
Rolling 30 day active users by chain work solution bsc ethereum evm binance	petertherock	Apr 14, 2023
Polygon TPM vs other chains bsc arbitrum optimism avalanche polygon	petertherock	Oct 1, 2022
Active User across chains bsc arbitrum ethereum optimism l2	petertherock	Mar 16, 2023

petertherock