Query editor
WITH combined AS (SELECTevt_block_time,depositTokenAmount/1e18 AS valueFROM hyperpie_launchpad_hyperevm.memelaunchpad_evt_memetokenbuyUNION ALLSELECTevt_block_time,depositTokenAmountRec/1e18 AS valueFROM hyperpie_launchpad_hyperevm.memelaunchpad_evt_memetokensell),price AS (SELECTMAX_BY(price, timestamp) AS price_usd,MAX(timestamp) AS price_tsFROM prices.hourWHERE blockchain = 'hyperevm'AND symbol = 'HYPE'AND price > 0),daily AS (SELECTDATE_TRUNC('day', evt_block_time) AS day,SUM(value * 0.01) AS fees,SUM(value * 0.01 * 0.8) AS revenue,SUM(value * 0.01) * p.price_usd AS fees_usd,SUM(value * 0.01 * 0.8) * p.price_usd AS revenue_usdFROM combinedCROSS JOIN price pGROUP BY 1, p.price_usd)SELECTday,fees,revenue,fees_usd,revenue_usd,SUM(fees) OVER (ORDER BY day) AS cumulative_fees,SUM(revenue) OVER (ORDER BY day) AS cumulative_revenue,SUM(fees_usd) OVER (ORDER BY day) AS cumulative_fees_usd,SUM(revenue_usd)OVER (ORDER BY day) AS cumulative_revenue_usdFROM dailyORDER BY day;