Query editor
WITH json_data_base AS (SELECTJSON_PARSE(HTTP_GET('https://api.llama.fi/overview/derivatives/base')) AS parsed_data),json_data_blast AS (SELECTJSON_PARSE(HTTP_GET('https://api.llama.fi/overview/derivatives/blast')) AS parsed_data),base_breakdown AS (SELECTJSON_EXTRACT(parsed_data, '$.totalDataChartBreakdown') AS breakdown_arrayFROM json_data_base),blast_breakdown AS (SELECTJSON_EXTRACT(parsed_data, '$.totalDataChartBreakdown') AS breakdown_arrayFROM json_data_blast),base_unnested AS (SELECTFROM_UNIXTIME(TRY_CAST(JSON_EXTRACT_SCALAR(item, '$[0]') AS BIGINT)) AS day,JSON_EXTRACT(item, '$[1]') AS product_volumes_jsonFROM base_breakdownCROSS JOIN UNNEST(TRY_CAST(JSON_EXTRACT(breakdown_array, '$') AS ARRAY(JSON))) AS t(item)),blast_unnested AS (SELECTFROM_UNIXTIME(TRY_CAST(JSON_EXTRACT_SCALAR(item, '$[0]') AS BIGINT)) AS day,JSON_EXTRACT(item, '$[1]') AS product_volumes_jsonFROM blast_breakdownCROSS JOIN UNNEST(TRY_CAST(JSON_EXTRACT(breakdown_array, '$') AS ARRAY(JSON))) AS t(item)),base_expanded AS (SELECTday,CASE WHEN key = 'SynFutures V3' THEN 'SynFutures V3' ELSE key END AS product_name,TRY_CAST(value AS DOUBLE) AS volumeFROM base_unnestedCROSS JOIN UNNEST(TRY_CAST(product_volumes_json AS MAP(VARCHAR, DOUBLE))) AS t(key, value)),blast_expanded AS (SELECTday,CASE WHEN key = 'SynFutures V3' THEN 'SynFutures V3' ELSE key END AS product_name,TRY_CAST(value AS DOUBLE) AS volumeFROM blast_unnestedCROSS JOIN UNNEST(TRY_CAST(product_volumes_json AS MAP(VARCHAR, DOUBLE))) AS t(key, value)),filtered_base AS (SELECTday,COALESCE(volume, 0) as base_volumeFROM base_expandedWHEREproduct_name = 'SynFutures V3'AND day >= FROM_UNIXTIME(1709251200) -- February 29, 2024),filtered_blast AS (SELECTday,