Query editor
WITHlatest_eth_price AS (SELECTprice AS eth_usdFROM prices.usd_latestWHERE-- WETHAddresscontract_address = 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2AND blockchain = 'ethereum'ORDER BY minute DESCLIMIT 1),avg_revs AS (SELECTblock_date,AVG(SUM(value/1e18 * latest_eth_price.eth_usd)) OVER (ORDER BYblock_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS sma7_rev_usd,AVG(SUM(value/1e18*100 * latest_eth_price.eth_usd)) OVER (ORDER BYblock_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS sma7_vol_usd,AVG(SUM(value/1e18 * latest_eth_price.eth_usd)) OVER (ORDER BYblock_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS sma30_rev_usd,AVG(SUM(value/1e18*100 * latest_eth_price.eth_usd)) OVER (ORDER BYblock_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS sma30_vol_usdFROM ethereum.traces, latest_eth_priceWHEREto = 0x8ddcdE6e74Afddcb405a00B94F3644778045C99bAND success = TRUEAND block_number > 19885694AND block_date != current_dateGROUP BY block_date)SELECT t.block_date,SUM(value/1e18) AS "Revenue (ETH)",SUM(value/1e18 * latest_eth_price.eth_usd) AS "Revenue (USD)",SUM(value/1e18*100) AS "Volume (ETH)",SUM(value/1e18*100 * latest_eth_price.eth_usd) AS "Volume (USD)",AVG(avg_revs.sma7_rev_usd) AS "7dy average revenue (USD)",AVG(avg_revs.sma7_vol_usd) AS "7dy average volume (USD)",AVG(avg_revs.sma30_rev_usd) AS "30dy average revenue (USD)",AVG(avg_revs.sma30_vol_usd) AS "30dy average volume (USD)"FROM latest_eth_price, ethereum.traces tLEFT JOIN avg_revs ON avg_revs.block_date = t.block_dateWHERE to = 0x8ddcdE6e74Afddcb405a00B94F3644778045C99b AND success = TRUE AND block_number > 19885694GROUP BY t.block_dateORDER BY t.block_date