Query editor
WITH a AS (SELECTevt_block_time,evt_tx_hash,depositTokenAmount / 1e18 AS amount,memeToken AS token,userFROM hyperpie_launchpad_hyperevm.memelaunchpad_evt_memetokenbuyWHERE evt_block_time BETWEEN TRY_CAST('{{start_time}}' AS TIMESTAMP)AND TRY_CAST('{{end_time}}' AS TIMESTAMP)UNION ALLSELECTevt_block_time,evt_tx_hash,depositTokenAmountRec / 1e18 AS amount,memeToken AS token,userFROM hyperpie_launchpad_hyperevm.memelaunchpad_evt_memetokensellWHERE evt_block_time BETWEEN TRY_CAST('{{start_time}}' AS TIMESTAMP)AND TRY_CAST('{{end_time}}' AS TIMESTAMP)),price AS (SELECTMAX_BY(price, timestamp) AS price_usd,MAX(timestamp) AS price_tsFROM prices.hourWHERE blockchain = 'hyperevm'AND symbol = 'HYPE'AND price > 0AND timestamp >= (NOW() - INTERVAL '1' DAY))SELECTa.user,a.token,SUM(a.amount) AS total_amount_HYPE,MAX(p.price_usd) AS hype_price_usd,MAX(p.price_ts) AS price_timestamp,SUM(a.amount) * MAX(p.price_usd) AS total_amount_usdFROM aCROSS JOIN price pGROUP BY a.user, a.tokenORDER BY total_amount_HYPE DESC;