PromQL query to aggregate effective CPU usage by Namespace

I have a PromQL query that aggregates average actual CPU usage by Namespace:

sum by (namespace) (
    avg_over_time(namespace:container_cpu_usage:sum{namespace=~"$namespace"}[$__range])
)

and a query that aggregates average CPU request by Namespace:

sum by (namespace) (
    avg_over_time(kube_pod_resource_request{resource="cpu", namespace=~"$namespace"}[$__range])
)

Is there a way that PromQL can aggregate the max of these two queries by Namespace, thereby giving us the average effective CPU usage by Namespace?

In promQL this can be done with following steps:

1. Use label_replace to add a label that will differenciate between results of two queries.
2. Use or as analog of UNION in SQL, to combine results of two queries.
3. Change sum by to aggregate within each query.
4. Wrap everything with max by to get the bigger value of two.

Resulting query in your case will be

max by (namespace) (
  sum by (namespace, name) (
    label_replace(
      avg_over_time(namespace:container_cpu_usage:sum{namespace=~"$namespace"}[$__range]),
      "name","cpu_usage",
      "namespace",".*")
    or
    label_replace(
      avg_over_time(kube_pod_resource_request{resource="cpu", namespace=~"$namespace"}[$__range]),
      "name","cpu_request",
      "namespace",".*")
  )
)

Transformations in Grafana