Query editor
WITH protocols AS (SELECT *FROM (VALUES('https://api.llama.fi/updatedProtocol/hypurrfi'),('https://api.llama.fi/updatedProtocol/hyperyield'),('https://api.llama.fi/updatedProtocol/looped-hype'),('https://api.llama.fi/updatedProtocol/kittenswap-finance'),('https://api.llama.fi/updatedProtocol/growihf'),('https://api.llama.fi/updatedProtocol/sentiment'),('https://api.llama.fi/updatedProtocol/hyperpie'),('https://api.llama.fi/updatedProtocol/hyperlend'),('https://api.llama.fi/updatedProtocol/keiko-finance'),('https://api.llama.fi/updatedProtocol/felix'),('https://api.llama.fi/updatedProtocol/valantis'),('https://api.llama.fi/updatedProtocol/laminar'),('https://api.llama.fi/updatedProtocol/upshift'),('https://api.llama.fi/updatedProtocol/morpho'),('https://api.llama.fi/updatedProtocol/hyperswap')) AS t(url)),json_data AS (SELECTurl,JSON_PARSE(HTTP_GET(url)) AS parsed_dataFROM protocols),protocol_tvl AS (SELECTJSON_EXTRACT_SCALAR(parsed_data, '$.name') AS protocol_name,JSON_EXTRACT(parsed_data, '$.chainTvls') AS chain_tvlsFROM json_data),unnested_tvl AS (SELECTprotocol_name,chain_key,JSON_EXTRACT(chain_value, '$.tvl') AS tvl_arrayFROM protocol_tvlCROSS JOIN UNNEST(CAST(JSON_EXTRACT(chain_tvls, '$') AS MAP(VARCHAR, JSON))) AS t(chain_key, chain_value)WHERE chain_key = 'Hyperliquid L1' OR chain_key = 'Hyperliquid'),daily_tvl_extracted AS (SELECTprotocol_name,DATE_TRUNC('day', FROM_UNIXTIME(CAST(JSON_EXTRACT_SCALAR(item, '$.date') AS BIGINT))) AS day,CAST(JSON_EXTRACT_SCALAR(item, '$.date') AS BIGINT) AS timestamp,COALESCE(CAST(JSON_EXTRACT_SCALAR(item, '$.totalLiquidityUSD') AS DOUBLE), 0) AS total_liquidity_usdFROM unnested_tvlCROSS JOIN UNNEST(CAST(JSON_EXTRACT(tvl_array, '$') AS ARRAY(JSON))) AS t(item)),-- Get the latest timestamp for each day per protocollatest_daily_data AS (SELECTprotocol_name,day,FIRST_VALUE(total_liquidity_usd) OVER (PARTITION BY protocol_name, dayORDER BY timestamp DESC