Query editor
WITH addresses AS (SELECT pool_address,liquidity_pool,token0,token1,token0_price,token1_price,pool_valueFROM query_5915600),creation AS (SELECT block_time as creation_time,VARBINARY_LTRIM(BYTEARRAY_SUBSTRING(data, 33,32)) AS pool_address,VARBINARY_LTRIM(topic1) AS token0,VARBINARY_LTRIM(topic2) AS token1FROM hyperevm.logsWHERE 1=1AND topic0 = 0x783cca1c0412dd0d695e784568c96da2e9c22ff989357a2e8b1d9b2b4e6b7118AND contract_address = 0xFf7B3e8C00e57ea31477c32A5B52a58Eea47b072),initial_lp AS (WITH first_day AS (SELECT contract_address,DATE_TRUNC('day', MIN(block_time)) AS first_dayFROM hyperevm.logsWHERE 1=1AND topic0 = 0x7a53080ba414158be7ec69b987b5fb7d07dee101fe85488f0853ae16239d0bdeAND contract_address IN (SELECT pool_address FROM creation)GROUP BY 1)SELECT first_day,contract_address,SUM(amount0) AS amount0,SUM(amount1) AS amount1FROM(SELECT f.first_day,l.tx_hash,l.contract_address,CASE WHEN topic0 = 0x7a53080ba414158be7ec69b987b5fb7d07dee101fe85488f0853ae16239d0bde THEN VARBINARY_TO_INT256(BYTEARRAY_SUBSTRING(data, 65, 32))WHEN topic0 = 0x0c396cd989a39f4459b5fa1aed6a9a8dcdbc45908acfd67e028cd568da98982c THEN -VARBINARY_TO_INT256(BYTEARRAY_SUBSTRING(data, 33, 32)) END AS amount0,CASE WHEN topic0 = 0x7a53080ba414158be7ec69b987b5fb7d07dee101fe85488f0853ae16239d0bde THEN VARBINARY_TO_INT256(BYTEARRAY_SUBSTRING(data, 97, 32))WHEN topic0 = 0x0c396cd989a39f4459b5fa1aed6a9a8dcdbc45908acfd67e028cd568da98982c THEN -VARBINARY_TO_INT256(BYTEARRAY_SUBSTRING(data, 65, 32)) END AS amount1FROM hyperevm.logs lINNER JOIN first_day f ON f.contract_address = l.contract_address AND DATE_TRUNC('day', l.block_time) = f.first_dayWHERE 1=1AND topic0 IN (0x7a53080ba414158be7ec69b987b5fb7d07dee101fe85488f0853ae16239d0bde, 0x0c396cd989a39f4459b5fa1aed6a9a8dcdbc45908acfd67e028cd568da98982c)AND l.contract_address IN (SELECT pool_address FROM creation))WHERE 1=1GROUP BY 1,2),pool_info AS(SELECT c.creation_time,a.pool_address,a.liquidity_pool,a.token0,