Query editor
WITH raw_pools AS (SELECThttp_get('https://api.indexer.omnipair.fi/api/v1/pools?limit=1000&sortBy=tvl&sortOrder=desc') AS response),parsed_data AS (SELECT json_parse(response) AS parsed_jsonFROM raw_pools),pools_extracted AS (SELECTCAST(json_extract(parsed_json, '$.data.pools') AS ARRAY(JSON)) AS pools_arrayFROM parsed_data),unnested AS (SELECT poolFROM pools_extracted,UNNEST(pools_array) AS t(pool)),pool_metadata AS (SELECTjson_extract_scalar(pool, '$.id') AS id,json_extract_scalar(pool, '$.pair_address') AS pool_address,json_extract_scalar(pool, '$.token0.symbol') || '/' ||json_extract_scalar(pool, '$.token1.symbol') AS pair,json_extract_scalar(pool, '$.token0.address') AS token0_mint,json_extract_scalar(pool, '$.token1.address') AS token1_mint,-- Reserves already in human readable token units (no decimal conversion needed)CAST(json_extract_scalar(pool, '$.reserves.token0') AS DOUBLE) AS reserve0,CAST(json_extract_scalar(pool, '$.reserves.token1') AS DOUBLE) AS reserve1FROM unnested),-- Collect all unique token mints for one batched GeckoTerminal callall_mints AS (SELECT array_join(array_agg(DISTINCT mint), ',') AS mint_listFROM (SELECT token0_mint AS mint FROM pool_metadataUNIONSELECT token1_mint AS mint FROM pool_metadata)),gecko_response AS (SELECT http_get('https://api.geckoterminal.com/api/v2/simple/networks/solana/token_price/' || mint_list) AS responseFROM all_mintsLIMIT 1),token_prices AS (SELECTm.mint,CAST(json_extract_scalar(json_parse(gr.response),'$.data.attributes.token_prices.' || m.mint) AS DOUBLE) AS priceFROM (