Query editor
WITH mint_events AS (SELECTdate_trunc('hour', evt_block_time) AS tx_time,SUM(CAST(amount0 AS DOUBLE) / 1e18) AS tac_add_amount,SUM(CAST(amount1 AS DOUBLE) / 1e18) AS usd1_add_amountFROM pancakeswap_v3_bnb.pancakev3pool_evt_mintWHERE contract_address = 0xd2cd331e493a325dd5634cfeb93852389c0aaeceAND evt_block_time >= TIMESTAMP '2026-04-01 00:00:00'GROUP BY 1),burn_events AS (SELECTdate_trunc('hour', evt_block_time) AS tx_time,SUM(CAST(amount0 AS DOUBLE) / 1e18) AS tac_remove_amount,SUM(CAST(amount1 AS DOUBLE) / 1e18) AS usd1_remove_amountFROM pancakeswap_v3_bnb.pancakev3pool_evt_burnWHERE contract_address = 0xd2cd331e493a325dd5634cfeb93852389c0aaeceAND evt_block_time >= TIMESTAMP '2026-04-01 00:00:00'GROUP BY 1),cg_raw AS (SELECThttp_get('https://pro-api.coingecko.com/api/v3/coins/tac/market_chart?vs_currency=usd&days=100&interval=hourly',ARRAY['accept: application/json','x-cg-pro-api-key: CG-q7v6WjHdkTFtLSMEna2f3hm8']) AS response),cg_hourly AS (SELECTdate_trunc('hour',from_unixtime(CAST(json_extract_scalar(price_point, '$[0]') AS DOUBLE) / 1000)) AS tx_time,MAX_BY(CAST(json_extract_scalar(price_point, '$[1]') AS DOUBLE),CAST(json_extract_scalar(price_point, '$[0]') AS DOUBLE)) AS tac_priceFROM cg_rawCROSS JOIN UNNEST(CAST(json_extract(response, '$.prices') AS ARRAY(JSON))) AS u(price_point)GROUP BY 1),hourly AS (SELECTCOALESCE(m.tx_time, b.tx_time) AS tx_time,COALESCE(m.tac_add_amount, 0) AS tac_add_amount,COALESCE(m.usd1_add_amount, 0) AS usd1_add_amount,COALESCE(b.tac_remove_amount, 0) AS tac_remove_amount,COALESCE(b.usd1_remove_amount, 0) AS usd1_remove_amount,c.tac_price,1.0 AS usd1_priceFROM mint_events mFULL OUTER JOIN burn_events bON m.tx_time = b.tx_time