# SQL capabilities of AWS Clean Rooms Differential Privacy
<a name="dp-sql-capabilities"></a>

AWS Clean Rooms Differential Privacy uses a general-purpose query structure to support complex SQL queries. Custom analysis templates are validated against this structure to ensure that they can run on tables protected by differential privacy. The following table indicates which functions are supported. See [Query structure and syntax](custom-diff-privacy.md#dp-query-structure-syntax) for more information.


| Category | SQL constructs supported in the Spark analytics engine | Common table expressions (CTEs) | Final SELECT clause | 
| --- |--- |--- |--- |
| Aggregate functions |  +  ANY\_VALUE function <br />+  APPROXIMATE PERCENTILE\_DISC function <br />+  AVG function <br />+  COUNT and COUNT DISTINCT functions <br />+  MAX function <br />+  MEDIAN function <br />+  MIN function <br />+  PERCENTILE\_CONT function <br />+  STDDEV\_SAMP and STDDEV\_POP functions <br />+  SUM and SUM DISTINCT functions <br />+  VAR\_SAMP and VAR\_POP functions   | Supported with the condition that CTEs using differential privacy protected tables must result in data with user-level records. You should write the SELECT expression in those CTEs using `SELECT userIdentifierColumn...' format. | Supported aggregations: AVG, COUNT, COUNT DISTINCT, STDDEV, and SUM. | 
| CTEs | WITH clause, WITH clause subquery | Supported with the condition that CTEs using differential privacy protected tables must result in data with user-level records. You should write the SELECT expression in those CTEs using `SELECT userIdentifierColumn...' format. | N/A | 
| Subqueries |  +  SELECT <br />+  HAVING <br />+  JOIN <br />+  JOIN condition <br />+  FROM <br />+  WHERE   | You can have any subquery that doesn't reference differential privacy relations in these constructs. You can have any subquery that references differential privacy relations in a FROM and JOIN clause only. | 
| Join clauses |  +  INNER JOIN <br />+  LEFT JOIN <br />+  LEFT SEMI JOIN <br />+  LEFT ANTI JOIN <br />+  RIGHT JOIN <br />+  FULL JOIN <br />+  [JOIN] OR operator <br />+  CROSS JOIN   | Supported with the condition that only JOIN functions that are equi-joins on user identifier columns are supported and are mandatory when querying two or more tables with differential privacy turned on. Ensure that the mandatory equi-join conditions are correct. Confirm that the table owner has configured the same user identifier column in all tables so that the definition of a user remains consistent across tables.<br />CROSS JOIN functions are not supported when combining two or more relations with differential privacy turned on. | 
| Set operators | UNION, UNION ALL, INTERSECT, EXCEPT \| MINUS (these are synonyms) | UNION, UNION ALL, INTERSECT, EXCEPT \| MINUS (these are synonyms) | Not supported | 
| Window functions | Aggregate functions+  AVG window function <br />+  COUNT window function <br />+  CUME\_DIST window function <br />+  DENSE\_RANK window function <br />+  FIRST\_VALUE window function <br />+  LAG window function <br />+  LAST\_VALUE window function <br />+  LEAD window function <br />+  MAX window functions <br />+  MEDIAN window functions <br />+  MIN window functions <br />+  NTH\_VALUE window function <br />+  STDDEV\_SAMP and STDDEV\_POP window function (STDDEV\_SAMP and STDDEV are synonyms) <br />+  SUM window functions <br />+  VAR\_SAMP and VAR\_POP window functions (VAR\_SAMP and VARIANCE are synonyms) <br />Ranking functions+  DENSE\_RANK window function <br />+  NTILE window function <br />+  PERCENT\_RANK window function <br />+  RANK window function <br />+  ROW\_NUMBER window function  | All are supported with the condition that the user identifier column in the window function's partition clause is required when you query a relation with differential privacy turned on. | Not supported | 
| Conditional expressions |  +  CASE condition expression <br />+  COALESCE expression <br />+  GREATEST and LEAST functions <br />+  NVL and COALESCE functions <br />+  NVL2 function <br />+  NULLIF function   | All are supported | All are supported | 
| Conditions |  +  Comparison condition <br />+  Logical conditions <br />+  Pattern-matching conditions <br />+  BETWEEN range conditions <br />+  Null condition   | EXISTS and IN can't be used because they require subqueries. All others are supported. | All are supported | 
| Date-time functions |  +  Date and time functions in transactions <br />+  Concatenation operator <br />+  ADD\_MONTHS functions <br />+  CONVERT\_TIMEZONE function <br />+  CURRENT\_DATE function <br />+  DATEADD function <br />+  DATEDIFF function <br />+  DATE\_PART functions <br />+  DATE\_TRUNC function <br />+  EXTRACT function <br />+  TO\_TIMESTAMP function <br />+  Date parts for date or timestamp functions   | All are supported | All are supported | 
| String functions |  +  \|\| (concatenation) operator <br />+  BTRIM function <br />+  CHAR\_LENGTH function <br />+  CHARACTER\_LENGTH function <br />+  CONCAT function <br />+  LEFT and RIGHT functions <br />+  LEN function <br />+  LENGTH function <br />+  LOWER function <br />+  LPAD and RPAD functions <br />+  LTRIM function <br />+  POSITION functions <br />+  REGEXP\_COUNT function <br />+  REGEXP\_INSTR function <br />+  REGEXP\_REPLACE function <br />+  REGEXP\_SUBSTR function <br />+  REPEAT function <br />+  REPLACE function <br />+  REVERSE function <br />+  RTRIM function <br />+  SPLIT\_PART function <br />+  SUBSTRING function <br />+  TRANSLATE function <br />+  TRIM functions <br />+  UPPER function   | All are supported | All are supported | 
| Data type formatting functions |  +  CAST function <br />+  TO\_CHAR <br />+  TO\_DATE function <br />+  TO\_NUMBER <br />+  Datetime format strings <br />+  Numeric format strings   | All are supported | All are supported | 
| Hash functions |  +  AES\_ENCRYPT <br />+  AES\_DECRYPT <br />+  ENCODE <br />+  DECODE <br />+  MD5 function <br />+  SHA1 function <br />+  SHA2 function <br />+  XX\_HASH64   | All are supported | All are supported | 
| Mathematical operator symbols | \+, -, \*, /, %, and @ | All are supported | All are supported | 
| Math functions |  +  ABS function <br />+  ACOS function <br />+  ASIN function <br />+  ATAN function <br />+  ATAN2 function <br />+  CBRT function <br />+  CEILING (or CEIL) function <br />+  COS function <br />+  COT function <br />+  DEGREES function <br />+  LTRIM function <br />+  EXP function <br />+  FLOOR function <br />+  LN function <br />+  LOG function <br />+  MOD function <br />+  PI function <br />+  POWER function <br />+  RADIANS function <br />+  RANDOM function <br />+  ROUND function <br />+  SIGN function <br />+  SIN function <br />+  SQRT functions <br />+  TRUNC function   | All are supported | All are supported | 
| VARBYTE functions |  +  UNHEX, <br />+  UNBASE64 <br />+  HEX <br />+   HLL\_SKETCH\_AGG, <br />+   HLL\_SKETCH\_ESTIMATE <br />+  HLL\_UNION <br />+  HLL\_UNION\_AGG   | All are supported | All are supported | 
| JSON |  +  TO\_JSON <br />+  GET\_JSON\_OBJECT   | All are supported | All are supported | 
| Array functions |  +  ARRAY\_CONTAINS <br />+  ARRAY\_DISTINCT <br />+  ARRAY\_EXCEPT <br />+  ARRAY\_INTERSECT <br />+  ARRAY\_JOIN <br />+  ARRAY\_REMOVE <br />+  ARRAY\_SORT <br />+  ARRAY\_UNION   | Not supported | Not supported | 
| Extended GROUP BY | GROUPING SETS, ROLLUP, CUBE | Not supported | Not supported | 
| Sort operation | ORDER BY | Supported with the condition that an ORDER BY clause is only supported in a window function's partition clause when querying tables with differential privacy turned on. | Supported | 
| Row limits | LIMIT, OFFSET | Not supported in CTEs using differential privacy protected tables | All are supported | 
| Table and column aliasing |   | Supported | Supported | 
| Math functions on aggregate functions |   | Supported | Supported | 
| Scalar functions within aggregate functions |   | Supported | Supported | 

## Common alternatives for unsupported SQL constructs
<a name="common-alternatives"></a>


| Category | SQL construct | Alternative | 
| --- |--- |--- |
| Window functions |  +  LISTAGG <br />+  PERCENTILE\_CONT <br />+  PERCENTILE\_DISC   | You can use the equivalent aggregate function with GROUP BY. | 
| Mathematical operator symbols |  +  $column \|\|/ 2 <br />+  $column \|/ 2 <br />+  $column ^ 2   |  +  CBRT <br />+  SQRT <br />+  POWER($column, 2)   | 
| Scalar functions |  +  SYSDATE <br />+  $column::integer <br />+  convert(type, $column)   |  +  CURRENT\_DATE <br />+  CAST $column AS integer <br />+  CAST $column AS type   | 
| Literals | INTERVAL ‘1 SECOND' | INTERVAL '1' SECOND | 
| Row limiting | TOP n | LIMIT n | 
| Join |  +  USING <br />+  NATURAL   | ON clause should explicitly contain a join criterion. |