Query editor
WITH fees_raw AS (SELECT json_parse(http_get('https://api.indexer.omnipair.fi/api/v1/stats/fees-chart?timeframe=30d')) AS response),volume_raw AS (SELECT json_parse(http_get('https://api.indexer.omnipair.fi/api/v1/stats/volume-chart?timeframe=30d')) AS response),fees_extracted AS (SELECTCAST(json_extract_scalar(entry, '$.date') AS DATE) AS day,CAST(json_extract_scalar(entry, '$.totalFees') AS DOUBLE) AS daily_total_fees_usd,CAST(json_extract_scalar(entry, '$.lpFees') AS DOUBLE) AS daily_lp_fees_usd,CAST(json_extract_scalar(entry, '$.protocolFees') AS DOUBLE) AS daily_protocol_fees_usdFROM fees_raw,UNNEST(CAST(json_extract(response, '$.data') AS ARRAY(JSON))) AS t(entry)),volume_extracted AS (SELECTCAST(json_extract_scalar(entry, '$.date') AS DATE) AS day,CAST(json_extract_scalar(entry, '$.volume') AS DOUBLE) AS daily_volume_usdFROM volume_raw,UNNEST(CAST(json_extract(response, '$.data') AS ARRAY(JSON))) AS t(entry)),-- Combine fees and volume, backfilling missing fees with volume × 0.25%combined AS (SELECTv.day,v.daily_volume_usd,-- If fees endpoint has data use it, otherwise backfill with volume × 0.25%CASEWHEN COALESCE(f.daily_total_fees_usd, 0) = 0THEN v.daily_volume_usd * 0.0025ELSE f.daily_total_fees_usdEND AS daily_total_fees_usd,CASEWHEN COALESCE(f.daily_lp_fees_usd, 0) = 0THEN v.daily_volume_usd * 0.0025 * 0.90ELSE f.daily_lp_fees_usdEND AS daily_lp_fees_usd,CASEWHEN COALESCE(f.daily_protocol_fees_usd, 0) = 0THEN v.daily_volume_usd * 0.0025 * 0.10ELSE f.daily_protocol_fees_usdEND AS daily_protocol_fees_usd,-- Flag to show which days are backfilled vs actualCASEWHEN COALESCE(f.daily_total_fees_usd, 0) = 0THEN '⚠️ Estimated'ELSE '✅ Actual'