Query editor
WITH page1 AS (SELECT json_parse(http_get('https://api.geckoterminal.com/api/v2/networks/solana/dexes/byreal/pools?page=1')) AS data),page2 AS (SELECT json_parse(http_get('https://api.geckoterminal.com/api/v2/networks/solana/dexes/byreal/pools?page=2')) AS data),page3 AS (SELECT json_parse(http_get('https://api.geckoterminal.com/api/v2/networks/solana/dexes/byreal/pools?page=3')) AS data),page4 AS (SELECT json_parse(http_get('https://api.geckoterminal.com/api/v2/networks/solana/dexes/byreal/pools?page=4')) AS data),page5 AS (SELECT json_parse(http_get('https://api.geckoterminal.com/api/v2/networks/solana/dexes/byreal/pools?page=5')) AS data),all_pools AS (SELECT CAST(json_extract(data, '$.data') AS ARRAY(JSON)) AS pools_array FROM page1UNION ALLSELECT CAST(json_extract(data, '$.data') AS ARRAY(JSON)) FROM page2UNION ALLSELECT CAST(json_extract(data, '$.data') AS ARRAY(JSON)) FROM page3UNION ALLSELECT CAST(json_extract(data, '$.data') AS ARRAY(JSON)) FROM page4UNION ALLSELECT CAST(json_extract(data, '$.data') AS ARRAY(JSON)) FROM page5)SELECTjson_extract_scalar(pool, '$.id') AS pool_id,json_extract_scalar(pool, '$.attributes.name') AS pool_name,json_extract_scalar(pool, '$.attributes.address') AS pool_address,CAST(json_extract_scalar(pool, '$.attributes.reserve_in_usd') AS double) AS tvl_usd,CAST(json_extract_scalar(pool, '$.attributes.base_token_price_usd') AS double) AS token_price_usd,CAST(json_extract_scalar(pool, '$.attributes.volume_usd.h24') AS double) AS volume_24h_usd,CAST(json_extract_scalar(pool, '$.attributes.transactions.h24.buys') AS integer) +CAST(json_extract_scalar(pool, '$.attributes.transactions.h24.sells') AS integer) AS txs_24h,json_extract_scalar(pool, '$.attributes.pool_created_at') AS pool_created_atFROM all_poolsCROSS JOIN UNNEST(pools_array) AS t(pool)WHERE json_extract_scalar(pool, '$.attributes.reserve_in_usd') IS NOT NULLORDER BY tvl_usd DESC NULLS LAST