And I get this error: Unexpected error Error: Custom SQL fields cannot contain sub-queries.

A workaround for this is by enabling the ALLOW_ADHOC_SUBQUERY feature flag. For example, in your superset_config.py, ensure this is present:

FEATURE_FLAGS = {
  "ALLOW_ADHOC_SUBQUERY": True
}

(source: superset github issue #25072).

Note that this flag was disabled by default for security reason, so use it with caution. You can read more about this here:

• https://github.com/apache/superset/pull/19242