# 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](analysis-rules-custom.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\$1VALUE function   APPROXIMATE PERCENTILE\$1DISC function   AVG function   COUNT and COUNT DISTINCT functions    MAX function   MEDIAN function   MIN function   PERCENTILE\$1CONT function   STDDEV\$1SAMP and STDDEV\$1POP functions   SUM and SUM DISTINCT functions   VAR\$1SAMP and VAR\$1POP 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   HAVING   JOIN   JOIN condition   FROM   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   LEFT JOIN    LEFT SEMI JOIN   LEFT ANTI JOIN    RIGHT JOIN   FULL JOIN   [JOIN] OR operator   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. CROSS JOIN functions are not supported when combining two or more relations with differential privacy turned on.  | 
| Set operators | UNION, UNION ALL, INTERSECT, EXCEPT \$1 MINUS (these are synonyms) | UNION, UNION ALL, INTERSECT, EXCEPT \$1 MINUS (these are synonyms) | Not supported | 
| Window functions |  Aggregate functions   AVG window function   COUNT window function   CUME\$1DIST window function   DENSE\$1RANK window function   FIRST\$1VALUE window function   LAG window function   LAST\$1VALUE window function   LEAD window function   MAX window functions   MEDIAN window functions   MIN window functions   NTH\$1VALUE window function    STDDEV\$1SAMP and STDDEV\$1POP window function (STDDEV\$1SAMP and STDDEV are synonyms)   SUM window functions   VAR\$1SAMP and VAR\$1POP window functions (VAR\$1SAMP and VARIANCE are synonyms)   Ranking functions   DENSE\$1RANK window function   NTILE window function   PERCENT\$1RANK window function   RANK window function   ROW\$1NUMBER 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   COALESCE expression   GREATEST and LEAST functions   NVL and COALESCE functions   NVL2 function   NULLIF function    | All are supported | All are supported | 
| Conditions |    Comparison condition   Logical conditions   Pattern-matching conditions   BETWEEN range conditions   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   Concatenation operator   ADD\$1MONTHS functions   CONVERT\$1TIMEZONE function   CURRENT\$1DATE function   DATEADD function   DATEDIFF function   DATE\$1PART functions   DATE\$1TRUNC function   EXTRACT function    TO\$1TIMESTAMP function   Date parts for date or timestamp functions    | All are supported | All are supported | 
| String functions |    \$1\$1 (concatenation) operator   BTRIM function   CHAR\$1LENGTH function   CHARACTER\$1LENGTH function    CONCAT function   LEFT and RIGHT functions   LEN function   LENGTH function   LOWER function   LPAD and RPAD functions   LTRIM function   POSITION functions   REGEXP\$1COUNT function   REGEXP\$1INSTR function   REGEXP\$1REPLACE function   REGEXP\$1SUBSTR function   REPEAT function   REPLACE function    REVERSE function   RTRIM function    SPLIT\$1PART function    SUBSTRING function    TRANSLATE function   TRIM functions   UPPER function    | All are supported | All are supported | 
| Data type formatting functions |    CAST function   TO\$1CHAR   TO\$1DATE function   TO\$1NUMBER   Datetime format strings   Numeric format strings    | All are supported | All are supported | 
| Hash functions |     AES\$1ENCRYPT   AES\$1DECRYPT   ENCODE   DECODE    MD5 function    SHA1 function   SHA2 function     XX\$1HASH64     | All are supported | All are supported | 
| Mathematical operator symbols | \$1, -, \$1, /, %, and @ | All are supported | All are supported | 
| Math functions |    ABS function   ACOS function   ASIN function   ATAN function   ATAN2 function   CBRT function   CEILING (or CEIL) function   COS function   COT function   DEGREES function    LTRIM function    EXP function   FLOOR function   LN function   LOG function   MOD function   PI function   POWER function   RADIANS function   RANDOM function   ROUND function   SIGN function   SIN function   SQRT functions   TRUNC function    | All are supported | All are supported | 
| VARBYTE functions |      UNHEX,   UNBASE64   HEX    HLL\$1SKETCH\$1AGG,    HLL\$1SKETCH\$1ESTIMATE   HLL\$1UNION   HLL\$1UNION\$1AGG     | All are supported | All are supported | 
| JSON |      TO\$1JSON   GET\$1JSON\$1OBJECT     | All are supported | All are supported | 
| Array functions |      ARRAY\$1CONTAINS   ARRAY\$1DISTINCT   ARRAY\$1EXCEPT   ARRAY\$1INTERSECT   ARRAY\$1JOIN   ARRAY\$1REMOVE   ARRAY\$1SORT   ARRAY\$1UNION     | 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   PERCENTILE\$1CONT   PERCENTILE\$1DISC    | You can use the equivalent aggregate function with GROUP BY. | 
| Mathematical operator symbols |    \$1column \$1\$1/ 2   \$1column \$1/ 2   \$1column ^ 2    |    CBRT   SQRT   POWER(\$1column, 2)    | 
| Scalar functions |    SYSDATE   \$1column::integer   convert(type, \$1column)    |    CURRENT\$1DATE   CAST \$1column AS integer   CAST \$1column AS type    | 
| Literals | INTERVAL ‘1 SECOND' | INTERVAL '1' SECOND | 
| Row limiting | TOP n | LIMIT n | 
| Join |    USING   NATURAL    | ON clause should explicitly contain a join criterion. |