Query editor
WITH raw_data AS (-- Fetch and parse the JSON data from the APISELECTjson_parse(http_get('https://api.coingecko.com/api/v3/coins/hyperliquid/market_chart?vs_currency=usd&days=365')) AS data),prices AS (-- Extract and unnest the prices arraySELECTfrom_unixtime(CAST(json_extract_scalar(price_item, '$[0]') AS BIGINT) / 1000) AS day, -- Convert UNIX timestamp to a readable dateCAST(json_extract_scalar(price_item, '$[1]') AS DOUBLE) AS priceFROM raw_data,UNNEST(CAST(json_extract(data, '$.prices') AS ARRAY(JSON))) AS t(price_item)),market_caps AS (-- Extract and unnest the market_caps arraySELECTfrom_unixtime(CAST(json_extract_scalar(market_cap_item, '$[0]') AS BIGINT) / 1000) AS day, -- Convert UNIX timestamp to a readable dateCAST(json_extract_scalar(market_cap_item, '$[1]') AS DOUBLE) AS market_capFROM raw_data,UNNEST(CAST(json_extract(data, '$.market_caps') AS ARRAY(JSON))) AS t(market_cap_item)),final as (SELECTp.day,p.price,m.market_cap,m.market_cap/1000000000 as market_cap_counterFROM prices pLEFT JOIN market_caps mON p.day = m.day)select a.day, price, market_cap, market_cap_counterfrom (select date(day) as day, max(day) as max_dayfrom final agroup by 1)aleft join final bon a.max_day=b.dayorder by 1 desc