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),pools_unnested AS (SELECTjson_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.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_24hFROM all_poolsCROSS JOIN UNNEST(pools_array) AS t(pool)WHERE json_extract_scalar(pool, '$.attributes.reserve_in_usd') IS NOT NULL)SELECT-- Top pools as individual columnsSUM(CASE WHEN pool_name = 'MNT / USDC' THEN tvl_usd ELSE 0 END) AS mnt_usdc_tvl,SUM(CASE WHEN pool_name = 'PYBOBO / USDC' THEN tvl_usd ELSE 0 END) AS pybobo_usdc_tvl,SUM(CASE WHEN pool_name = 'USDT / USDC' THEN tvl_usd ELSE 0 END) AS usdt_usdc_tvl,SUM(CASE WHEN pool_name = 'BIRB / USDC' THEN tvl_usd ELSE 0 END) AS birb_usdc_tvl,SUM(CASE WHEN pool_name = 'FIGHT / USDT' THEN tvl_usd ELSE 0 END) AS fight_usdt_tvl,SUM(CASE WHEN pool_name = 'SKR / USDC' THEN tvl_usd ELSE 0 END) AS skr_usdc_tvl,SUM(CASE WHEN pool_name = 'HYPE / USDC' THEN tvl_usd ELSE 0 END) AS hype_usdc_tvl,SUM(CASE WHEN pool_name = 'WETH / USDC' THEN tvl_usd ELSE 0 END) AS weth_usdc_tvl,SUM(CASE WHEN pool_name = 'SOL / USDC' THEN tvl_usd ELSE 0 END) AS sol_usdc_tvl,SUM(CASE WHEN pool_name = 'WAR / USD1' THEN tvl_usd ELSE 0 END) AS war_usd1_tvl,-- Total TVLSUM(tvl_usd) AS total_tvl,-- Others (pools not in top 10)SUM(tvl_usd) - (SUM(CASE WHEN pool_name = 'MNT / USDC' THEN tvl_usd ELSE 0 END) +