Advanced SQL
Common Table Expressions (CTEs)
CTEs provide a way to define temporary named result sets within a query using the WITH clause.
Syntax
WITH cte_name AS (
SELECT ...
)
SELECT ... FROM cte_name;Single CTE
WITH high_value_orders AS (
SELECT customer_id, SUM(total) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 10000
)
SELECT c.name, h.total_spent
FROM customers c
JOIN high_value_orders h ON c.id = h.customer_id;Multiple CTEs
WITH
regional_sales AS (
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
),
top_regions AS (
SELECT region
FROM regional_sales
WHERE total_sales > (SELECT SUM(total_sales) / 10 FROM regional_sales)
)
SELECT region, total_sales
FROM regional_sales
WHERE region IN (SELECT region FROM top_regions);Window Functions
Window functions compute values across a set of rows related to the current row without collapsing the result set.
Syntax
function_name() OVER (
[PARTITION BY column1, column2, ...]
[ORDER BY column3, column4, ...]
)Ranking Functions
ROW_NUMBER
Assigns a unique sequential number to each row within a partition.
SELECT
name,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;RANK
Assigns a rank with gaps for ties.
SELECT
name,
score,
RANK() OVER (ORDER BY score DESC) AS rank
FROM students;
-- Scores: 95, 90, 90, 85 → Ranks: 1, 2, 2, 4DENSE_RANK
Assigns a rank without gaps for ties.
SELECT
name,
score,
DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM students;
-- Scores: 95, 90, 90, 85 → Ranks: 1, 2, 2, 3Aggregate Window Functions
Standard aggregate functions can be used as window functions:
SELECT
name,
department,
salary,
SUM(salary) OVER (PARTITION BY department) AS dept_total,
AVG(salary) OVER (PARTITION BY department) AS dept_avg,
COUNT(*) OVER (PARTITION BY department) AS dept_count,
MIN(salary) OVER (PARTITION BY department) AS dept_min,
MAX(salary) OVER (PARTITION BY department) AS dept_max
FROM employees;Window with ORDER BY
When ORDER BY is specified, aggregate window functions compute a running total:
SELECT
order_date,
amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;ROWS Frame
The default frame above is a RANGE frame: rows that tie on the ORDER BY key all get the same running total. A ROWS frame stops at the current row instead, even when later rows tie:
SELECT
order_date,
amount,
SUM(amount) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS running_total
FROM orders;ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is the same frame. This is the only ROWS frame supported; other frame bounds are rejected.
Set Operations
Combine results from multiple queries.
UNION ALL
Returns all rows from both queries, including duplicates:
SELECT name, email FROM customers
UNION ALL
SELECT name, email FROM prospects;UNION
Returns distinct rows from both queries:
SELECT city FROM customers
UNION
SELECT city FROM suppliers;INTERSECT
Returns rows that appear in both queries:
SELECT customer_id FROM orders_2023
INTERSECT
SELECT customer_id FROM orders_2024;EXCEPT
Returns rows from the first query that don't appear in the second:
SELECT customer_id FROM orders_2023
EXCEPT
SELECT customer_id FROM orders_2024;GROUP BY with HAVING
HAVING filters groups after aggregation, unlike WHERE which filters rows before aggregation.
SELECT
department,
COUNT(*) AS employee_count,
AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 5 AND AVG(salary) > 50000
ORDER BY avg_salary DESC;ORDER BY on Aggregates and Aliases
Arneb supports ordering by aggregate expressions and column aliases:
SELECT
category,
SUM(revenue) AS total_revenue
FROM sales
GROUP BY category
ORDER BY total_revenue DESC;
SELECT
region,
COUNT(*) AS order_count
FROM orders
GROUP BY region
ORDER BY COUNT(*) DESC;GROUP BY ROLLUP
GROUP BY ROLLUP(a, b) adds subtotal rows: it groups by (a, b), then by (a), then over all rows, with the rolled-up keys set to NULL. GROUPING(x) is 1 on rows where x was rolled up and 0 otherwise, which tells a subtotal's NULL apart from a NULL key value.
SELECT
region,
category,
SUM(revenue) AS total_revenue,
GROUPING(category) AS is_region_total
FROM sales
GROUP BY ROLLUP(region, category)
ORDER BY region, is_region_total, category;Limits:
ROLLUP(...)must be the whole GROUP BY clause: one ROLLUP, not mixed with plain keys, and no composite elements such asROLLUP((a, b), c).CUBEandGROUPING SETSare not supported.GROUPINGtakes one argument and works only withROLLUP; Trino's multi-argument form andGROUPINGover a plain GROUP BY are rejected.
Aggregate Functions
COUNT, SUM, AVG, MIN and MAX, plus Trino's variance family:
| Function | Result |
|---|---|
STDDEV_SAMP(x), STDDEV(x) | Sample standard deviation |
STDDEV_POP(x) | Population standard deviation |
VAR_SAMP(x), VARIANCE(x) | Sample variance |
VAR_POP(x) | Population variance |
The variance family returns DOUBLE and skips NULL inputs. It returns NULL when there are too few non-NULL values: none for the population forms, fewer than two for the sample forms.