Query editor
--forked from https://dune.com/queries/5611045/9124467 by HessishWITH tvlt AS (SELECTfrom_unixtime(CAST(item['date'] AS double)) AS date,CAST(item['tvl'] AS double) AS tvlFROM UNNEST(CAST(json_parse(HTTP_GET('https://api.llama.fi/v2/historicalChainTvl/INK'))AS ARRAY(MAP(VARCHAR, JSON)))) AS t (item)),changes AS (SELECTdate,tvl,LAG(tvl, 1) OVER (ORDER BY date ASC) AS tvl_1d_ago,LAG(tvl, 7) OVER (ORDER BY date ASC) AS tvl_7d_ago,LAG(tvl, 30) OVER (ORDER BY date ASC) AS tvl_30d_agoFROM tvlt)SELECTdate,tvl,CASE WHEN tvl_1d_ago IS NOT NULL THEN tvl - tvl_1d_ago END AS tvl_change_1d,CASE WHEN tvl_1d_ago IS NOT NULL AND tvl_1d_ago != 0THEN (tvl - tvl_1d_ago) * 100 / tvl_1d_ago END AS pct_change_1d,CASE WHEN tvl_7d_ago IS NOT NULL THEN tvl - tvl_7d_ago END AS tvl_change_7d,CASE WHEN tvl_7d_ago IS NOT NULL AND tvl_7d_ago != 0THEN (tvl - tvl_7d_ago) * 100 / tvl_7d_ago END AS pct_change_7d,CASE WHEN tvl_30d_ago IS NOT NULL THEN tvl - tvl_30d_ago END AS tvl_change_30d,CASE WHEN tvl_30d_ago IS NOT NULL AND tvl_30d_ago != 0THEN (tvl - tvl_30d_ago) * 100 / tvl_30d_ago END AS pct_change_30dFROM changes--where date > date('2024-04-30')ORDER BY date DESC;