For privacy reasons, we restrict which SQL functions you can use in ADH.

## Scalar functions

All scalar functions except the following are allowed:

- `ERROR`
- `ST_AREA`
- `ST_ASBINARY`
- `ST_ASGEOJSON`
- `ST_ASTEXT`
- `ST_BOUNDARY`
- `ST_CENTROID`
- `ST_CENTROID_AGG`
- `ST_CLOSESTPOINT`
- `ST_CLUSTERDBSCAN`
- `ST_CONTAINS`
- `ST_CONVEXHULL`
- `ST_COVEREDBY`
- `ST_COVERS`
- `ST_DIFFERENCE`
- `ST_DIMENSION`
- `ST_DISJOINT`
- `ST_DISTANCE`
- `ST_DUMP`
- `ST_DWITHIN`
- `ST_EQUALS`
- `ST_GEOGFROMGEOJSON`
- `ST_GEOGFROMTEXT`
- `ST_GEOGFROMWKB`
- `ST_GEOGPOINT`
- `ST_GEOGPOINTFROMGEOHASH`
- `ST_GEOHASH`
- `ST_INTERSECTION`
- `ST_INTERSECTS`
- `ST_INTERSECTSBOX`
- `ST_ISCOLLECTION`
- `ST_ISEMPTY`
- `ST_LENGTH`
- `ST_MAKELINE`
- `ST_MAKEPOLYGON`
- `ST_MAKEPOLYGONORIENTED`
- `ST_MAXDISTANCE`
- `ST_NPOINTS`
- `ST_NUMPOINTS`
- `ST_PERIMETER`
- `ST_SIMPLIFY`
- `ST_SNAPTOGRID`
- `ST_TOUCHES`
- `ST_UNION`
- `ST_UNION_AGG`
- `ST_WITHIN`
- `ST_X`
- `ST_Y`

## Analysis queries

For aggregated analysis queries, we restrict functions that combine data from
multiple users. All aggregate and analytic functions are allowed when processing
a single user's data. See [User-level aggregations](https://developers.google.com/ads-data-hub/reference/allowed-functions#user_level) for details.

### Aggregate functions

The following functions are allowed for aggregation across users:

- `SUM`
- `COUNT`
- `COUNTIF`
- `APPROX_COUNT_DISTINCT`
- `AVG`

In the difference check privacy mode, the following functions are also allowed:

- `ANY_VALUE`
- `LOGICAL_AND`
- `LOGICAL_OR`
- `MAX`
- `MIN`
- `CORR`
- `COVAR_POP`
- `COVAR_SAMP`
- `STDDEV_POP`
- `STDDEV_SAMP`
- `STDDEV`
- `VAR_POP`
- `VAR_SAMP`
- `VARIANCE`

See [noise injection](https://developers.google.com/ads-data-hub/guides/noise-injection#supported_aggregate_functions)
for details on additional noise-specific aggregate functions and features.

### Analytic functions

All [analytic
functions](https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts)
are restricted.

### HyperLogLog++ functions

Ads Data Hub supports [HyperLogLog++ (HLL++)
functions](https://cloud.google.com/bigquery/docs/reference/standard-sql/hll_functions).
However, they use specialized data types with more limitations.

**Supported types**

| Type | Description |
|---|---|
| `ADH.USER_HLL` | A sketch aggregated from values of type `ADH.USER_ID` |
| `ADH.BYTE_HLL` | A sketch aggregated from values of any non-ID type |

These types support merging and extracting to cardinality estimates, and don't
support direct conversion to any other type. They cannot be directly exported
from Ads Data Hub, so they must use `HLL_COUNT.EXTRACT` or `HLL_COUNT.MERGE`
before reaching the query output.

HLL sketches enable cardinality estimation across multiple layers of
aggregation, but note that in Ads Data Hub they don't give a substantial
performance improvement over `COUNT(DISTINCT)` in a single layer.

**Supported functions**

| Function name | Supported types | Return type |
|---|---|---|
| `HLL_COUNT.EXTRACT` | `ADH.USER_HLL`, `ADH.BYTE_HLL` | `INT64` |
| `HLL_COUNT.INIT` | [All standard supported types](https://cloud.google.com/bigquery/docs/reference/standard-sql/hll_functions#hll_countinit), `ADH.USER_ID` | `ADH.USER_HLL` for `ADH.USER_ID` input, otherwise `ADH.BYTE_HLL` |
| `HLL_COUNT.INITNT.MERGE` | `ADH.USER_HLL`, `ADH.BYTE_HLL` | `INT64` |
| `HLL_COUNT.MERGE_PARTIAL` | `ADH.USER_HLL`, `ADH.BYTE_HLL` | Same as input type |

**Limitations**

**Noise injection:** HLL++ functions are not supported when running a query
using
[noise injection](https://developers.google.com/ads-data-hub/guides/noise-injection).

### User-level aggregations

We allow all aggregate and analytic functions in `SELECT` statements that group
or partition by `user_id`. For this to work, any preceding joins must make it
clear that the data is kept at the user level by joining on `user_id`, like in
this example:

    CREATE TABLE paths AS
    SELECT ARRAY_AGG(campaign_id ORDER BY query_id.time_usec) AS path
    FROM
      adh.google_ads_impressions
      LEFT JOIN adh.google_ads_creative_conversions
        USING(query_id, user_id)
    GROUP BY user_id;

## User list queries

In user list queries, aggregate functions are allowed, and analytic functions
aren't supported.