Query editor
WITH json_data AS (SELECT json_parse(http_get('https://api.llama.fi/protocol/synfutures-v3')) AS parsed_data),dates AS (SELECT day as dateFROM UNNEST(SEQUENCE(CAST('2024-02-29' AS DATE),CURRENT_DATE,INTERVAL '1' DAY)) AS t(day)),blast_tvl AS (SELECTFROM_UNIXTIME(CAST(json_extract_scalar(value, '$.date') AS bigint)) AS date,CAST(json_extract_scalar(value, '$.totalLiquidityUSD') AS double) AS tvl_valueFROM json_dataCROSS JOIN UNNEST(CAST(json_extract(parsed_data, '$.chainTvls.Blast.tvl') AS ARRAY(JSON))) AS t(value)),base_tvl AS (SELECTFROM_UNIXTIME(CAST(json_extract_scalar(value, '$.date') AS bigint)) AS date,CAST(json_extract_scalar(value, '$.totalLiquidityUSD') AS double) AS tvl_valueFROM json_dataCROSS JOIN UNNEST(CAST(json_extract(parsed_data, '$.chainTvls.Base.tvl') AS ARRAY(JSON))) AS t(value))SELECTd.date,COALESCE(bt.tvl_value, 0) as blast_tvl,COALESCE(bat.tvl_value, 0) as base_tvl,COALESCE(bt.tvl_value, 0) + COALESCE(bat.tvl_value, 0) as total_tvlFROM dates dLEFT JOIN blast_tvl bt ON DATE_TRUNC('day', bt.date) = d.dateLEFT JOIN base_tvl bat ON DATE_TRUNC('day', bat.date) = d.dateORDER BY d.date;