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, '$.pair_address') AS pool_address,json_extract_scalar(pool, '$.token0.address') AS token0_mint,json_extract_scalar(pool, '$.token1.address') AS token1_mint,CAST(json_extract_scalar(pool, '$.token0.decimals') AS DOUBLE) AS dec0,CAST(json_extract_scalar(pool, '$.token1.decimals') AS DOUBLE) AS dec1,CAST(json_extract_scalar(pool, '$.total_debts.token0') AS DOUBLE) AS raw_debt0,CAST(json_extract_scalar(pool, '$.total_debts.token1') AS DOUBLE) AS raw_debt1,CAST(json_extract_scalar(pool, '$.volume_24h.volumeUsd') AS DOUBLE) AS volume_usd_24h,CAST(json_extract_scalar(pool, '$.total_fees_paid.total_fee_paid_in_token0') AS DOUBLE) AS raw_fees0,CAST(json_extract_scalar(pool, '$.total_fees_paid.total_fee_paid_in_token1') AS DOUBLE) AS raw_fees1FROM unnested),all_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 (SELECT DISTINCTmint AS token_mint_address,CAST(json_extract_scalar(json_parse(gr.response),'$.data.attributes.token_prices.' || mint) AS DOUBLE) AS priceFROM (SELECT token0_mint AS mint FROM pool_metadata