Query editor
/* truncated after june 15th to get rid of weird outliers rq */WITH token AS(SELECT call_tx_hash AS tx_hash,CASEWHEN addrs[7] = '\x0000000000000000000000000000000000000000' THEN '\xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'ELSE addrs[7]END AS token_addressFROM opensea."WyvernExchange_call_atomicMatch_" aWHERE addrs[5] = '\xED5AF388653567Af2F388E6224dC7C4b3241C544')SELECT *,AVG(eth_floor) OVER (ORDER BY HOUR ROWS BETWEEN 23 PRECEDING AND CURRENT ROW) AS average_floor,AVG(eth_avg) OVER (ORDER BY HOUR ROWS BETWEEN 23 PRECEDING AND CURRENT ROW) AS average_priceFROM (SELECT date_trunc('hour', evt_block_time) AS HOUR,MIN((om.price / 10^erc.decimals) ) AS eth_floor,AVG((om.price / 10^erc.decimals) ) AS eth_avg,SUM((om.price / 10^erc.decimals) ) AS eth_volume,count (*) as transactionsFROM opensea."WyvernExchange_evt_OrdersMatched" omINNER JOIN token ON token.tx_hash = om.evt_tx_hashINNER JOIN erc20.tokens erc ON token.token_address = erc.contract_addressAND om.evt_block_time >= '2021-07-15'GROUP BY 1ORDER BY 1 ) t