Query editor
WITHapi_response AS (SELECT json_parse(http_get(CONCAT('https://currency-api-cayg.onrender.com/api/exchange-rates?start_date=2025-01-01&end_date=', SUBSTRING(CAST(DATE_TRUNC('day', NOW()) AS varchar), 1, 10), '&symbols=ZAR,NGN,XOF,KES,GHS'))) AS response),weekly_forex AS (SELECTDATE_TRUNC('week', CAST(JSON_EXTRACT_SCALAR(value, '$.day') AS date)) AS week,JSON_EXTRACT_SCALAR(value, '$.symbol') AS currency,AVG(CAST(JSON_EXTRACT_SCALAR(value, '$.price') AS double)) AS avg_priceFROM UNNEST (CAST((SELECT response FROM api_response) AS array(json))) AS t (value)GROUP BY DATE_TRUNC('week', CAST(JSON_EXTRACT_SCALAR(value, '$.day') AS date)), JSON_EXTRACT_SCALAR(value, '$.symbol')),stablecoin_currency_map AS (SELECT stablecoin, currencyFROM (VALUES('ZARP', 'ZAR'),('xZAR', 'ZAR'),('cZAR', 'ZAR'),('cNGN', 'NGN'),('NGNC', 'NGN'),('eXOF', 'XOF'),('cKES', 'KES'),('cGHS', 'GHS')) AS t(stablecoin, currency)),token_info AS (SELECT'Base' AS chain, 0xb755506531786C8aC63B756BaB1ac387bACB0C04 AS contract_address, 'ZARP' AS stablecoin, 18 AS decimalsUNION ALLSELECT 'Ethereum', 0xb755506531786C8aC63B756BaB1ac387bACB0C04, 'ZARP', 18UNION ALLSELECT 'Polygon', 0xb755506531786C8aC63B756BaB1ac387bACB0C04, 'ZARP', 18UNION ALLSELECT 'Ethereum', 0x48f07301e9e29c3c38a80ae8d9ae771f224f1054, 'xZAR', 18UNION ALLSELECT 'Polygon', 0x30DE46509Dbc3a491128F97be0aAf70dc7Ff33cB, 'xZAR', 18UNION ALLSELECT 'BNB', 0x2074c8E9253CD50D3cB81deb28Ae85d932D2D26b, 'xZAR', 18UNION ALLSELECT 'Base', 0x46C85152bFe9f96829aA94755D9f915F9B10EF5F, 'cNGN', 6UNION ALLSELECT 'Polygon', 0x52828daa48C1a9A06F37500882b42daf0bE04C3B, 'cNGN', 6UNION ALLSELECT 'Ethereum', 0x17CDB2a01e7a34CbB3DD4b83260B05d0274C8dab, 'cNGN', 6UNION ALLSELECT 'BNB', 0xa8AEA66B361a8d53e8865c62D142167Af28Af058, 'cNGN', 6UNION ALLSELECT 'Celo', 0xE2702Bd97ee33c88c8f6f92DA3B733608aa76F71, 'cNGN', 18UNION ALLSELECT 'Base', 0xe743f13623e000261b634f0e5676f294475ec24d, 'NGNC', 18UNION ALLSELECT 'Avalanche', 0x3134210a6BdB55eED6E59905bC34928DF6d74830, 'NGNC', 6UNION ALLSELECT 'Celo', 0x4c35853A3B4e647fD266f4de678dCc8fEC410BF6, 'cZAR', 18UNION ALLSELECT 'Celo', 0xfAeA5F3404bbA20D3cc2f8C4B0A888F55a3c7313, 'cGHS', 18UNION ALLSELECT 'Celo', 0x73F93dcc49cB8A239e2032663e9475dd5ef29A08, 'eXOF', 18UNION ALL