Query editor
WITH json_data AS (SELECTJSON_PARSE(HTTP_GET('https://api.llama.fi/overview/derivatives')) AS parsed_data), total_data_chart_breakdown AS (SELECTJSON_EXTRACT(parsed_data, '$.totalDataChartBreakdown') AS breakdown_arrayFROM json_data), unnested_data AS (SELECTFROM_UNIXTIME(TRY_CAST(JSON_EXTRACT_SCALAR(item, '$[0]') AS BIGINT)) AS day,JSON_EXTRACT(item, '$[1]') AS product_volumes_jsonFROM total_data_chart_breakdownCROSS JOIN UNNEST(TRY_CAST(JSON_EXTRACT(breakdown_array, '$') AS ARRAY(JSON))) AS t(item)), expanded_data AS (SELECTday,CASE WHEN key = 'SynFutures V3' THEN 'SynFutures V3' ELSE key END AS product_name,TRY_CAST(value AS DOUBLE) AS volumeFROM unnested_dataCROSS JOIN UNNEST(TRY_CAST(product_volumes_json AS MAP(VARCHAR, DOUBLE))) AS t(key, value)), filtered_data AS (SELECTday,product_name,volumeFROM expanded_dataWHEREproduct_name = 'SynFutures V3'AND day >= FROM_UNIXTIME(1709251200)), date_filled AS (SELECTdayFROM UNNEST(SEQUENCE(DATE(FROM_UNIXTIME(1709251200)), CURRENT_DATE, INTERVAL '1' DAY)) AS t(day)), daily_data_with_gaps AS (SELECTdf.day,COALESCE(fd.volume, 0) AS volumeFROM date_filled AS dfLEFT JOIN filtered_data AS fdON df.day = fd.day)SELECTSUM(volume) AS total_volumeFROM daily_data_with_gaps;