so... just from your description

for each id

=> means there is a GROUP BY missing somewhere

can you give and example of the transformation you are looking to perform, doe not have to be your real data, but if you could provide a fake (or real) sample of a handful of rows and what you would expect to get out that would really help

Specifically I am not tracking the nesting you are trying to do... you are nesting a query within the select but it looks (to me) independent form the outer query

if I run this query:

SELECT
  "system_id",
  (
    SELECT
      AVG("value")
    FROM table
    WHERE name = 'mem_usage'
  ) AS "avg_mem_usage",
  AVG("value") AS "avg_gpu_util"
FROM table
WHERE name = 'gpu_util'
GROUP BY 1

I get results like this:

system_id       avg_mem_usage       avg_gpu_util
328931          3.6                 36.53
328932          3.6                 24.31
328933          3.6                 2.67
328934          3.6                 9.88
328935          3.6                 98.42

although I expect this:

system_id       avg_mem_usage       avg_gpu_util
328931          3.6                 36.53
328932          7.54                24.31
328933          1.19                2.67
328934          2.51                9.88
328935          28.16               98.42

the table rows would look similar to this:

[
    {"name": "mem_usage", "timestamp": 1662730600, "system_id": 328931, "value": "2.3"},
    {"name": "gpu_util", "timestamp": 1662730605, "system_id": 328931, "value": "49.3"},
    {"name": "mem_usage", "timestamp": 1662730630, "system_id": 328931, "value": "5.88"},
    {"name": "gpu_util", "timestamp": 1662730635, "system_id": 328931, "value": "49.3"},
    {"name": "mem_usage", "timestamp": 1662730700, "system_id": 328931, "value": "6.71"},
    {"name": "gpu_util", "timestamp": 1662730705, "system_id": 328931, "value": "50.2"},
    {"name": "mem_usage", "timestamp": 1662730730, "system_id": 328931, "value": "3.56"},
    {"name": "gpu_util", "timestamp": 1662730735, "system_id": 328931, "value": "48.54"},
    {"name": "mem_usage", "timestamp": 1662730800, "system_id": 328931, "value": "4.66"},
    {"name": "gpu_util", "timestamp": 1662730805, "system_id": 328931, "value": "48.55"},
    ...
]